Django ORM and Closure Tables

Question

I'm trying to model data that's organised as a hierarchical tree using a closure table. The entries that will represent nodes in the tree are nothing fancy and are defined as follows.

class Region(models.Model):
    RegionGuid = models.CharField(max_length=40, unique=True, db_column='RegionGUID', blank=True)
    CustomerId = models.IntegerField(null=True, db_column='CustomerID', blank=True)
    RegionName = models.CharField(max_length=256, db_column='RegionName', blank=True)
    Description = models.TextField(db_column="Description", blank=True)
    class Meta:
        db_table = u'Region'

The paths between the nodes are defined using the following closure table. It consists of a FK to the ancestor node, FK to the descendant node and the path length (i.e. number of nodes) between Ancestor and Descendant:

class RegionPath(models.Model):
    Ancestor = models.ForeignKey(Region, null=True, db_column='Ancestor', blank=True)
    Descendant = models.ForeignKey(Region, null=True, db_column='Descendant', blank=True)
    PathLength = models.IntegerField(null=True, db_column='PathLength', blank=True)
    class Meta:
        db_table = u'RegionPath'

Now how would I retrieve all Region rows and their respective parent node (i.e. where the RegionPath.PathLength = 1)? My SQL is slightly rusty but I think the SQL query should look something like this.

SELECT r.* from Region as r 
LEFT JOIN 
(SELECT r2.RegionName, p.Ancestor, p.Descendant from Region as r2 INNER JOIN RegionPath as p on r2.id = p.Ancestor WHERE p.PathLength = 1) AS Parent
on r.id = Parent.Descendant

Any help in expressing this using Django's QuerySet API would be much appreciated.

Particia

not sure if much of a help as it's a different language (php/codeigniter) but I had a play with implementing closure tables, and maybe it'll give you some ideas. gist.github.com/dazld/2174233 – Langill

Thanks for the link, but I don't think that helps me. I can work out the logic of the query and I can write the query if in raw SQL if necessary. I'm just stumped by the Django QuerySet API. – Particia

Is there any particular reason you need to use closure tables here? There's a very nice Django implementation of MPTT, for example, which solves the same problem. – Algophobia

My Django app integrates with a legacy database that is not Django orientated, so a Django only solution isn't ideal. In addition to that, closure tables are generally a better way of implementing tree like structures with flat tables. See #8196675. – Particia

Answer 1

By adding related_name to the foreign keys like so:

class RegionPath(models.Model):
    Ancestor = models.ForeignKey(Region, null=True, db_column='Ancestor', blank=True, related_name="ancestor")
    Descendant = models.ForeignKey(Region, null=True, db_column='Descendant', blank=True, related_name="descendants")
    PathLength = models.IntegerField(null=True, db_column='PathLength', blank=True)
    class Meta:
        db_table = u'RegionPath'

You can make queries for either relation:

children = Region.objects.filter(ancestors__PathLength=1)
parents = Region.objects.filter(descendants__PathLength=1)

I made my test on a very similar model. You might have to add .distinct(), you might want to select_related() to reduce queries.

Neuromuscular