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