I have three tables, categories, tags, and taggings. The categories table is put together in the fashion of a nested set, with relevant columns id, lft, rgt, and parent_id. Tags has id and name. Taggings has tag_id and taggable_id, which refers to categories.id.
If possible, I'd like for one query which returns in a field, say tag_list, a string containing a category's and all of its ancestors' tags. So given the following schema:
id | parent_id | lft | rgt
1 | NULL | 1 | 6
2 | 1 | 2 | 5
3 | 2 | 3 | 4
id | name
1 | cool
2 | rad
3 | soup
id | tag_id | taggable_id
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
I'd like for the query SELECT ??? FROM categories to return:
id | tag_list
1 | cool
2 | rad cool
3 | rad cool soup
Background info: I'm running Rails, and I'm using Thinking Sphinx for search and awesome_nested_set for nesting. I have a table called categories, which has many tags in a has_many_through relationship.
SELECT node_id, group_concat(name SEPARATOR ' ')
FROM taggings INNER JOIN tags ON taggings.tag_id=tags.id
INNER JOIN (SELECT node.id AS node_id, parent.id AS parent_id
FROM categories AS node,
categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt) subcategories
ON subcategories.parent_id=taggings.taggable_id
GROUP BY (node_id);
Well, I for one learned a thing or three doing that :)
EDIT: I did not check it against mysql, only SQLite, so the syntax might not be 100%.