[Django]-Recursive QuerySet with django

by

2👍

✅

Shouldn’t this work for you?

parts = PartCategory.objects.raw('''
    WITH RECURSIVE
    under_partcategory(id,name, parent_id,level) AS (
    select   api_partcategory.id,api_partcategory.name,api_partcategory.parent_id,0 from api_partcategory where api_partcategory.id=64
    UNION ALL
    SELECT api_partcategory.id,api_partcategory.name,api_partcategory.parent_id, under_partcategory.level+1
    FROM api_partcategory JOIN under_partcategory ON api_partcategory.parent_id=under_partcategory.id
    ORDER BY 2
  )
SELECT * FROM under_partcategory;
''')

You can also look at https://github.com/django-mptt/django-mptt

👤Colwin

1👍

Or check this: https://docs.djangoproject.com/en/4.1/topics/db/sql/#passing-parameters-into-raw

id = 64

parts = PartCategory.objects.raw('''
    WITH RECURSIVE
    under_partcategory(id,name, parent_id,level) AS (
    select   api_partcategory.id,api_partcategory.name,api_partcategory.parent_id,0 from api_partcategory where api_partcategory.id=%s
    UNION ALL
    SELECT api_partcategory.id,api_partcategory.name,api_partcategory.parent_id, under_partcategory.level+1
    FROM api_partcategory JOIN under_partcategory ON api_partcategory.parent_id=under_partcategory.id
    ORDER BY 2
  )
SELECT * FROM under_partcategory;
''', [id])

Note the %s substitution at the raw query.

👤Marcos Koetz

Leave a comment