select the category with the largest number of nested-parent subcategories, and the category path in MYSQL for OPENCART, an analog of SYS_CONNECT_BY_PATH oracle
as we know, it's hard to select hierarchical data from relational data without recursion, mysql still doesn't support SQL-1999 recursion in queries like
WITH [recursive] query_alias_name [ (column list) ]
AS (query)
main query
you could probably hack something together with stored procedures but I don't want to deal with them
how can this be implemented with a single query?
in opencart a product can be stored in several categories and we need to select the most deeply nested category and the full path to the parent (assume the product can only be in categories from a single branch)
select
pc.category_id, t1.parent_id, t2.parent_id, t3.parent_id, t4.parent_id, t5.parent_id,
!ISNULL( t1.parent_id)+!ISNULL( t2.parent_id)+! ISNULL(t3.parent_id)+!ISNULL( t4.parent_id)+!ISNULL( t5.parent_id) AS d
FROM _product_to_category pc
left join _category t1 on t1.category_id = pc.category_id
left join _category t2 on t1.parent_id = t2.category_id
left join _category t3 on t2.parent_id = t3.category_id
left join _category t4 on t3.parent_id = t4.category_id
left join _category t5 on t4.parent_id = t5.category_id
where
product_id = 3080
order by d DESC
LIMIT 1
but again
(assume the product can only be in categories from a single branch)
one more limitation - the maximum number of nested categories
here is the full query to select all category names with the full path of the most deeply nested category for an OPENCART product
SELECT cd.* FROM _category_description cd
,
(
select
pc.category_id AS c1,
t1.parent_id AS c2, t2.parent_id AS c3, t3.parent_id AS c4, t4.parent_id AS c5, t5.parent_id AS c6,
!ISNULL( t1.parent_id)+!ISNULL( t2.parent_id)+! ISNULL(t3.parent_id)+!ISNULL( t4.parent_id)+!ISNULL( t5.parent_id) AS d
FROM _product_to_category pc
left join _category t1 on t1.category_id = pc.category_id
left join _category t2 on t1.parent_id = t2.category_id
left join _category t3 on t2.parent_id = t3.category_id
left join _category t4 on t3.parent_id = t4.category_id
left join _category t5 on t4.parent_id = t5.category_id
where
product_id = 3465
order by d DESC
LIMIT 1
) f
WHERE cd.category_id IN (f.c1,f.c2,f.c3,f.c4,f.c5,f.c6)
Comments