mySQL query for selecting children

Question

I am not sure if this is possible in mySQL. Here are my tables:-

Categories table:

• id
• name
• parent_id (which points to Categories.id)

I use the above table to map all the categories and sub-categories.

Products table:

• id
• name
• category_id

The category_id in the Products table points to the sub-category id in which it belongs.

e.g. If I have Toys > Educational > ABC where ABC is product, Toys is Category and Educational is sub Category, then ABC will have category_id as 2.

Now the problem is that I want to use a SQL query to display all the products (in all the sub-categories and their sub-categories.. n level) for a particular category.

e.g.:

select * from categories,products where  category.name = 'Toys' and ....

The above query should display the products from Educational also and all other sub categories and their subcategories.

Is this possible using a mySQL query? If not what options do I have? I would like to avoid PHP recursion.

Update: Basically I want to display the top 10 products in the main category which I will be doing by adding a hits column to products table.

Answer 1

What I've done in previous projects where I've needed to do the same thing, I added two new columns.

• i_depth: int value of how deep the category is
• nvc_breadcrumb: complete path of the category in a breadcrumb type of format

And then I added a trigger to the table that houses the category information to do the following (all three updates are in the same trigger)...

-- Reset all branches
UPDATE t_org_branches
    SET nvc_breadcrumb = NULL,
    i_depth = NULL

-- Update the root branches first
UPDATE t_org_branches 
    SET nvc_breadcrumb = '/', 
        i_depth = 0 
    WHERE guid_branch_parent_id IS NULL

-- Update the child branches on a loop
WHILE EXISTS (SELECT * FROM t_branches WHERE i_depth IS NULL) 
    UPDATE tobA 
        SET tobA.i_depth = tobB.i_depth + 1, 
            tobA.nvc_breadcrumb = tobB.nvc_breadcrumb + Ltrim(tobA.guid_branch_parent_id) + '/' 
        FROM t_org_branches AS tobA
            INNER JOIN t_org_branches AS tobB ON (tobA.guid_branch_parent_id = tobB.guid_branch_id) 
        WHERE tobB.i_depth >= 0 
            AND tobB.nvc_breadcrumb IS NOT NULL 
            AND tobA.i_depth IS NULL

And then just do a join with your products table on the category ID and do a "LIKE '%/[CATEGORYID]/%' ". Keep in mind that this was done in MS SQL, but it should be easy enough to translate into a MySQL version.

It might just be compatible enough for a cut and paste (after table and column name change).

---

Expansion of explanation...

t_categories (as it stands now)...

Cat Parent  CategoryName
1   NULL    MyStore
2   1       Electronics
3   1       Clothing
4   1       Books
5   2       Televisions
6   2       Stereos
7   5       Plasma
8   5       LCD

t_categories (after modification)...

Cat  Parent  CategoryName   Depth   Breadcrumb
1   NULL    MyStore         NULL    NULL    
2   1       Electronics     NULL    NULL
3   1       Clothing        NULL    NULL
4   1       Books           NULL    NULL
5   2       Televisions     NULL    NULL
6   2       Stereos         NULL    NULL
7   5       Plasma          NULL    NULL
8   5       LCD             NULL    NULL

t_categories (after use of the script I gave)

Cat  Parent  CategoryName   Depth   Breadcrumb
1   NULL    MyStore         0       /   
2   1       Electronics     1       /1/
3   1       Clothing        1       /1/
4   1       Books           1       /1/
5   2       Televisions     2       /1/2/
6   2       Stereos         2       /1/2/
7   5       LCD             3       /1/2/5/
8   7       Samsung         4       /1/2/5/7/

t_products (as you have it now, no modifications)...

ID   Cat Name
1   8   Samsung LNT5271F
2   7   LCD TV mount, up to 36"
3   7   LCD TV mount, up to 52"
4   5   HDMI Cable, 6ft

Join categories and products (where categories is C, products is P)

C.Cat Parent CategoryName   Depth   Breadcrumb  ID   p.Cat  Name
1    NULL   MyStore         0       /           NULL NULL   NULL
2    1      Electronics     1       /1/         NULL NULL   NULL
3    1      Clothing        1       /1/         NULL NULL   NULL
4    1      Books           1       /1/         NULL NULL   NULL
5    2      Televisions     2       /1/2/       4    5      HDMI Cable, 6ft
6    2      Stereos         2       /1/2/       NULL NULL   NULL
7    5      LCD             3       /1/2/5/     2    7      LCD TV mount, up to 36"
7    5      LCD             3       /1/2/5/     3    7      LCD TV mount, up to 52"
8    7      Samsung         4       /1/2/5/7/   1    8      Samsung LNT5271F

Now assuming that the products table was more complete so that there is stuff in each category and no NULLs, you could do a "Breadcrumb LIKE '%/5/%'" to get the last three items of the last table I provided. Notice that it includes the direct items and children of the category (like the Samsung tv). If you want ONLY the specific category items, just do a "c.cat = 5".

Answer 2

I think the cleanest way to achieve this would be to use the nested set model. It's a bit complicated to implement, but powerful to use. MySQL has a tutorial named Managing Hierarchical Data in MySQL. One of the big SQL gurus Joe Celko wrote about the same thing here. If you need even more information have a look at Troel's links on storing hierarchical data.

In my case I would stay away from using a RDBMS to store this kind of data and use a graph database instead, as the data in this case actually is a directed graph.

Answer 3

Add a column to the Categories table that will contain the complete comma-delimited tree for each group. Using your example, sub-category Educational would have this as the tree '1,2', where 1 = Toys, 2 = Educational (it includes itself). The next nested level of categories would keep adding to the tree.

To get all products in a group, you use MySQL's FIND_IN_SET function, like so

SELECT p.ID 
FROM Products p INNER JOIN Categories c ON p.category_ID = c.ID
WHERE FIND_IN_SET(your_category_id, c.tree)

I wouldn't use this method for big tables, as I don't think this query can use an index.

Answer 4

One way is to maintain a table that contains the ancestor to descendant relationships. You can query this particular table and get the list of all dependents.

Answer 5

Assuming MySQL, it'll be difficult to avoid recursion in PHP.

Your question is, essentially, how to mimic Oracle's CONNECT BY PRIOR syntax in MySQL. People ask this question repeatedly but it's a feature that's never made it in to MySQL and implementing is via stored procedures probably won't work because (now) stored functions cannot be recursive.

Beware of the database kludges offered so far.

The best information so far are the three links from nawroth:

• Managing Hierarchical Data in MySQL, including the Nested Set Model.
• Trees in SQL - nested set model by Joel Celko
• Troels' links: Relational database systems: Hierarchical data in RDBMSs

Answer 6

How big is the table Categories? You may need to cache this on the application level and construct the appropriate query: ... where id in (2, 3, 6, 7)

Also, it's best if you fetch categories by id which is their unique ID, indexed and fast as opposed to finding by name.

Answer 7

Bear with me, because I have never done something like this.

BEGIN
  SET cat = "5";
  SET temp = "";

  WHILE STRCMP(temp, cat) != 0 DO
    SET temp = cat;
    SET cat = SELECT CONCAT_WS(GROUP_CONCAT(id), cat) FROM Categories GROUP BY (parent_id) HAVING FIND_IN_SET(parent_id, cat);
  END LOOP;
END;

SELECT * FROM products WHERE FIND_IN_SET(category_id, cat)

I can almost guarantee the above won't work, but you can see what I'm trying to do. I got this far and I just decided to not finish the end of the query (select the top N from each category), sorry. :P