Figured out and fixed it - the fault is with acts-as-taggable-on, as it has explicit conditions for the joins that don't allow for join aliases. TS now checks for these (only if you're using acts-as-taggable-on), and replaces them appropriately.
Fix is in both master and rails3 branches, will hopefully have gem releases out soon. -- Pat On 24/05/2011, at 3:05 PM, sparky wrote: > Thanks Pat, please let me know if there's any info I can provide to > help. I'm digging through the code to see if I can come up with a > patch so any thoughts on where to start poking around would help me > out. > > Thanks again. > > s.park > > On May 24, 12:23 am, Pat Allan <[email protected]> wrote: >> Someone else raised this issue just the other day as well. I'll hopefully >> have some time to create a test app and give it a spin soon, see if I can >> figure out what's causing the problems. >> >> -- >> Pat >> >> On 24/05/2011, at 2:46 AM, sparky wrote: >> >> >> >> >> >> >> >>> I have a model that has acts_as_taggable in 2 contexts >> >>> class Asset < ActiveRecord::Base >>> acts_as_taggable >>> acts_as_taggable_on :categories >> >>> ... >>> end >> >>> Basically, I want to maintain a list of tags for the Asset as well as >>> a list of catergories. Both of which I'd like to index. >> >>> When I define the indices, >> >>> I tried >> >>> define_index do >>> indexes tags.name, :as => :categories >>> indexes categories.name, :as => :tags >>> .... >>> end >> >>> which generates the following sql..... (relevant snippets shown) >>> sql_query = SELECT ... >>> GROUP_CONCAT(DISTINCT IFNULL(`tags`.`name`, '0') SEPARATOR ' ') AS >>> `categories`, >>> GROUP_CONCAT(DISTINCT IFNULL(`categories_assets`.`name`, '0') >>> SEPARATOR ' ') AS `tags` >>> .... >>> FROM `assets` >>> LEFT OUTER JOIN `taggings` ON `assets`.`id` = `taggings`.`taggable_id` >>> AND `taggings`.`taggable_type` = 'Asset' >>> LEFT OUTER JOIN `tags` ON taggings.tagger_id IS NULL AND >>> taggings.context = 'tags' >>> LEFT OUTER JOIN `taggings` `categories_assets_join` ON `assets`.`id` = >>> `categories_assets_join`.`taggable_id` AND >>> `categories_assets_join`.`taggable_type` = 'Asset' LEFT OUTER JOIN >>> `tags` `categories_assets` ON taggings.tagger_id IS NULL AND >>> taggings.context = 'categories' >> >>> The problem is here 2 fold it looks like. In the joins, it joins on >>> the tagging tables twice, and in the join to the actual tags table, >>> it doesn't specify the tag.id so I get a massively cartesian query >>> that takes forever to run and indexes the wrong data. >> >>> What I think the from clause should look like is more like >> >>> LEFT OUTER JOIN `taggings` ON `assets`.`id` = >>> `taggings`.`taggable_id` AND `taggings`.`taggable_type` = 'Asset' >>> LEFT OUTER JOIN `tags` category_tags ON taggings.context = >>> 'categories' and taggings.tag_id = category_tags.id >>> LEFT OUTER JOIN `tags` tag_tags ON taggings.context = 'tags' and >>> taggings.tag_id = tag_tags.id >> >>> So my question is the following. What's the best way to fix the >>> indexing query? Should I try and figure out how the sql is being >>> generated and submit a patch? Should i disabled the ts:config and >>> handwrite the config or have I missed a way to pass in my own indexing >>> query into sphinx.yml so that I can free regenerate the config file >>> but use my query. >> >>> Thanks in advance for any help. >> >>> s.park >> >>> -- >>> 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 >>> athttp://groups.google.com/group/thinking-sphinx?hl=en. > > -- > 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. > -- 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.
