I’ve written quite a lot of stuff about trees in Postgres, but not quite so much about how they might be used. One situation where they could be used is in categories: where each category may have sub-categories, and those may or may not have further sub-categories. When editing an object that has a single category, it would be useful to only show the values at each level, and then making a selection results in the display of another select element, that only has those values that apply at that level, and with that chain of parents selected.
Changing a selection should remove all subsequent items, and redisplay the “next” select with the new available options.
As you select different options from the “Category” select, you will see that only the leaf node is shown in the box on the right: but also (to show that it’s not just rendering the last value), we have a second “category” key/value pair from the text input.
This is a bit of a proof of concept: it doesn’t actually do an AJAX request: although there is a commented out fetch() request that shows where this would occur, but it doesn’t include the current selection.
The responses from the server would need to look something like:
INSERTINTOlocation(node_id,name,parent_id)VALUES(1,'Australia',NULL),(2,'South Australia',1),(3,'Victoria',1),(4,'South-East',2),(5,'Western Districts',3),(6,'New Zealand',NULL),(7,'Barossa Valley',2),(8,'Riverland',2),(9,'South Island',6),(10,'North Island',6),(11,'Eastern Bay of Plenty',10);
To begin with, we need to get all of the items, and their depth:
The first part of this query gets all of the deepest leaf nodes.
This is then combined with another recursive subquery, that creates branches. This relies on the fact it’s possible use the “type”, and have records as columns in a query. The second part of this subquery finds all remaining leaf nodes, and combines them in. This second subquery will keep executing until it doesn’t find any new rows, which will happen when all root nodes have been processed.
We can see from the results of this last clause that we just need to limit this to root nodes:
After writing up a method of using a Postgres View that generates a materialised path within the context of a Django model, I came across some queries of my data that were getting rather troublesome to write. It occurred to me that having a closure table would be useful. Specifically, I needed all of the descendants of a given set of nodes.
I couldn’t find an existing Postgres extension that will manage the closure table, and didn’t feel like writing my own implemention using triggers just yet. However, it occurred to me that I could use a similar trick to the recursive materialised path view. Thus, we have a Closure View.
This uses a recursive query. The first part builds the self-reference relations, and the second part uses the RECURSIVE function to collect child nodes for each node already in the table (or added in previous iterations of this part of the view).
Now, because we are using the in-built Django Many to Many features, we have some nice queries ready to go:
node.ancestors.all() : All ancestors of a given Node instance.
node.descendants.all() : All descendants of a given Node instance.
Node.objects.filter(ancestors=queryset) : All descendants of all nodes in a queryset.
Node.objects.filter(descendants=queryset) : All ancestors of all nodes in a queryset.
Of particular note are the bottom two: these are rather cumbersome to write in older versions of Django.
Today, I’m going to walk through modelling a tree in Django, using an Adjacency List, and a Postgres View that dynamically creates the materialised path of ancestors for each node.
With this, we will be able to query the tree for a range of operations using the Django ORM.
I’ve written this view before, so I won’t go into any detail.
We can create a tree. Normally I wouldn’t specify the primary key, but since we want to talk about those values shortly, I will. It also means you can delete them, and recreate with this code, and not worry about the sequence values.
Oh, that’s pretty nice. It’s not necessarily sorted, but it will do for now.
We can also query directly for a root:
Node.objects.filter(tree_node__root=10)
We could spell that one as tree_node__ancestors__0=10, but I think this is more explicit. Also, that one will not include the root node itself.
Deletions are also simple: if we can build a queryset, we can delete it. Thus, deleting a full tree could be done by following any queryset by a .delete()
Fetching a node’s ancestors is a little trickier: because we only have an array of node ids; thus it does two queries.
This is a fairly simple way to enable relatively performance-aware queries of tree data. There are still places where it’s not perfect, and in reality, you’d probably look at building up queryset or model methods for wrapping common operations.
It’s been a while, but I’ve finally gotten off my arsefound some time to revisit this series. As promised last time, I’m going to rewrite the queries from the Adjacency List “solutions” using a View. Indeed, there will be two versions of the view - one which is a MATERIALIZED VIEW. There will also be discussion of when the two different types of view might be best to use.
One of the reasons this post took so long to write was that I was sidetracked by writing an SVG generator that would allow for graphically seeing what the different operations discussed in this series look like in terms of an actual tree. That didn’t eventuate.
We will start by defining what our tree view will actually look like. You’ll notice is it rather like the CTE that we saw in the previous post.
All of the insertions do not require access to the tree view, since the beauty of an Adjacency List model is that you only ever need to operate on the immediate parent-child.
Removals
Similarly, we will skip over the simple operations: those don’t require access to any more of the tree than just the parent-child relationship. It’s not until we need to remove a subtree that it becomes interesting.
If you are paying attention, you will notice that this is virtually identical to the CTE version, except that we no longer need to redeclare the CTE each time. The full tree deletion is the same, as is removing all decscendants:
Again, the operations that don’t require the actual tree are unchanged: this is where the Adjacency List really shines.
Fetches
Since we are starting with the “full” tree, we should be able to use it for all of the queries. It is possible that these queries (unlike those we have seen before) may be slightly slower than the CTE version (specifically, those where the CTE is customised for that operation).
Descendants
Let’s get all of node 10’s descendants:
SELECTnode_idFROMtreeWHERE10=ANY(ancestors);
This query is far less complicated than the CTE version, as expected. However, when dealing with very large datasets, it performs far worse. I have a data set with 221000 nodes, in 1001 different trees. Performing this query takes around 5 seconds, but the customised CTE version takes about 750ms.
Getting a set of ancestors to a given depth is actually a little tricky: because we can’t just reverse the end that we add the parent node to the ancestors array, we can’t use that trick. We’ll have to enumerate the rows, and then extract those we care about. You can’t use OFFSET with a variable, otherwise that would be a nice trick.
None of the special queries access the tree in any way, so can be omitted for now.
Discussion
So how does using a view stack up to the ad-hoc CTE queries?
Mostly pretty well. In the case where you have only small data sets, then the view that builds up the complete tree each time is not that much of a problem (I ran some tests with tens of thousands of items, and it still performed relatively well). When it moves up to hundreds of thousands, then the ad-hoc CTE versions can greatly outperform the full tree view.
However, using a materialised view changes everything. It now becomes just as fast as querying a table: indeed, that’s just what it is. You could have triggers based on changes to the nodes table causing a REFRESH MATERIALIZED VIEW, but it is worth keeping in mind that this will take some time: in my case, a full refresh of 221000 rows took upwards of 4.5 seconds.
Using a materialised view gets us most of the way to (and leads nicely into the next method), storing a materialised path. The similarity of the names here should be a trigger, but now I’m just making foreshadowing jokes.
One of the nice things about Adjacency Lists as a method of storing tree structures is that there is not much redundancy: you only store a reference to the parent, and that’s it.
It does mean that getting that data in a nested object is a bit complicated. I’ve written before about getting data out of a database: I’ll revisit that again I’m sure, but for now, I’m going to deal with data that has the following shape: that is, has been built up into a Materialized Path:
You’ll see that each node is a 2-tuple, and each set of siblings is a list. Even a node with no children still gets an empty list.
We can build up this data structure in two steps: based on the fact that a dict, as key-value pairs, matches a 2-tuple. That is, we will start by creating:
{1:{2:{4:{},7:{},8:{},},3:{5:{},}},6:{},}
You might be reaching for python’s defaultdict class at this point, but there is a slightly nicer way:
We also need to build up the simple key-value store to pass as label_dict, but that’s pretty easy.
Let’s look at the full code: first the complete class:
classTree(dict):"""Simple Tree data structure
Stores data in the form:
{
"a": {
"b": {},
"c": {},
},
"d": {
"e": {},
},
}
And can be nested to any depth.
"""def__missing__(self,key):value=self[key]=type(self)()returnvaluedefinsert(self,node,ancestors):"""Insert the supplied node, creating all ancestors as required.
This expects a list (possibly empty) containing the ancestors,
and a value for the node.
"""ifnotancestors:self[node]else:self[ancestors[0]].insert(node,ancestors[1:])deflabel(self,labels,sort_key=lambdax:x[0]):"""Return a nested 2-tuple with just the supplied labels.
Realistically, the labels could be any type of object.
"""returnsorted([(labels.get(key),value.label(labels,sort_key))forkey,valueinself.items()],key=sort_key)
This is the second post in an ongoing series dealing with storing Hierarchical or Tree data structures in Postgres. You should read the Introduction if you haven’t already.
This post contains the queries that illustrate how an Adjacency List model can be used to represent a Hierarchical set of data, including data definitions, and the various operations that have been defined in the aforementioned introduction.
They can be modelled with most ORMs without any extra infrastructure
Many of the operations are non-complex
Recursive queries allow us to perform the complex queries in reasonable time
To help build suspense (but more because I haven’t yet come up with a way to generate a nice reproducible, yet complex tree), this post may discuss the complexity of the queries, but will not contain any measurements.
Initial tree
Before each operation, our data will look like this (where parents point to children):
We will assume reversion back to this structure after each operation: we could do this using a TRUNCATE followed by an INSERT; or we could run the operation in a transaction and rollback.
There may be a post which shows the effects of each of the queries below in a graphical form.
Table structure
Adjacency Lists are dead simple. Each node simply contains a reference to it’s parent node.
Inserting a single leaf node is simple. To insert a single node as a child of node 13, for example:
INSERTINTOnodes(parent_id)VALUES(13);
Inserting a new root node is slightly more complicated. In most ORMs, we would probably do it in two queries: one to create the new node, and a second to update the other root nodes to point to this one. We’ll see that below.
We can do slightly better in raw SQL: UPDATE our table with the result of an INSERT ... RETURNING that occurs inside a Common Table Expression (CTE, or WITH query).
And our last insert, adding a child node, that gets it’s siblings as children. For instance, adding a new node under node 12, which gets all of node 12’s children as it’s children.
All of these queries should perform relatively well: the CTE will be very simple (as it is no different to the single leaf insert), and the UPDATE should likewise be fairly simple: it needs to filter out which existing nodes do not need to be updated; and then it needs to update the remainder of the rows with the value pulled from the CTE.
This theoretically is only marginally more complex than just a simple UPDATE foo SET bar=baz WHERE quux IS NULL style query.
If we were using an ORM, we might need to do this in two queries: something like this (in Django):
# Insert new root node: all other root nodes now have this as a parentnew_node=Node.objects.create()Node.objects.filter(parent=None).exclude(pk=new_node.pk).update(parent=new_node)# Could possibly do as:Node.objects.filter(parent=None).update(parent=Node.objects.create().pk)# Insert new node, with a single child as it's child (and that child's previous# parent as it's parent)new_node=Node.objects.create(parent=old_node.parent)old_node.parent=new_nodeold_node.save()# Insert new node, with children of that node's parent now children of the node.new_node=parent_node.children.create()parent_node.children.exclude(pk=new_node.pk).update(parent=new_node)# Again, may be able to do:parent_node.children.update(parent=parent_node.children.create().pk)
Note the required exclusion of the newly created node: we don’t have to do this in the CTE versions, as that doesn’t “exist” at the time the other part of the query runs.
Removals
Removing a single leaf node is no different than removing a row from a normal table. Removing node 9, for instance:
DELETEFROMnodesWHEREnode_id=9;
Because the information about parent-child relationships is stored in the child, we do not need to do anything else to maintain the tree.
To remove a single root node (in this case, node 1), and promote all children to root nodes themselves, we can do two queries:
The same pattern can be used for removing a node, and attaching it’s children to it’s parent. Here, we will remove node 2, and attach it’s children (4 and 5) as children of it’s parent, node 1:
Righto, now we are up to the traditionally “hard” things for an Adjacency List to perform. Dealing with removing an arbitrary depth of (sub)tree.
We’ll need to create a recursive CTE, and delete according to that. Let’s just select from that initially, so we can see what the CTE data will look like:
Coolio. So, on to our operations. Let’s remove the subtree starting with node 2. I’ll hide the CTE, since it will be the same for quite a few of these operations:
Because the relationship is stored purely on the child element, moving around trees and subtrees is very easy. We can start with moving subtree starting with 3 to underneath node 4:
UPDATEnodesSETparent_id=4WHEREnode_id=3;
Nothing surprising there. Similarly, the query is identical for moving a leaf to a different parent, a root node into a tree, and turning a subtree into a full tree (making that node a root node).
The final move: all of node’s children to a different node is almost as simple:
UPDATEnodesSETparent_id=5WHEREparent_id=12;
This seems to be a situation where Adjacency Lists are really good. None of these queries are any more complex than the simplest UPDATE you could think of.
Fetches
Using the same CTE, we can perform our fetches. We may need to extend it to deal with depths, but since the ancestors column contains ancestors starting with the root node, we could count stuff in there. Let’s see how it goes.
Descendants
Fetching all descendants of a given node just means we want to see if the node occurs at all in each row’s ancestors. To get all of node 10’s descendants:
Obviously, this is far less generic, but it is also significantly less complex. For starters, it only builds up the part of the tree we care about, and then just returns the node ids, rather than building up the whole tree, and then discarding the parts that are not required.
The same code can be used for determining the number of descendants, but with a COUNT(node_id) in the final query.
To get our depth-limited query, we can approach from two directions. To get the subtree to depth 2 from above:
To do the equivalent of the hand-built CTE, we would need to start with the node, and build back the other way. It’s getting late here, so I can’t think of a way to do this right now that doesn’t get stuck doing infinite recursion.
The count query is an interesting one: we can just remove the need to unnest, and take the cardinality:
The depth query is a little trickier. We want to know the ancestors of node 15, up to a depth of 2. If our ancestors array was in the reverse order, we should be able to unnest and limit.
We can do this because a node only has one parent: so limiting the number of ancestors (when sorted nearest ancestor first) is the same as limiting the depth.
Special queries
Fetching all leaf nodes is just a matter of excluding those that have a relationship to another node as it’s parent:
Well, that’s our operations. Most of them are really simple. For anything that requires us to fetch or delete a whole subtree, we needed to revert to recursive CTEs, and for some of the other operations, using a CTE makes it simpler (and easier to understand).
Next, we will look at an alternative to the CTE operations, using a recursive view. From there, we should be able to look at a trigger-based approach that materializes our tree (node, ancestors) data in a table, and keeps it up to date. That’s, as I hinted, getting close to a Materialized Path approach, but keeps the conceptual simplicity of the Adjacency List, and hopefully prevents possible issues relating to referential integrity.
I’ve written before about using Adjacency Lists, and even done some performance tests on querying them. Whilst reading a post today, it occurred to me that it might be worthwhile to do a comparison of the various methods of storing hierarchical data in Postgres, and the costs of the same operations on each of those.
This post is just an introduction, with an outline of what I plan to do to run these tests. Please feel free to suggest things that I have missed, or that might be an oversight at my end.
Tree Models
There are four methods of storing the relationships that might form a tree. This analysis will be limited to actual tree, rather than graph structures (no cycles). I plan to detail the data structures in a series of posts, one per method. In each case, where there are multiple ways to store the data, I will attempt to examine each of these.
Adjacency Lists, being the simplest to understand (and the ones I have spent more time on recently), will be discussed first.
Path Enumerations will be next, with a comparison of storing the data using the ltree extension, and using an ARRAY column.
Following this, I’ll make an attempt at using the Closure Table model: where each ancestor-descendant relationship is stored, rather than just the parent-child relationship.
Finally, I’ll have a crack at the Nested Set model. I’m not solidly behind this model for the types of data I’ve had to deal with, but it is a valid mechanism for storing and retrieving this data. Besides, it will be an interesting exercise to implement.
My plan to handle all of these is that all tree manipulation should be “automatic”, that is, adding a node (or removing one, or whatever) should not require explicit updating of the various metadata. This should all be handled by trigger functions on the tables themselves. Whether this turns out to be reasonable we shall see.
Operations
I plan to perform the same set of operations (on the same data, rather than randomly generated data) in all models, and compare the complexity and run-time of the various queries. I’m hoping to cover all of the operations that might be performed on a tree structure, so please add any more to the comments.
The data stored in the table will contain more than one tree: this means we can perform operations which add/remove root nodes/whole trees.
Insertions
Insert a single leaf node
Insert a single root node (all existing root nodes will then point to this)
Insert a single node partway through the tree, with the “replaced” node becoming a child of this (and this one keeps it’s children)
Insert a single node partway through the tree, with this node’s parent’s existing children all becoming children of the new node.
Removals
Remove a single leaf node
Remove a single root node (all children of this are promoted to root nodes)
Remove a single node partway through the tree: all children of this node then have their grand-parent as their parent.
Remove a subtree (a single non-root node and it’s descendants)
Remove a whole tree (a single root node and all descendants)
Remove all descendants of a specific node (but not the node itself)
Moves
Move a subtree from one parent to another
Move a single leaf node to a different parent
Move a root node into a tree
Make a subtree into a tree (turn a node into a root node).
Move all children of a node to a different parent
Fetches
Fetch all descendants of a given node
Fetch the number of descendants of a given node
Fetch descendants of a given node to a given depth
Fetch the number of descendants of a given node to a given depth
Fetch all ancestors of a given node
Fetch the number of ancestors of a given node
Fetch ancestors of a given node to a given depth
Fetch the number of ancestors of a given node to a given depth I don’t think this makes any sense.
Yesterday, I wrote up some ideas about doing recursive queries on Adjacency Lists using Postgres. Today, I wrote up some code that allows me to run some tests on larger data sets. It’s worth noting that this is still somewhat “toy” data, but I did see comparable results with a real query.
Now, we want to be able to populate it with test data. This function will allow you to populate any number of records, with a 10% chance that any given record will be a root (have no parent). If it has a parent, it will be randomly selected from all existing rows. This means that earlier rows have a much higher chance of being a parent, and the first row is overwhemingly likely to have the most descendants (as it has a 90% chance that row 2 will have it as a parent, and therefore any descendants of that will also be descendants of row 1…)
CREATEORREPLACEFUNCTIONpopulate_nodes(countinteger)RETURNSvoidAS$$BEGINFORiIN2..countLOOPIF((SELECTcount(*)FROMnode)=0)or(random()<0.1)THENINSERTINTOnode(parent_id)SELECTNULL;ELSEINSERTINTOnode(parent_id)SELECTnode_idFROMnodeOFFSETrandom()*(SELECTcount(*)FROMnode)LIMIT1;ENDIF;ENDLOOP;END;$$LANGUAGEplpgsql;-- Let's stick 10k records in there
SELECTpopulate_nodes(10000);
For now, we want to find all descendants of node 1.
I can think of eleven ways we could write this query:
INNER JOIN with a RECURSIVE VIEW
Implicit CROSS JOIN with RECURSIVE VIEW, filtered using a WHERE clause
Sub-query with a RECURSIVE VIEW
INNER JOIN with a MATERIALIZED VIEW based on the RECURSIVE VIEW
Implicit CROSS JOIN (filtered) with MATERIALIZED VIEW based on RECURSIVE VIEW
Sub-query with MATERIALIZED VIEW based on RECURSIVE VIEW
RECURSIVE CTE, using an INNER JOIN
RECURSIVE CTE, using an implicit CROSS JOIN (filtered)
INNER JOIN with RECURSIVE CTE
Implicit CROSS JOIN (filtered) with RECURSIVE CTE
Subquery that is a RECURSIVE CTE
(Whilst some of these seem similar, we’ll see below how they differ).
In all cases, the actual query used for the tree will be:
It is extremely likely that the MATERIALIZED VIEW versions will be fastest: it is worth noting that in a very write-heavy environment (where you still need 100% up-to-date data), there would be an extra cost with the REFRESH MATERIALIZED VIEW.
As for which other ones will be fast (or fast enough), I would expect the CTE and VIEW versions to be roughly equivalent, as they appear to do the same amount of work. I’m not sure if the last three will perform as well as the others, as it seems that a “root” CTE would perform better than one later down the track.
So, let’s get underway. I wasn’t able to easily use the benchmark function I wanted to use, so I repeated each query five times and took the average.
So, it appears that Common Table Expressions are nearly twice as slow as using a RECURSIVE VIEW. I didn’t expect that at all, as I thought they were equivalent. Unsurprisingly, MATERIALIZED VIEW is much faster.
This has some implications for the stuff I was working on: I was using a query of the #11 form (a sub-query that is a WITH RECURSIVE statement), which, as it turns out is just as fast as doing a “root” CTE. However, it’s still far slower than doing a JOIN with a VIEW.
The problem I have now is that there is no way to have the addition of a Field to a Model in django to cause an extra migration operation to be added. One solution would be to manually add a RunSQL operation, but that is messy. I’ll also have to investigate costs of REFRESH MATERIALIZED VIEW.
I recently wrote about the excellent book SQL Antipatterns, and in it briefly discussed the tree structures. I’ve been thinking about trees in Postgres a fair bit lately, and a discussion on #django gave me further incentive to revisit this topic.
The book discusses four methods of storing a tree in a database.
Adjacency Lists, apart from the inability to grab a full or partial tree easily, are the simplest to understand. The child object stores a reference to it’s parent. Because this is a foreign key, then it always maintains referential integrity. Fetching a parent is simple, as is fetching all children, or siblings. It’s only when you need to fetch an arbitrary depth that things become problematic, unless you use a recursive query. More on that later.
Postgres has an extension called ltree, which provides an implementation of a Path Enumeration, but one thing that really bothers me about this type of structure is the lack of referential integrity. In practice, I’m not sure what having this ltree structure would give you over simply storing the keys in an ARRAY type. Indeed, if Postgres ever gets Foreign Key constraints for ARRAY elements (which there is a patch floating around for), this becomes even more compelling. It also seems to me that restructuring a tree becomes a bit more challenging in a Path Enumeration than an Adjacency List.
Nested Sets are also interesting, and maintain FK integrity, but require potentially rewriting lots of data when any change is made to the tree. They aren’t that appealing to me: perhaps I fail to see any big advantages of this structure.
Finally, Closure Tables are perhaps the most interesting. This stores all ancestor-descendant relationships, rather than just parent-child, which again requires more work when adding or removing. Again, Referential Integrity is preserved, but it seems like there is lots of work to maintain them.
From all of these, there are some significant advantages, in my mind, to using a simple Adjacency List.
Adding a new row never requires you to alter any other rows in the database.
Moving a subtree to a different location only requires a change to one now in the database.
It’s never possible to end up with Referential Integrity errors: the database will prevent you from deleting a parent row whilst it still has children (or, you may set it to CASCADE or SET NULL the children automatically).
It’s conceptually very simple. Everyone understands the parent-child relationship (and all of the relationships that follow, like grand-parents). It’s a similar mental model to how we think about our own families, except we don’t have exactly one parent.
There is really only two things that are hard to do:
Given a node, select all descendants of that node.
Given a node, select all ancestors of that node.
But, as we shall see shortly, it is possible to do these in Postgres using some nice recursive features.
There is another advantage to using an Adjacency List, this time from the perspective of Django. We can do it without needing to install a new package, or subclass or mix-in a new Model:
This actually performs relatively well, and, if it doesn’t do well enough, we could create a MATERIALIZED VIEW based on the recursive view, and query that instead (refreshing it whenever we need to, perhaps using a trigger).
This is pretty good now, but if we have cycles in our tree (yes, this makes it technically no longer a tree, but a graph, of which a tree is a restricted kind), this query will run forever. There’s a pretty neat trick to prevent cycles:
You don’t need to use the cycle column outside of the view.
The query used for the view can be repurposed into a Common Table Expression, which is basically a way of defining a view that only exists for the query we are executing (but will itself only be executed once, even if it’s referred to lots of times):
The problem we have with using the CTE immediately above is that we don’t have access to the full query at the time we are dealing with the filter. We could define the view prior to running the query (perhaps in a migration), but this means it’s more than just a simple field: although with the new migrations framework, we could make it so that makemigrations automatically adds a migration operation to create the recursive view.
The other solution is to still use a recursive CTE, but use it as a subquery. I’m still investigating if this will have poor performance characteristics.
Here is an implementation of doing just that:
fromdjango.dbimportmodelsSQL="""
WITH RECURSIVE "tree" ("{pk}", "related", "cycle") AS (
SELECT "{pk}", ARRAY[]::integer[], FALSE
FROM "{table}" WHERE "{fk}" IS NULL
UNION ALL
SELECT a."{pk}", b."related" || a."{fk}", a."{fk}" = ANY(b."related")
FROM "tree" b, "{table}" a
WHERE a."{fk}" = b."{pk}" AND NOT b."cycle"
) {query}"""classRecursiveRelation(models.ForeignKey):def__init__(self,*args,**kwargs):super(RecursiveRelation,self).__init__('self',*args,**kwargs)defget_lookup_constraint(self,constraint_class,alias,targets,sources,lookups,raw_value):iflookups[0]=='recursive':# With a recursive query, we want to build up a subquery that creates# the simplest possible tree we can deal with.data={'fk':self.get_attname(),'pk':self.related_fields[0][1].get_attname(),'table':self.model._meta.db_table}iflookups[-1]=='in':iftargets[0]==self:raw_value=ForeignKeyRecursiveInLookup(raw_value,**data)else:raw_value=ForeignKeyRecursiveReverseInLookup(raw_value,**data)else:iftargets[0]==self:raw_value=ForeignKeyRecursiveLookup(raw_value,**data)else:raw_value=ForeignKeyRecursiveReverseLookup(raw_value,**data)# Rewrite some variables so we get correct behaviour.# This makes the query based on the original table, not the joined version,# which was skipping a level of relation. It still joins the table, however,# which can't be great for performancealias=self.model._meta.db_table# This sets the correct lookup type, removing the recursive bit.lookups=lookups[1:]or['exact']returnsuper(RecursiveRelation,self).get_lookup_constraint(constraint_class,alias,targets,sources,lookups,raw_value)classForeignKeyRecursiveLookup(object):query='SELECT "{pk}" FROM "tree" WHERE %s = ANY("related")'def__init__(self,value,**kwargs):self.value=valueself.data=kwargsdefget_compiler(self,*args,**kwargs):returnselfdefas_subquery_condition(self,alias,columns,qn):sql=SQL.format(query=self.query.format(**self.data),**self.data)return'%s.%s IN (%s)'%(qn(alias),qn(self.data['pk']),sql),[self.value]classForeignKeyRecursiveInLookup(ForeignKeyRecursiveLookup):query='SELECT "{pk}" FROM "tree" WHERE %s && "related"'classForeignKeyRecursiveReverseLookup(ForeignKeyRecursiveLookup):query='SELECT unnest("related") FROM "tree" WHERE "{pk}" = %s'classForeignKeyRecursiveReverseInLookup(ForeignKeyRecursiveLookup):query='SELECT unnest("related") FROM "tree" WHERE "{pk}" IN %s'
If we were to use an existing view (created using a migration), then the structure would be largely the same: simply the SQL constant would be simpler:
SQL='SELECT {pk} FROM "{table}_{fk}_tree" WHERE {where}'
But then we would need some sort of name mangling for the view: I’ve suggested <tablename>_<fk-name>-tree.
I went into this exercise thinking it would be simple: just write a Lookup (or Transform), but it seems that Foreign Keys in django have a fair bit of special casing. There’s also a bit of lax code around the names of lookups: I may polish it up at some stage.