has_many with :finder_sql

[Piotr Sarnacki]

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?

[Piotr Sarnacki]

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 it http://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

[Pat Allan]

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

[Piotr Sarnacki]

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 <p...@freelancing-gods.com> 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

[Piotr Sarnacki]

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 :)

[Pat Allan]

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