[FIXED] Django: Hierarchy model query

Issue

Imagine there is a model:

class OrgUnit(models.Model):
    parent = models.ForeignKey(
        'self',
        on_delete=models.CASCADE,
        verbose_name=_('parent'),
        related_name='children',
        blank=True,
        null=True,
    )
    name = models.CharField(_('name'), max_length=255)
    type = models.CharField(_('type'), max_length=55, null=True, blank=True, db_index=True)
   

And hierarchy sample:

It is easy find all stores if one knows cluster id (cluster_id=1):

stores = OrgUnit.objects.filter(
    type='store',
    parent__parent_id=cluster_id
)

It is also easy find cluster by sales department id (sales_department_id=5):

cluster = OrgUnit.objects.select_related('parent__parent').get(pk=sales_department_id).parent.parent

And finally find stores for sales department:

cluster_id = OrgUnit.objects.select_related('parent').get(pk=sales_department_id).parent.parent_id
stores = OrgUnit.objects.filter(type='store', parent__parent_id=cluster_id)

Getting stores by sales department id will make 2 queries to database. I wonder to know whether it possible to fetch stores by sales department id in one query? If yes how?

Solution

You can move the hierarchy down again with the children, so querying the ForeignKey relation in reverse:

stores = OrgUnit.objects.filter(
    type='store',
    parent__parent__children__children__pk=sales_department_id
)

Here we thus query for OrgItems that have a parent that has a parent for which there is a child for which there is a child with as primary key the sales_department_id.

Answered By - Willem Van Onsem