Selecting 5 subcategories per category

Question

I have a table product_category, with fields id, name, parent_id and level.

For example, the category Agriculture has id = 75, level = 1 and parent_id = NULL. The subcategories Corn, Wheat, Rye, etc. have level = 2 and parent_id = 75.

In my site I'd like to show the top-level categories and below each of them, only 5 subcategories. But doing a query to retrieve them it's harder than what I thought.

If I do the following query:

SELECT a.name, a.parent_id FROM product_category a
WHERE (
    SELECT b.level
    FROM product_category b
    WHERE b.id = a.parent_id
    LIMIT 1
) = 1

I retrieve all top-level categories and subcategories, but there are thousands of subcategories so it would be very unnecessarily expensive when I only want the first 5 of each.

If I do the following one:

SELECT a.name, a.parent_id FROM product_category a
WHERE (
    SELECT b.level
    FROM product_category b
    WHERE b.id = a.parent_id
    LIMIT 1
) = 1
LIMIT 5

It only retrieves 5 subcategories, not 5 subcategories per top-level category.

Then I thought of doing it the following way:

(
     SELECT a.name, a.parent_id FROM product_category a
     WHERE parent_id = 12
     LIMIT 5
) UNION (
     SELECT a.name, a.parent_id FROM product_category a
     WHERE parent_id = 21
     LIMIT 5
) UNION (     
     SELECT a.name, a.parent_id FROM product_category a
     WHERE parent_id = 75
     LIMIT 5
) UNION (
.
.
.

Which looks very dirty and hard-coded, yet it is the only way I can think of right now. Is there any other solution to this?

Thanks!

Several good examples exist on SO already. Search for recursive query in mysql or hierarchical query with mysql. Here's one example.http://stackoverflow.com/questions/14266697/tricky-recursive-mysql-query But what your going to find is MySQL is limited in this type of query. You're best doing the structure outside of SQL in this case with mulitple calls. — xQbert, Feb 04 '13 at 22:15
@xQbert I don't think in this case doing multiple calls would be the best thing, because I have many "top level" categories and that would mean way too many connections to the database for each user entering to the front page. — federico-t, Feb 04 '13 at 23:21

score 3 · Accepted Answer · answered Feb 04 '13 at 22:32

Here's an example to return up to two subcategories per root category:

select  parent.name as Category
,       child.name as SubCategory
from    (
        select  name
        ,       parent_id
        ,       @rn := if(@cur = parent_id, @rn+1, 1) as rn
        ,       @cur := parent_id
        from    product_category pc
        join    (select @rn := 0, @cur := '') i
        where   level = 2
        order by
                parent_id
        ,       id
        ) as child
join    product_category as parent
on      child.parent_id = parent.id
where   child.rn < 3

Live example at SQL Fiddle.

Thanks man, this worked perfectly! I changed the `< 3` to `< 6` to make it return 5 subcategories per root category — federico-t, Feb 04 '13 at 23:18

score 2 · Answer 2 · answered Feb 04 '13 at 22:41

This solution prioritises sub-results alphabetically...

SELECT * FROM product_category;
+-----+---------------------+-----------+-------+
| id  | name                | parent_id | level |
+-----+---------------------+-----------+-------+
|  75 | Agriculture         |      NULL |     1 |
|  76 | Corn                |        75 |     2 |
|  77 | Wheat               |        75 |     2 |
|  78 | Rye                 |        75 |     2 |
|  85 | Vehicles            |      NULL |     1 |
|  86 | Cars                |        85 |     1 |
|  87 | Planes              |        85 |     1 |
|  88 | Trains              |        85 |     1 |
|  95 | Painters            |      NULL |     1 |
|  96 | Surrealists         |        95 |     2 |
|  97 | Impressionists      |        95 |     2 |
|  98 | Post-Impressionists |        95 |     2 |
|  99 | Max Ernst           |        96 |     3 |
| 100 | Claude Monet        |        97 |     3 |
| 101 | Gauguin             |        98 |     3 |
| 102 | Van Gogh            |        98 |     3 |
+-----+---------------------+-----------+-------+

SELECT a.*
  FROM
     ( SELECT x.*
            , y.name subcategory
         FROM product_category x
         JOIN product_category y
           ON y.parent_id = x.id
        WHERE x.parent_id IS NULL
     ) a
  JOIN
     ( SELECT x.*
            , y.name subcategory
         FROM product_category x
         JOIN product_category y
           ON y.parent_id = x.id
        WHERE x.parent_id IS NULL
     ) b
    ON b.id = a.id
   AND b.subcategory <= a.subcategory
 GROUP
    BY a.id,a.subcategory
HAVING COUNT(*) <= 2;
+----+-------------+-----------+-------+---------------------+
| id | name        | parent_id | level | subcategory         |
+----+-------------+-----------+-------+---------------------+
| 75 | Agriculture |      NULL |     1 | Corn                |
| 75 | Agriculture |      NULL |     1 | Rye                 |
| 85 | Vehicles    |      NULL |     1 | Cars                |
| 85 | Vehicles    |      NULL |     1 | Planes              |
| 95 | Painters    |      NULL |     1 | Impressionists      |
| 95 | Painters    |      NULL |     1 | Post-Impressionists |
+----+-------------+-----------+-------+---------------------+

+1 A *double* self join would perform badly for large rowsets. But really creative solution — Andomar, Feb 05 '13 at 10:41

Selecting 5 subcategories per category

2 Answers2