django-tree-queries: adjacency lists queried with recursive CTEs

An adjacency list, queried as a tree

The project description is precise: adjacency-list trees for Django using recursive common table expressions, supporting PostgreSQL, sqlite, MySQL and MariaDB. The documentation is at django-tree-queries.readthedocs.io.

The design decision is that the stored data stays boring. There is no nested set column, no path column to maintain on every move, and no separate tree table. Your model has a parent foreign key, exactly as you would write it by hand, and the tree structure is materialised when you query.

That is a real trade. A materialised path or nested set is fast to read and awkward to write, because moving a subtree means rewriting the paths of every descendant. An adjacency list is trivial to write and awkward to read, because answering 'give me all descendants' means a recursive query. This library moves the difficulty to the read path, where SQL's recursive CTEs are good at it, and leaves the write path alone.

The practical consequence is that the tree fields do not exist until you ask for them. The README says to call the with_tree_fields() queryset method if you require the additional fields respectively the CTE, and to create a manager using TreeQuerySet.as_manager(with_tree_fields=True) if you want the tree fields added by default. That is the explicit opt-in mentioned in the package description, tree queries with explicit opt-in, without configurability, and it means a plain query on your model costs exactly what it always did.

The package has no runtime dependencies at all, which the pyproject states as an empty dependencies list, so installing it adds nothing to your dependency tree beyond the library itself. The author is Matthias Kestenholz, and there is a blog post linked from the README offering background on why the library exists.

The audience is a Django developer with a category tree, a comment thread, a permission hierarchy or an org chart, who wants the ancestor and descendant queries without adopting a tree library that changes how they write models.

The three fields the CTE adds, and why not to trust them

The fields added by the common table expression always are tree_depth, tree_path and tree_ordering, and the names cannot be changed.

tree_depth is an integer, and the README is careful about the definition, saying the library uses the correct definition of depth, where root nodes have a depth of zero. That is the detail that trips people up, because an off-by-one in depth is the kind of thing that produces an off-by-one in a pagination calculation three layers up.

tree_path is an array of primary keys representing the path from the root to the current node, including the current node itself. So for a three level tree the path array has three entries, and the last entry is the node's own key. The membership test that defines a descendant is then a containment check on that array.

tree_ordering is an array of values used for ordering nodes within their siblings at each level of the tree hierarchy. It is a per level array rather than a single value, which is what lets a subtree be moved without rewriting the ordering of the nodes below it, and it is the field that carries the sibling ordering described later.

Then the warning, and it is the important one: the contents of the tree_path and tree_ordering are subject to change, and you shouldn't rely on their contents. The library is free to change the representation, because the fields are an implementation detail of the query rather than a contract. So a query that filters on tree_path contents is relying on something the project has explicitly disclaimed.

The other documented limits are equally concrete. Only integer and UUID primary keys are supported, for now. The parent foreign key must be named parent at the moment, with the README adding a rhetorical aside about why you would want to name it differently. And the package adds only queryset methods for ordering siblings and filtering ancestors and descendants, with the note that other features may be useful but will not be added just because it is possible to do so.

Sibling ordering, and the depth-first default

By default, nodes are returned according to the depth-first search algorithm, which is the ordering you want when you are rendering a tree in a UI. The README notes that an explicit order_by() replaces that ordering, and that this generally means you are no longer getting a tree, just a list of nodes with tree fields attached.

That warning is worth taking seriously, because the two are easy to confuse. The tree fields still compute, so a query with order_by() and with_tree_fields() returns perfectly valid tree_depth and tree_path values, and the result is a flat list in whatever order you asked for. If your code then assumes sibling adjacency, it breaks in a way that only shows up when the ordering changes.

The alternative to the default is order_siblings_by("field_name"), which orders the siblings of a node by a model field. The README's recipe section shows the manual version, where the model declares its own position field and ordering:

from tree_queries.models import TreeNode

class Node(TreeNode):
    name = models.CharField(max_length=100)
    position = models.PositiveIntegerField(default=0)

    class Meta:
        ordering = ["position"]

The README's comment on the default says the default is to order siblings by their primary key, and that this is not always very useful, which is the honest reason for the whole feature.

There is also an automatic version. OrderableTreeNode automatically assigns sequential position values to new nodes:

from tree_queries.models import OrderableTreeNode

class Category(OrderableTreeNode):
    name = models.CharField(max_length=100)
    # position field and ordering are inherited from OrderableTreeNode

When a node is created without an explicit position, the position is set ten units higher than the maximum position among its siblings. The increment of ten rather than one is deliberate, and the README explains why: it makes it explicit that the position values themselves have no inherent meaning, they are purely for relative ordering, not a sibling counter or an index.

That is a small, well thought out detail. A step of one would invite the assumption that position 3 means the third child, and inserting a sibling in the middle would then require renumbering.

The README also documents how to customise the Meta class when you need a verbose name or extra ordering fields, by inheriting from OrderableTreeNode.Meta rather than from object.

Filtering before the tree is built

For large tables, the README recommends tree_filter() and tree_exclude() for better performance, and explains why in one sentence: these filter the base table before building the tree structure.

That is the key performance idea in the library. A recursive CTE walks the whole hierarchy and computes paths for every node it visits, so a filter applied after the CTE is computed has already paid for the traversal of the entire tree. Applying the filter to the base table first means the CTE only has to walk the subset that survives, and in a table where most rows are irrelevant to a given view, the difference is between a full scan and a fraction of one.

The same reasoning explains the performance optimization described in the feature list. The library automatically detects simple cases, meaning single field ordering, no tree filters and no custom tree fields, and uses an optimized CTE that avoids creating a rank table, which the README says significantly improves performance for basic tree queries. A rank table is the extra structure a general query needs to compute sibling ordering across levels, and when the query is simple enough that structure is not needed, skipping it removes a join from the plan.

There is also tree_fields(), which aggregates ancestor field values into arrays, so you can ask for a node together with the values of a field on all its ancestors in one query rather than making a query per level.

The documentation is candid about its own state. The README says that until the documentation is more complete it refers the reader to the test suite for additional instructions and usage examples, and the test suite is linked. That is a real signal for an adopter: the recipes in the README cover the common cases, and the edge cases are documented by example in the tests rather than in prose.

Database support, the fifty level limit, and what the library will not do

The support matrix is stated precisely, and one entry has a condition attached.

PostgreSQL and sqlite3 are supported, with sqlite3 at version 3.8.3 or higher. MariaDB is supported at 10.2.2 or higher. MySQL is supported at 8.0 or higher, if running without ONLY_FULL_GROUP_BY. That condition is not incidental, because the recursive CTE in MySQL needs to group intermediate results, and the default sql_mode on MySQL 8 rejects a query whose SELECT list is not functionally dependent on the grouping columns. A deployment that has tightened its sql_mode will find that the MySQL path does not work, which is a realistic production configuration and worth checking before you adopt the library.

The MySQL and MariaDB limit is the other one: only trees with a maximum of fifty levels are supported there, since those databases do not support arrays and require the library to provide a maximum length for tree_path and tree_ordering upfront. On PostgreSQL and SQLite, where arrays are native, there is no such ceiling. Fifty levels is far beyond any real category tree, so the limit rarely binds, but the reason it exists tells you how the implementation differs per backend.

Then the design philosophy, which the README states in a way that is unusual to see. Besides adding the fields mentioned above, the package only adds queryset methods for ordering siblings and filtering ancestors and descendants, and other features may be useful but will not be added to the package just because it is possible to do so. Combined with the description, tree queries with explicit opt-in, without configurability, that is a clear statement of scope: this is a small library that does one thing well.

The data integrity argument is worth noting too. The README says there is little code and that it is relatively simple compared to other tree management solutions for Django, with no redundant values, so the only way to end up with corrupt data is by introducing a loop in the tree structure, making it a graph. The TreeNode abstract model class has some protection against this, using model validation, and the recipes section says the common table expression could be hardened against such loops but that this would involve a performance hit which the maintainer does not want, so it is a documented limitation, a non-goal, of the library.

The state of the project, and where it came from

The licence is BSD-3-Clause, with the text in the LICENSE file at the root of the repository, and the pyproject metadata carries the same licence plus the OSI approved BSD classifier.

The support matrix for the library itself is broad. It requires Python 3.10 or later, and the classifiers list Python 3.10 through 3.15, and it requires Django 4.2 or better, with the README pointing at the GitHub Actions build for more detail on the supported combinations. The optional extras are minimal, an admin extra that adds django-js-asset for the admin integration and a tests extra with pytest, pytest-django, pytest-cov and coverage. The runtime dependency list is empty.

The development tooling is modern and thorough. The build is hatchling with the version read from tree_queries/__init__.py, so the package version and the source tree cannot drift apart. The linting is Ruff with a long list of enabled rule groups, including flake8-builtins, bugbear, comprehensions, mccabe, flake8-django, boolean trap, logging format, isort, gettext and pep8-naming, and with a target version of py310. There is a tox.ini, a pre-commit configuration, a readthedocs configuration and a mise.toml for tool versions, plus an AGENTS.md and a CHANGELOG.rst.

The maintenance record is healthy. The last push to the repository was on 2026-10-20, the project is not archived, and the CHANGELOG.rst is the upgrade record since the repository publishes no GitHub releases.

One small thing to note is that the project has moved organisation. The repository is now under feincms, and the package name and the pyproject metadata still point at the earlier home at matthiask, which is where the CI badge, the homepage and the link to the test suite in the README also point. If you are following the project, the code has moved and the links have not, which is worth knowing before you file an issue in the wrong place.

Editorial conclusion

Adopt django-tree-queries if you have a hierarchical model in Django and want ancestor and descendant queries without a nested set column or a separate tree library, since the CTE approach keeps the write path ordinary and the read path expressive. Do not adopt it if you need tree data in a database other than the four supported, if your primary keys are not integers or UUIDs, or if you want a general purpose tree toolkit, because the README says the package deliberately adds only sibling ordering and ancestor or descendant filtering. Verify first that the parent foreign key can be named parent in your model, that your MySQL or MariaDB deployment does not enforce ONLY_FULL_GROUP_BY, since the MySQL support is conditional on that, and that the tree_depth, tree_path and tree_ordering fields do not collide with names already in your models, because the field names cannot be changed.

Frequently asked questions

Official sources

1. feincms/django-tree-queries on GitHub
2. Issues
3. License: BSD-3-Clause
4. Project website
5. README