We have a hierarchy which looks this:

enter image description here

Now the problem is that most products are only connected with 1 category, which is most of the time the lowest level.

I am trying to get ALL products below a given category. So If select one category, I want ALL products from current category and ALL categories below. So this didn't work:

SELECT "category"."id"
FROM "products"
INNER JOIN "product_categories" ON "products"."id" = "product_categories"."product_id"
INNER JOIN "categories" ON "product_categories"."category_id" = "categories"."id"
WHERE "categories"."parent_id" = 0

Anyone who can help me out? BTW, we are using PostgreSQL


We are using "PostgreSQL 9.3.5" And these are the tables for this case, you can see in my above query (and below ERD) which connections I would like to accomplish. enter image description here

EDIT 2: This was my query for mysql (here we use taxonomies and not categories):

SELECT T2.id, T2.taxonomy, T2.parent, T2.terms_id, terms.term
        @r AS _id,
        (SELECT @r := parent FROM term_taxonomies WHERE id = _id) AS parent,
        @l := @l + 1 AS lvl
        (SELECT @r := 1, @l := 0) vars,
        term_taxonomies m
    WHERE @r <> 0) T1
JOIN term_taxonomies T2
ON T1._id = T2.id
JOIN terms ON T2.terms_id = terms.id

But this only works for mysql


If your data structure is fixed and out of your control, then the best way to do this is to use a recursive common table expression (CTE) like in this question.

If you can change the structure then there are ways to make such queries considerably more efficient. What you have there is often called a "naive tree" - while it allows easy construction and easy navigation up and down by one level at a time, more complex queries including those that need to consider arbitrary depth (like your question) can be a pain.

The common alternatives are:

  • Store the full path of each node as well as the parent relationship, and index it. This denormalises your data a little but makes "this node down" queries easy because you can search for WHERE tree_path LIKE current_node_path+'%' or to exlude the current node and list just those below it WHERE tree_path LIKE current_node_path+'/%'. Of course you have the extra work of updating the paths when nodes are moved - this is probably best done by trigger or if your application always uses stored procs for updating the tree instead of direct table access it is clear (and perhaps more efficient) to include the logic there.
  • Store a matrix of relationships, so for the tree 1->2, 1->3, 2->4, 2->5, 5->6 you would store:
    n1 n2 depth 2 1 1 3 1 1 4 2 1 4 1 2 5 2 1 5 1 2 6 5 1 6 2 2 6 1 3
    Again you have extra data to maintain, but it makes certain varieties of query more efficient and easer to write. For all under node 5 it is WHERE n1=5. Some would store the self relation (i.e. n1=5, n2=5, d=0) too to avoid unions when you want the node and all below - then "5 and below" is WHERE n1=5 and "below 5" is WHERE n1=5 AND depth>0.

There is a good chapter covering this in more detail in this book which I recommend anyone working with database has a read through - it has a good tone for the relatively inexperienced and the experienced will be reminded of important things they've forgotten! If you want to go into real depth there are whole books about handing hierarchical data in SQL and other relational systems. The usual recommendation is "Joe Celko's Trees and Hierarchies in SQL for Smarties".

  • Thank you! We're not able to implement the alternatives, so I'm taking a good look at recursive common table expression right now. And I'll take a good look at the book from the link you posted, looks good :) – Erik van de Ven Dec 19 '14 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.