MySQL query incorporation both join tables and nested sets

Question

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:

Categories

id | parent_id | lft | rgt
 1 |      NULL |   1 |   6
 2 |         1 |   2 |   5
 3 |         2 |   3 |   4

Tags

id | name
 1 | cool
 2 |  rad
 3 | soup

Taggings

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.

Answer 1

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