Django's ORM has no CTE API; how would you load every employee under a given manager with a recursive CTE and still get Employee instances?

seniornice to knowfreq 22%

answer

raw() maps any SELECT to a model
depth column becomes an attribute
or feed the ids to an __in filter
guard against cycles

basics

~10 s

Write the WITH RECURSIVE query and pass it to Employee.objects.raw() with the manager id in params, selecting the primary key; or wrap it in RawSQL inside filter(pk__in=...) to keep a chainable QuerySet.

solid answer

~40 s

Django 6.1's QuerySet API has no common table expressions, so the recursion is hand-written. Option one: Employee.objects.raw("WITH RECURSIVE sub AS (... WHERE id = %s UNION ALL ... JOIN sub ...) SELECT e.*, sub.depth FROM hr_employee e JOIN sub ON sub.id = e.id ORDER BY sub.depth", [manager_id]) — you get Employee instances, depth arrives as an attribute, and prefetch_related() still works, but you cannot filter() or select_related() further. Option two: Employee.objects.filter(pk__in=RawSQL("WITH RECURSIVE ... SELECT id FROM sub", [manager_id])) keeps a real QuerySet for select_related, count(), pagination and further filters, at the cost of losing depth. In both, bind the manager id as a parameter, take the table name from Employee._meta.db_table rather than hard-coding it, and guard the recursion against cycles in bad data.

Django 6.1's QuerySet API has no common table expressions, so the recursion is hand-written. Option one: `Employee.objects.raw("WITH RECURSIVE sub AS (... WHERE id = %s UNION ALL ... JOIN sub ...) SELECT e.*, sub.depth FROM hr_employee e JOIN sub ON sub.id = e.id ORDER BY sub.depth", [manager_id])` — you get `Employee` instances, `depth` arrives as an attribute, and `prefetch_related()` still works, but you cannot `filter()` or `select_related()` further. Option two: `Employee.objects.filter(pk__in=RawSQL("WITH RECURSIVE ... SELECT id FROM sub", [manager_id]))` keeps a real `QuerySet` for `select_related`, `count()`, pagination and further filters, at the cost of losing `depth`. In both, bind the manager id as a parameter, take the table name from `Employee._meta.db_table` rather than hard-coding it, and guard the recursion against cycles in bad data.

code

python · 23 lines

python

from django.db import models
from django.db.models.expressions import RawSQL


class EmployeeQuerySet(models.QuerySet):
    def subordinates_of(self, manager_id):
        table = self.model._meta.db_table
        ids = RawSQL(
            f"WITH RECURSIVE sub AS (SELECT id FROM {table} WHERE manager_id = %s "
            f"UNION SELECT e.id FROM {table} e JOIN sub ON e.manager_id = sub.id) "
            "SELECT id FROM sub",
            (manager_id,),
        )
        return self.filter(pk__in=ids)


class Employee(models.Model):
    name = models.CharField(max_length=100)
    manager = models.ForeignKey(
        "self", null=True, blank=True, on_delete=models.SET_NULL, related_name="reports"
    )

    objects = EmployeeQuerySet.as_manager()

go deeper

for a junior

Recall that raw SQL can still return model instances through Manager.raw(), as long as the primary key is selected.

for a middle

Explain the recursive CTE shape and why the id travels in params while the table name comes from _meta.db_table.

for a senior

Choose between raw() and a RawSQL __in subquery based on whether callers need chaining or the depth column, and guard against cycles.

for a principal

Weigh per-request recursion against a materialised path or closure table, based on read/write ratio and how much raw SQL the team can maintain.

The situation

An Employee model has manager = models.ForeignKey("self", null=True, on_delete=models.SET_NULL, related_name="reports"). The report needs everyone under a manager, at any depth. Walking reports in Python costs one query per level (or per node); a recursive common table expression (CTE) does it in one statement. Django's QuerySet API in 6.1 has no CTE builder, so this is a textbook case for the raw-SQL escape hatches.

Option 1: `Manager.raw()` returning model instances

python

from hr.models import Employee

table = Employee._meta.db_table  # e.g. "hr_employee"; code-controlled, not user input
sql = f'''
WITH RECURSIVE sub AS (
    SELECT id, 0 AS depth FROM {table} WHERE id = %s
    UNION ALL
    SELECT e.id, sub.depth + 1
    FROM {table} e JOIN sub ON e.manager_id = sub.id
    WHERE sub.depth < 20
)
SELECT e.*, sub.depth FROM {table} e JOIN sub ON sub.id = e.id
ORDER BY sub.depth, e.id
'''
tree = Employee.objects.raw(sql, [manager_id]).prefetch_related("reports")

What you get and why:

Model instances. raw() maps columns to fields by name; e.* supplies every field including the primary key, which raw() requires.
depth as an attribute. Columns that match no field are set on each instance, so emp.depth is available for indentation.
Laziness. Nothing runs until iteration; prefetch_related() is supported on a RawQuerySet.
Limits. No filter(), exclude(), order_by() or select_related(); len() fetches all rows, and indexing happens in Python.

Option 2: `RawSQL` inside a normal QuerySet

python

from django.db.models.expressions import RawSQL

ids = RawSQL(
    f"WITH RECURSIVE sub AS (SELECT id FROM {table} WHERE id = %s "
    f"UNION SELECT e.id FROM {table} e JOIN sub ON e.manager_id = sub.id) "
    "SELECT id FROM sub",
    (manager_id,),
)
qs = Employee.objects.filter(pk__in=ids).select_related("department").order_by("last_name")

RawSQL is wrapped in parentheses and becomes the subquery of IN (...). PostgreSQL accepts a WITH clause inside a subquery, so the result is an ordinary QuerySet: chain filters, select_related(), count(), a Paginator, values(). You lose the depth column unless you add a second expression for it.

Comparing the two

	`raw()`	`filter(pk__in=RawSQL(...))`
Result	`RawQuerySet` of `Employee`	`QuerySet` of `Employee`
Extra columns such as `depth`	yes, as attributes	no
Further filtering, `select_related`, `count()`	no	yes
`prefetch_related()`	yes	yes

Details reviewers check

Parameters. The manager id is a value, so it goes in params with an unquoted %s. Only the table name is interpolated, and it comes from model metadata, never from the request.
Cycles. Bad data (A manages B, B manages A) makes UNION ALL recurse forever. Either use UNION, which discards rows already produced — this stops a cycle only when the recursive row is just the id, because a changing depth column makes every row new — or cap depth, as Option 1 does.
The root row. Decide whether the manager belongs in the result; filter depth > 0 if not.
Placement. Put the SQL in a custom manager or QuerySet method such as Employee.objects.subordinates_of(manager) so views never see it, and test it against the production database engine, since recursive CTE syntax varies.
Indexes. The recursive step joins on manager_id; the foreign key's index is what keeps each level cheap.

Testing the query

A recursive CTE is exactly the kind of SQL that passes review and fails on the first odd tree, so pin its behaviour with a test that builds a small hierarchy:

a manager with two levels of reports and a sibling branch that must not appear;
an employee with no manager (the root) and one with no reports (a leaf);
a deliberately cyclic pair, asserting the query terminates and returns each person once;
an assertion on the query count, confirming the whole tree loads in one statement plus any prefetches.

Run it against the same engine as production; SQLite and PostgreSQL both support WITH RECURSIVE, but details such as subquery placement and type coercion differ.

When not to recurse per request

If the tree is read far more often than it changes, a different storage shape — a materialised path column or a closure table maintained on writes — turns "all descendants" into a single indexed filter the ORM can express without raw SQL. That is a modelling decision; the CTE is the right answer when the hierarchy is an adjacency list you cannot change.

## The situation An `Employee` model has `manager = models.ForeignKey("self", null=True, on_delete=models.SET_NULL, related_name="reports")`. The report needs **everyone under a manager, at any depth**. Walking `reports` in Python costs one query per level (or per node); a recursive **common table expression** (CTE) does it in one statement. Django's QuerySet API in 6.1 has no CTE builder, so this is a textbook case for the raw-SQL escape hatches. ## Option 1: `Manager.raw()` returning model instances ```python from hr.models import Employee table = Employee._meta.db_table # e.g. "hr_employee"; code-controlled, not user input sql = f''' WITH RECURSIVE sub AS ( SELECT id, 0 AS depth FROM {table} WHERE id = %s UNION ALL SELECT e.id, sub.depth + 1 FROM {table} e JOIN sub ON e.manager_id = sub.id WHERE sub.depth < 20 ) SELECT e.*, sub.depth FROM {table} e JOIN sub ON sub.id = e.id ORDER BY sub.depth, e.id ''' tree = Employee.objects.raw(sql, [manager_id]).prefetch_related("reports") ``` What you get and why: - **Model instances.** `raw()` maps columns to fields by name; `e.*` supplies every field including the **primary key**, which `raw()` requires. - **`depth` as an attribute.** Columns that match no field are set on each instance, so `emp.depth` is available for indentation. - **Laziness.** Nothing runs until iteration; `prefetch_related()` is supported on a `RawQuerySet`. - **Limits.** No `filter()`, `exclude()`, `order_by()` or `select_related()`; `len()` fetches all rows, and indexing happens in Python. ## Option 2: `RawSQL` inside a normal QuerySet ```python from django.db.models.expressions import RawSQL ids = RawSQL( f"WITH RECURSIVE sub AS (SELECT id FROM {table} WHERE id = %s " f"UNION SELECT e.id FROM {table} e JOIN sub ON e.manager_id = sub.id) " "SELECT id FROM sub", (manager_id,), ) qs = Employee.objects.filter(pk__in=ids).select_related("department").order_by("last_name") ``` `RawSQL` is wrapped in parentheses and becomes the subquery of `IN (...)`. PostgreSQL accepts a `WITH` clause inside a subquery, so the result is an ordinary **`QuerySet`**: chain filters, `select_related()`, `count()`, a `Paginator`, `values()`. You lose the `depth` column unless you add a second expression for it. ## Comparing the two | | `raw()` | `filter(pk__in=RawSQL(...))` | |---|---|---| | Result | `RawQuerySet` of `Employee` | `QuerySet` of `Employee` | | Extra columns such as `depth` | yes, as attributes | no | | Further filtering, `select_related`, `count()` | no | yes | | `prefetch_related()` | yes | yes | ## Details reviewers check 1. **Parameters.** The manager id is a value, so it goes in `params` with an unquoted `%s`. Only the table name is interpolated, and it comes from model metadata, never from the request. 2. **Cycles.** Bad data (A manages B, B manages A) makes `UNION ALL` recurse forever. Either use `UNION`, which discards rows already produced — this stops a cycle only when the recursive row is just the id, because a changing `depth` column makes every row new — or cap `depth`, as Option 1 does. 3. **The root row.** Decide whether the manager belongs in the result; filter `depth > 0` if not. 4. **Placement.** Put the SQL in a custom manager or QuerySet method such as `Employee.objects.subordinates_of(manager)` so views never see it, and test it against the production database engine, since recursive CTE syntax varies. 5. **Indexes.** The recursive step joins on `manager_id`; the foreign key's index is what keeps each level cheap. ## Testing the query A recursive CTE is exactly the kind of SQL that passes review and fails on the first odd tree, so pin its behaviour with a test that builds a small hierarchy: - a manager with two levels of reports and a sibling branch that must **not** appear; - an employee with no manager (the root) and one with no reports (a leaf); - a deliberately cyclic pair, asserting the query terminates and returns each person once; - an assertion on the query count, confirming the whole tree loads in one statement plus any prefetches. Run it against the same engine as production; SQLite and PostgreSQL both support `WITH RECURSIVE`, but details such as subquery placement and type coercion differ. ## When not to recurse per request If the tree is read far more often than it changes, a different storage shape — a materialised path column or a closure table maintained on writes — turns "all descendants" into a single indexed filter the ORM can express without raw SQL. That is a modelling decision; the CTE is the right answer when the hierarchy is an adjacency list you cannot change.

Is interpolating Employee._meta.db_table into the SQL an injection risk?
No, because the value comes from model metadata defined in code, not from a request. Injection needs attacker-controlled text reaching the SQL string. The manager id, which does come from the request, still travels through `params`.
How do you show each employee's depth if you use the RawSQL __in approach?
The `__in` subquery returns only ids, so depth is lost. Either switch to `raw()`, where the extra `depth` column becomes an attribute, or annotate a second `RawSQL` subselect that computes depth for the outer row, accepting a more expensive query.

saying these in an interview costs you the question

Walks the reports relation recursively in Python, one query per node
Believes Django 6.1 has a built-in CTE method on QuerySet
Interpolates the manager id from the request into the SQL string
Uses UNION ALL with no depth limit on data that may contain cycles
Expects filter() to work on the result of Manager.raw()

Django's ORM has no CTE API; how would you load every employee under a given manager with a recursive CTE and still get Employee instances?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The situation

Option 1: Manager.raw() returning model instances

Option 2: RawSQL inside a normal QuerySet

Comparing the two

Details reviewers check

Testing the query

When not to recurse per request

▾common follow-ups2 questions

▾red flags5 flags

The situation

Option 1: Manager.raw() returning model instances

Option 2: RawSQL inside a normal QuerySet

Comparing the two

Details reviewers check

Testing the query

When not to recurse per request

answer

go deeper

Option 1: `Manager.raw()` returning model instances

Option 2: `RawSQL` inside a normal QuerySet

Option 1: `Manager.raw()` returning model instances

Option 2: `RawSQL` inside a normal QuerySet