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.
