Geek asks and answers

ORM Tables and Django Closing

I am trying to simulate data that is organized as a hierarchical tree using a closure table. The entries that will represent the nodes in the tree are no different 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'

Paths between nodes are determined using the following closure table. It consists of FK for the node ancestor, FK for the node descendant, and the path length (i.e., Number of nodes) between the ancestor and the 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 get all the rows Regionand their parent node (i.e. where is RegionPath.PathLength = 1)? My SQL is a little 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 the Django QuerySet API would be greatly appreciated.

+5
1

:

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'

:

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

. , .distinct(), select_related(), .

+1

More articles:


All Articles