if you want to get all level child of a particular parent then you should try this select id, name, parent from (select * from tablename order by parent, id) tablename, (select @pv := ‘1’) initialisation where find_in_set(parent, @pv) > 0 and @pv := concat(@pv, ‘,’, id)
You can always add a recursive function to your model: EDIT: Corrected according to SeomGi Han def get_all_children(self, include_self=True): r = [] if include_self: r.append(self) for c in Person.objects.filter(parent=self): _r = c.get_all_children(include_self=True) if 0 < len(_r): r.extend(_r) return r (Don’t use this if you have a lot of recursion or data …) Still recommending mptt … Read more
Here’s a complete example. First the DDL: test=> CREATE TABLE node ( test(> id SERIAL, test(> label TEXT NOT NULL, — name of the node test(> parent_id INT, test(> PRIMARY KEY(id) test(> ); NOTICE: CREATE TABLE will create implicit sequence “node_id_seq” for serial column “node.id” NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index … Read more
Look into using what is called a CTE (common table expression) (Refer to MSDN document): ;with cteAppointments as ( select AppointmentID, PersonID, PrevAppointmentID from Appointments where PrevAppointmentID is null union all select a.AppointmentID, a.PersonID, a.PrevAppointmentID from Appointments a inner join cteAppointments c on a.PrevAppointmentID = c.AppointmentID ) select AppointmentID, PrevAppointmentID from cteAppointments where PersonID = … Read more
Think of a recursive CTE as of an endless UNION ALL: WITH rows AS ( SELECT * FROM mytable WHERE anchor_condition ), rows2 AS ( SELECT * FROM set_operation(mytable, rows) ), rows3 AS ( SELECT * FROM set_operation(mytable, rows2) ), … SELECT * FROM rows UNION ALL SELECT * FROM rows2 UNION ALL SELECT * … Read more
Use a RECURSIVE CTE in Postgres: WITH RECURSIVE cte AS ( SELECT key, value, 1 AS level FROM taxonomy WHERE key = 0 UNION ALL SELECT t.key, t.value, c.level + 1 FROM cte c JOIN taxonomy t ON t.taxHier = c.key ) SELECT value FROM cte ORDER BY level; Details and links to documentation in … Read more
Here is an example script using common table expression: with recursive sumthis(id, val) as ( select id, value from example where id = :selectedid union all select C.id, C.value from sumthis P inner join example C on P.id = C.parentid ) select sum(val) from sumthis The script above creates a ‘virtual’ table called sumthis that … Read more
Edit Solution mentioned by @leftclickben is also effective. We can also use a stored procedure for the same. CREATE PROCEDURE get_tree(IN id int) BEGIN DECLARE child_id int; DECLARE prev_id int; SET prev_id = id; SET child_id=0; SELECT col3 into child_id FROM table1 WHERE col1=id ; create TEMPORARY table IF NOT EXISTS temp_table as (select * … Read more
MySQL doesn’t have recursive functionality, so you’re left with using the NUMBERS table trick – Create a table that only holds incrementing numbers – easy to do using an auto_increment: DROP TABLE IF EXISTS `example`.`numbers`; CREATE TABLE `example`.`numbers` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Populate the table … Read more
For MySQL 8+: use the recursive with syntax. For MySQL 5.x: use inline variables, path IDs, or self-joins. MySQL 8+ 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 ) … Read more