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 -~----------~----~----~----~------~----~------~--~---
