How to aggregate hierarchical data efficiently in Django without causing N+1 queries?

Question

I’m working with a hierarchical model structure in Django, where each level can represent a region, district, or village. The structure looks like this:

class Location(models.Model):
    name = models.CharField(max_length=255)
    parent = models.ForeignKey(
        'self',
        on_delete=models.CASCADE,
        related_name='children',
        null=True,
        blank=True
    )

    def __str__(self):
        return self.name

Each Location can have child locations (for example: Region → District → Village).

I also have a model that connects each location to a measurement point:

class LocationPoint(models.Model):
    location = models.ForeignKey(Location, on_delete=models.CASCADE)
    point = models.ForeignKey('Point', on_delete=models.DO_NOTHING, db_constraint=False)

And a model that stores daily or hourly measurement values:

import uuid

class Value(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    point = models.ForeignKey('Point', on_delete=models.DO_NOTHING, db_constraint=False)
    volume = models.FloatField(default=0)
    timestamp = models.DateTimeField()

Goal: I want to aggregate values (e.g., total volume) for each top-level region, including all nested child levels (districts, villages, etc.).

Example:

Region A → Total Volume: 10,000
Region B → Total Volume: 20,000

Problem: When I try to calculate these sums recursively (looping over children and summing their related Value records), the number of database queries increases dramatically — a classic N+1 query problem.

Question: How can I efficiently compute aggregated values across a hierarchical model in Django — for example, summing all Value.volume fields for every descendant location — without causing N+1 queries?

Additional Info:

Django 4.x
PostgreSQL
Preferably using Django ORM (but raw SQL with recursive CTEs is also acceptable)

Serhii Fomenko · Accepted Answer · 2025-10-30 13:14:20Z

I'm not sure if this can be done with ORM. In such difficult cases, I don't even try to use it. Although I know that there is a library called djano-cte that you can use to adapt my raw query using only ORM.

You haven't provided your Point model, so my answer is based somewhat on assumptions. A query for your purpose would look something like this:

WITH RECURSIVE cte AS (
    SELECT
        "name" AS root_region_name,
        parent_id,
        "id",
        "id" AS root_id
    FROM
        appname_location
    WHERE
        parent_id IS NULL
    UNION
    SELECT
        cte.root_region_name,
        appname_location.parent_id,
        appname_location.id,
        cte.root_id
    FROM
        appname_location
    JOIN
        cte
    ON
        appname_location.parent_id = cte.id
)

SELECT
    t1.root_region_name AS parent_region_name,
    t1.root_id AS parent_region_id,
    SUM(t4.volume) AS total_volume
FROM
    cte AS t1
JOIN
    appname_locationpoint AS t2
ON
    t1.id = t2.location_id
JOIN
    appname_point AS t3
ON
    t2.point_id = t3.id
JOIN 
    appname_value AS t4 
ON 
    t3.id = t4.point_id
GROUP BY
    t1.root_id,
    t1.root_region_name
ORDER BY
    total_volume DESC,
    parent_region_name,
    parent_region_id
;

Let's take a closer look at what's going on here. First, we need to select regions with parent_id IS NULL — these are our top-level regions. We recursively attach child regions to them, and we need a common key root_id (top-level region identifier) for subsequent grouping. I also used the top-level region name (root_region_name) to make it clearer.

The rest is a matter of technique: join the necessary tables and group by root_id and root_region_name. Since my query is based on some assumptions, replace the table and column names with the correct ones. My goal is to show you the idea of how to do this with a single query while avoiding the N + 1 problem.

Collectives™ on Stack Overflow

How to aggregate hierarchical data efficiently in Django without causing N+1 queries?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related