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
CREATE TABLE #MY_TABLE ( ID INT ,ID_FATHER INT ,COL_X INT ) CREATE TABLE #MY_TABLE_BIS ( MY_ID INT ,X VARCHAR(50) ) CREATE TABLE #OTHER_TABLE ( MY_ID INT ,[ROOT] VARCHAR(50) ) CREATE TABLE #BIG_TABLE ( AN_ID INT ) go DECLARE @MAXDEPTH INT = 10 ;WITH cte_prepare AS ( SELECT ID ,ID_FATHER ,TB.X ,OT.[ROOT] FROM #MY_TABLE T LEFT … 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
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
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
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
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