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!

Answer 1

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 |
+----+-------------+-----------+-------+---------------------+

Answer 2

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.