Selection of 5 subcategories for each category
I have a table product_category
with fields id
, name
, parent_id
and level
.
For example, the Agriculture category has , and . Subcategories Corn, Wheat, Rye, etc. Have and . id = 75
level = 1
parent_id = NULL
level = 2
parent_id = 75
On my site, I would like to show the top level categories and below each one, there are 5 subcategories in total. But making a request to get them is harder than I thought.
If I make the following request:
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 am fetching all top level categories and subcategories, but there are thousands of subcategories, so it would be very unreasonably expensive when I only need the first 5 of them.
If I do the following:
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
Only retrieves 5 subcategories, not 5 subcategories for the top level category.
Then I thought about doing it like this:
(
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 messy and hardcoded, but that's the only way I can think of right now. Is there any other solution for this?
Thank!
source to share
Here's an example of returning up to two subcategories for each 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
source to share
This solution suspends the sub-selections in alphabetical order ...
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 |
+----+-------------+-----------+-------+---------------------+
source to share