I have a MySQL table which is as follows:
id | name | parent_id |
---|---|---|
19 | category1 | 0 |
20 | category2 | 19 |
21 | category3 | 20 |
22 | category4 | 21 |
... | ... | ... |
Now, I want to have a single MySQL query to which I simply supply the id [for instance say id=19
] then I should get all its child ids [i.e. result should have ids '20,21,22']....
The hierarchy of the children is not known; it can vary....
I know how to do it using a for
loop... but how to achieve the same using a single MySQL query?
MySQL (still) doesn't support hierarchical queries (as other modern DBMS do). You will need to write a stored procedure or use a different datamodel. Possible duplicate of What are the options for storing hierarchical data in a relational database?
AS ( subquery ) Select col1, col2, .. from cte_name; cte_name: Name given to recursive subquery written in subquery block. col1, col2, ... colN: The name given to columns generated by subquery. subquery: A MySql query that refer to itself using cte_name as its own name.
Recursion is achieved by WITH statement, in SQL jargon called Common Table Expression (CTE). It allows to name the result and reference it within other queries sometime later. Here is a sample. Query (SELECT 1 AS n) now have a name — R .
MySQL does offer recursive common table expressions, but compared to SQL Server CTE's, they have major limitations and won't solve this problem. MySQL functions do not handle recursion at all. This article will explore all of these options.
For MySQL 8+: use the recursive with
syntax.
For MySQL 5.x: use inline variables, path IDs, or self-joins.
with recursive cte (id, name, parent_id) as ( select id, name, parent_id from products where parent_id = 19 union all select p.id, p.name, p.parent_id from products p inner join cte on p.parent_id = cte.id ) select * from cte;
The value specified in parent_id = 19
should be set to the id
of the parent you want to select all the descendants of.
For MySQL versions that do not support Common Table Expressions (up to version 5.7), you would achieve this with the following query:
select id, name, parent_id from (select * from products order by parent_id, id) products_sorted, (select @pv := '19') initialisation where find_in_set(parent_id, @pv) and length(@pv := concat(@pv, ',', id))
Here is a fiddle.
Here, the value specified in @pv := '19'
should be set to the id
of the parent you want to select all the descendants of.
This will work also if a parent has multiple children. However, it is required that each record fulfills the condition parent_id < id
, otherwise the results will not be complete.
This query uses specific MySQL syntax: variables are assigned and modified during its execution. Some assumptions are made about the order of execution:
from
clause is evaluated first. So that is where @pv
gets initialised.where
clause is evaluated for each record in the order of retrieval from the from
aliases. So this is where a condition is put to only include records for which the parent was already identified as being in the descendant tree (all descendants of the primary parent are progressively added to @pv
).where
clause are evaluated in order, and the evaluation is interrupted once the total outcome is certain. Therefore the second condition must be in second place, as it adds the id
to the parent list, and this should only happen if the id
passes the first condition. The length
function is only called to make sure this condition is always true, even if the pv
string would for some reason yield a falsy value.All in all, one may find these assumptions too risky to rely on. The documentation warns:
you might get the results you expect, but this is not guaranteed [...] the order of evaluation for expressions involving user variables is undefined.
So even though it works consistently with the above query, the evaluation order may still change, for instance when you add conditions or use this query as a view or sub-query in a larger query. It is a "feature" that will be removed in a future MySQL release:
Previous releases of MySQL made it possible to assign a value to a user variable in statements other than
SET
. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.
As stated above, from MySQL 8.0 onward you should use the recursive with
syntax.
For very large data sets this solution might get slow, as the find_in_set
operation is not the most ideal way to find a number in a list, certainly not in a list that reaches a size in the same order of magnitude as the number of records returned.
with recursive
, connect by
More and more databases implement the SQL:1999 ISO standard WITH [RECURSIVE]
syntax for recursive queries (e.g. Postgres 8.4+, SQL Server 2005+, DB2, Oracle 11gR2+, SQLite 3.8.4+, Firebird 2.1+, H2, HyperSQL 2.1.0+, Teradata, MariaDB 10.2.2+). And as of version 8.0, also MySQL supports it. See the top of this answer for the syntax to use.
Some databases have an alternative, non-standard syntax for hierarchical look-ups, such as the CONNECT BY
clause available on Oracle, DB2, Informix, CUBRID and other databases.
MySQL version 5.7 does not offer such a feature. When your database engine provides this syntax or you can migrate to one that does, then that is certainly the best option to go for. If not, then also consider the following alternatives.
Things become a lot easier if you would assign id
values that contain the hierarchical information: a path. For example, in your case this could look like this:
ID | NAME |
---|---|
19 | category1 |
19/1 | category2 |
19/1/1 | category3 |
19/1/1/1 | category4 |
Then your select
would look like this:
select id, name from products where id like '19/%'
If you know an upper limit for how deep your hierarchy tree can become, you can use a standard sql
query like this:
select p6.parent_id as parent6_id, p5.parent_id as parent5_id, p4.parent_id as parent4_id, p3.parent_id as parent3_id, p2.parent_id as parent2_id, p1.parent_id as parent_id, p1.id as product_id, p1.name from products p1 left join products p2 on p2.id = p1.parent_id left join products p3 on p3.id = p2.parent_id left join products p4 on p4.id = p3.parent_id left join products p5 on p5.id = p4.parent_id left join products p6 on p6.id = p5.parent_id where 19 in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id) order by 1, 2, 3, 4, 5, 6, 7;
See this fiddle
The where
condition specifies which parent you want to retrieve the descendants of. You can extend this query with more levels as needed.
From the blog Managing Hierarchical Data in MySQL
Table structure
+-------------+----------------------+--------+ | category_id | name | parent | +-------------+----------------------+--------+ | 1 | ELECTRONICS | NULL | | 2 | TELEVISIONS | 1 | | 3 | TUBE | 2 | | 4 | LCD | 2 | | 5 | PLASMA | 2 | | 6 | PORTABLE ELECTRONICS | 1 | | 7 | MP3 PLAYERS | 6 | | 8 | FLASH | 7 | | 9 | CD PLAYERS | 6 | | 10 | 2 WAY RADIOS | 6 | +-------------+----------------------+--------+
Query:
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM category AS t1 LEFT JOIN category AS t2 ON t2.parent = t1.category_id LEFT JOIN category AS t3 ON t3.parent = t2.category_id LEFT JOIN category AS t4 ON t4.parent = t3.category_id WHERE t1.name = 'ELECTRONICS';
Output
+-------------+----------------------+--------------+-------+ | lev1 | lev2 | lev3 | lev4 | +-------------+----------------------+--------------+-------+ | ELECTRONICS | TELEVISIONS | TUBE | NULL | | ELECTRONICS | TELEVISIONS | LCD | NULL | | ELECTRONICS | TELEVISIONS | PLASMA | NULL | | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH | | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL | | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL | +-------------+----------------------+--------------+-------+
Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table. Read more
Refer the blog for more details.
EDIT:
select @pv:=category_id as category_id, name, parent from category join (select @pv:=19)tmp where parent=@pv
Output:
category_id name parent 19 category1 0 20 category2 19 21 category3 20 22 category4 21
Reference: How to do the Recursive SELECT query in Mysql?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With