How can I get the total number of objects associated with a model and objects associated with child objects of a model?

Question

In Django, I have a Checkout model that is a ticket for someone checking equipment. I also have an OrganizationalUnit model, which includes the Checkout model (via ForeignKey), since the person at the checkout belongs to OrganizationalUnit on our campus.

OrganizationalUnit is related to itself, therefore several departments can be children of a certain department, and these children can have children, etc. Here are some simplified models.

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 Checkouts account associated with a specific OrganizationalUnit and all its children. I know how to get an account of all OU related checks.

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

, OU ? - ?

---

. , while, . , , 5 . ...

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()

... . , . ! ( , , .)

Answer 1

, . OrganizationalUnit :

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)

, OrganizationalUnit :

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)

pre_save, post_save pre_delete:

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)

...

Answer 2

, OrganizationalUnit OrganizationalUnit. , :

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

, :

checkout_count = ou.checkouts.count()

insted of:

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

(. http://docs.djangoproject.com/en/1.1/ref/models/querysets/#count).

Answer 3

, SQL , , , , .

OU While, Checkouts .

ORM provides you with dynamic SQL operations, but it kills performance :)