How can I get a total count of a model's related objects and the model's children's related objects?

Question

In Django, I've got a Checkout model, which is a ticket for somebody checking out equipment. I've also got an OrganizationalUnit model that the Checkout model relates to (via ForeignKey), as the person on the checkout belongs to an OrganizationalUnit on our campus.

The OrganizationalUnit has a self relation, so several OUs can be the children of a certain OU, and those children can have children, and so on. Here are the models, somewhat simplified.

class OrganizationalUnit(models.Model):
    name = models.CharField(max_length=100)
    parent = models.ForeignKey(
        'self',
        blank=True, null=True,
        related_name='children',
)

class Checkout(models.Model):
    first_name = models.CharField(max_length=100)
    last_name = models.CharField(max_length=100)
    department = models.ForeignKey(
        OrganizationalUnit,
        null=True,
        blank=True,
        related_name='checkouts',
)

I want to get a count of the Checkouts that are related to a certain OrganizationalUnit and all of its children. I know how to get the count of all the checkouts that are related to an OU.

ou = OrganizationalUnit.objects.get(pk=1)
count = ou.checkouts.all().count()

But how do I make that count reflect the checkouts of this OU's children and their children? Do I use some sort of iterative loop?

---

EDIT: I guess I still can't quite wrap my head around the while command to do this. The organizational units can go as deep as the user wants to nest them, but right now the most it goes in the DB is 5 deep. I've written this…

for kid in ou.children.all():
    child_checkout_count += kid.checkouts.all().count()
    for kid2 in kid.children.all():
        child_checkout_count += kid2.checkouts.all().count()
        for kid3 in kid2.children.all():
            child_checkout_count += kid3.checkouts.all().count()
            for kid4 in kid3.children.all():
                child_checkout_count += kid4.checkouts.all().count()
                for kid5 in kid4.children.all():
                    child_checkout_count += kid5.checkouts.all().count()

…which is total crap. And it takes a while to run because it pretty much traverses a major chunk of the database. Help! (I can't seem to think very well today.)

Answer 1

I'm not sure how does SQL perform on this one but what you want to do is exactly what you explained.

Get all OU and it's parents with While loop and then count Checkouts and sum them.

ORM brings you dynamic operations over SQL but kill performance:)

Answer 2

What you need is a recursive function that traverse OrganizationalUnit relation tree and gets number of related Checkouts for each OrganizationalUnit. So your code will look like this:

def count_checkouts(ou):
   checkout_count = ou.checkouts.count()
   for kid in ou.children.all():
       checkout_count += count_checkouts(kid)
   return checkout_count

Also note, that to get a number of related checkouts I use:

checkout_count = ou.checkouts.count()

insted of:

count = ou.checkouts.all().count()

My variant is more efficient (see http://docs.djangoproject.com/en/1.1/ref/models/querysets/#count).

Answer 3

I think the most efficient way of calculating this is at write time. You should modify OrganizationalUnit like this:

class OrganizationalUnit(models.Model):
    name = models.CharField(max_length=100)
    parent = models.ForeignKey(
        'self',
        blank=True, null=True,
        related_name='children',
    )
    checkout_number = models.IntegerField(default=0)

create the functions that will update the OrganizationalUnit and its parents at write time:

def pre_save_checkout(sender, instance, **kwargs):
    if isinstance(instance,Checkout) and instance.id and instance.department:
         substract_checkout(instance.department)

def post_save_checkout(sender, instance, **kwargs):
    if isinstance(instance,Checkout) and instance.department:
         add_checkout(instance.department)

def  substract_checkout(organizational_unit):
    organizational_unit.checkout_number-=1
    organizational_unit.save()
    if organizational_unit.parent:
        substract_checkout(organizational_unit.parent)

def  add_checkout(organizational_unit):
    organizational_unit.checkout_number+=1
    organizational_unit.save()
    if organizational_unit.parent:
        add_checkout(organizational_unit.parent)

now all you need is connect those functions to the pre_save, post_save and pre_delete signals:

from django.db.models.signals import post_save, pre_save, pre_delete

pre_save.connect(pre_save_checkout, Checkout)
pre_delete.connect(pre_save_checkout, Checkout)
post_save.connect(post_save_checkout, Checkout)

That should do it...