Hi Piotr
Now that I've thought about it, and it's clicked how finder_sql works
in AR associations, I can't see how ActiveRecord - and thus Thinking
Sphinx - can merge it easily into other queries as a join. I mustn't
have been completely awake and focused when thinking about it
previously.
But, I've taken your patch as inspiration, and restructured it
slightly, so in your example you can do the following:
has FEATURE_OPTION_IDS_SQL,
:as => :feature_option_ids,
:source => :query
The :type => :multi is implicit, since you're setting a source, and
there's no need to refer to associations, as they're not used, so
let's just throw in the SQL as the main argument. You definitely need
that alias though, otherwise everything will fall over.
Hopefully this should help everything work for you :)
Oh, and Menno's great work has been merged in as well, of course.
Cheers
--
Pat
On 28/02/2009, at 10:00 PM, Piotr Sarnacki wrote:
>
> I've got some time to take a closer look to my problem recently and I
> managed to add support for custom query sql for sql_attr_multi. It was
> pretty easy using menno's patch:
> http://groups.google.com/group/thinking-sphinx/browse_thread/thread/94bec92a0c401136#
>
> http://github.com/drogus/thinking-sphinx/commit/e8c9d47e7e5e8dba93446040712607c4a7e3453b
>
> So now I can do crazy stuff like: http://gist.github.com/71933 without
> attaching any lines to config manually (I know this query is huge, but
> it's an edge case and I can't really find better solution :)
>
> I must say that: Thinking-Sphinx and Sphinx are awesome. One of the
> best tools I've ever used. I can't imagine how much work I would have
> to do, to obtain the same result with using AR.find and SQL...
>
> Cheers :)
>
> On Feb 15, 10:32 am, Piotr Sarnacki <[email protected]> wrote:
>> I've added config to previous gist: http://gist.github.com/64335
>>
>> The tables are like (listing only important columns):
>> restaurants:
>> id: integer
>> name: string
>>
>> feature_options:
>> id: integer
>> name: string
>> feature_id: integer
>> radio: string
>>
>> # restaurants and feature_options join table
>> restaurant_feature_options:
>> id: integer
>> feature_option_id: integer
>> restaurant_id: integer
>>
>> # it's for grouping feature_options - feature_options belongs_to
>> features
>> features:
>> id: integer
>> name: string
>>
>> The error says that pgsql can't find feature_options.restaurant_id
>> column (and it's correct as feature_options doesn't have one).
>>
>> It seems that thinking sphinx omits finder_sql and just tries to get
>> feature_options as they were regular has_many with
>> feature_options.restaurant_id = restaurants.id (in fact
>> choosen_radio_options are like has_many through).
>>
>> I've tried to use 'has' with sql argument, but I can't figure out how
>> to insert JOINS and DISTINCT there. (DISTINCT is very important in
>> this query. it has to select the feature with most votes).
>>
>> Are you sure that TS should use :finder_sql in config generation?
>>
>> On Feb 15, 9:30 am, Pat Allan <[email protected]> wrote:
>>
>>> Hi Piotr
>>
>>> Ideally, your original approach - using chosen_radio_features(:id) -
>>> should do the trick, and it shouldn't generate errors. It will also
>>> add the sql_attr_multi definition to the configuration file.
>>
>>> So, we should try to figure out why it isn't working, though. Can
>>> you
>>> put your original error-generating config onto gist?
>>
>>> As a work-around, you can edit the config file manually, and then
>>> when
>>> you index, you'll need to set the INDEX_ONLY environment variable as
>>> well, to ensure the file isn't overwritten:
>>
>>> rake ts:in INDEX_ONLY=true
>>
>>> Of course, should you make changes to your config via ruby later,
>>> you're going to have to overwrite the file and re-apply your custom
>>> edit.
>>
>>> Cheers
>>
>>> --
>>> Pat
>>
>>> On 15/02/2009, at 7:21 PM, Piotr Sarnacki wrote:
>>
>>>> I had some time to play with it and probably the best way is to add
>>>> sql_attr_multi definition to config file. I've added
>>>> ithttp://gist.github.com/64643
>>>> and it even works! :)
>>
>>>> The funny thing is that I always feared sphinx's config, stayed
>>>> out of
>>>> it and prayed that Riddle/TS has needed config generation
>>>> features ;-)
>>>> It seems that it's not as hard as it seems, but you have to give
>>>> it a
>>>> chance (yeah, I know it's pretty obvious, but everything is obvious
>>>> when you know it ;-).
>>
>>>> The only thing that left is to add this part of config file each
>>>> time
>>>> config is generated. At the moment every ts:config or ts:index
>>>> run is
>>>> generating standard config from define_index block overwriting my
>>>> changes. Is there any way to inject it to config file while
>>>> generating? I can't find it in the docs...
>>
>>>> Cheers
>>
>>>> On Feb 14, 11:48 am, Piotr Sarnacki <[email protected]> wrote:
>>>>> Hi,
>>
>>>>> I have to get some records with pretty complex query. It's only
>>>>> for
>>>>> sphinx so I use has_many with :finder_sql
>>>>> option:http://gist.github.com/64335
>>
>>>>> With this collection and has choosen_radio_features(:id), :as
>>>>> => :feature_ids generated config contains some errors and there
>>>>> is no
>>>>> DISTINCT option anywhere - probably TS does not use finder_sql.
>>
>>>>> What is the proper way to get such indexes?
>>
>>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Thinking Sphinx" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/thinking-sphinx?hl=en
-~----------~----~----~----~------~----~------~--~---
