Recursive query with 2 parents

[jphilip]

I was wondering if it is possible to have a recursive query with 2 parent fields with django-cte, i.e., something like this SQL:

WITH RECURSIVE ped_cte AS (
  SELECT 1 AS n, d.id, d.name, d.father_id, d.mother_id
  FROM dog AS d
  WHERE d.id = 16324  

  UNION ALL

  SELECT n+1, f.id, f.name, f.father_id, f.mother_id
  FROM ped_cte  
  INNER JOIN dog AS f ON ped_cte.father_id = f.id  
  WHERE n < 5

  UNION ALL

  SELECT n+1, m.id, m.name, m.father_id, m.mother_id
  FROM ped_cte  
  INNER JOIN dog AS m ON ped_cte.mother_id = m.id  
  WHERE n < 5  
)
SELECT * FROM ped_cte;

Edit by @millerdev to fix code formatting.

[millerdev]

@jphilip I don't see why not. Have you tried adding a second .union(...) clause in the recursive query expression?

Examples of recursive CTEs can be found in the tests.

django-cte/tests/test_recursive.py

Lines 34 to 52 in ab0cf7e

	def make_regions_cte(cte):
	return Region.objects.filter(
	parent__isnull=True
	).values(
	"name",
	path=F("name"),
	depth=Value(0, output_field=int_field),
	).union(
	cte.join(Region, parent=cte.col.name).values(
	"name",
	path=Concat(
	cte.col.path, Value("\x01"), F("name"),
	output_field=text_field,
	),
	depth=cte.col.depth + Value(1, output_field=int_field),
	),
	all=True,
	)
	cte = With.recursive(make_regions_cte)

[jphilip]

@millerdev I did, but for some reason the parents did not get loaded into the object, so I ended up using a bare cursor with sql code similar to what I showed here, but with a materialized path field which in turn allowed me to easily recursively load the data into the object.

[millerdev]

@jphilip can you provide a test case that reproduces your issue?

[millerdev]

Feel free to re-open if this is still an issue.