Index and facet category ancestor ids recipe

[James Earl]

Hi,

Just wanted to post what I finally ended up doing to index and facet
our product category ancestor ids. My categories are setup using
nested sets (using awesome_nested_set). Maybe it'll help another
newbie like me, or someone will tell me there's an easier way, or
maybe it'll help to improve TS?

define_index do
indexes products.description
has category(id), :as => :category_ids, :facet => true
end

This creates a join on the categories table. It also provides a
placeholder to allow us to trick Thinking Sphinx into allowing us to
facet on the MVA attribute that we'll change this to below.

After running rake ts:config to update the configuration file, we edit
it to make our custom changes.

Delete this line created by TS based on our 'has category(id)' line above:
sql_attr_uint = category_ids

Add after "sql_attr_multi = uint subclass_crcs from field" to let
Sphinx know this will be a MVA field:
sql_attr_multi = uint category_ids from field

Then, in the 'sql_query' find category_ids in the SELECT and replace
the following:
`categories`.`id` AS `category_ids`

With this (which creates a string of comma separated category ids or 0 if null):
GROUP_CONCAT(DISTINCT IFNULL(`c1`.`id`, '0') SEPARATOR ',') AS `category_ids`

Lastly, we add an additional join on categories (named 'c1' in this example):
LEFT OUTER JOIN `categories` ON `categories`.id = `products`.category_id
LEFT OUTER JOIN `categories` c1 ON `categories`.lft BETWEEN `c1`.lft
AND `c1`.rgt

Update indexes without regenerating the configuration file:
rake ts:in INDEX_ONLY=true

Now you can go have fun doing something else!

James

My wish list:
* INDEX_ONLY=true becomes the default behaviour.
* :facet => true is no longer required on attributes.

[zaadjis]

Hey James,

A while back I had the same (a PTA of a) problem, eventually ended up
with this:

has category_id, :facet => true
has "SELECT p.id * 3 + 2 AS id, GROUP_CONCAT(a.id SEPARATOR ',')
AS category_ancestor_id " +
'FROM products AS p ' +
'JOIN categories AS c ON c.id = p.category_id ' +
'JOIN categories AS a ON a.parent_id IS NOT NULL AND a.lft
<= c.lft AND c.rgt <= a.rgt ' +
'GROUP BY p.id',
:as => :category_ancestor_id, :source => :query

Note: facets didn't work with source=>query (if I recall correctly),
so I fell back to faceting only the parent category (instead of all
the ancestors).

Cheers