Django ORM grouped count of children

Given the following models.py

class Parent(models.Model):
    name = models.CharField(max_length=100)

class Child(models.Model):
    parent = models.ForeignKey('Parent', related_name='children')
    status = models.CharField(max_length=10, choices=(('ok', 'ok'), ('fail', 'fail')))

I would like access on the parent model/view to the grouped counts of the parent's children.

eg

parent.num_ok, parent.num_failed

or

 parent.child_counts_per_status['ok']

The counting needs to be done in SQL as loading all children for all parents then counting them in memory is too much of an overhead (could have tens of thousands of children per parent)

If I were to write this outside a ORM I would do something like:

select parent.id, parent.name, child.status, count(*) from parent
inner join child on child.parent_id = parent.id
group by parent.id, parent.name, child.status

However seeing as I will be limiting the number of parents (via pagination) it may be ok to have:

select parent.* from parent where ... (page is)

then one execution per parent of:

select status, count(*) from child where parent_id = :parent_id
group by status

Are either of these options available via Django ORM?

Also if so .. how do I plug this into the object model? I am using Django Rest Framework and I am guessing the query would go into the views.py which currently looks like:

class ParentViewSet(viewsets.ModelViewSet):

    queryset = Parent.objects.all()

---

The following will annotate each parent object with the counts for both types of children in an attribute named num_ok and num_fail as you suggested.

This internally creates SQL almost identical to the SQL that you have suggested which leaves the counting up to the database and not done in Python nor Django.

from django.db.models import Count, Case, When, IntegerField

...

queryset = Parent.objects.annotate(
               num_ok=Count(Case(
                            When(children__status='ok', then=1),
                                output_field=IntegerField()))
           ).annotate(
               num_fail=Count(Case(
                              When(children__status='fail', then=1),
                                output_field=IntegerField())))

This will you allow to iterate over the Parent objects and retrieve the counts as follows:

for parent in queryset:
    print(parent.num_ok)
    print(parent.num_fail)

---

If you want to get count of 'ok' children for particular parent (say parent1), use

parent1.children.filter(status='ok').count()

If you need to count 'ok' children for all parents, then you can use annotate, so for example to print children count for each parents, you will use

from django.db.models import Count 

parents = Parent.objects.filter(children__status='ok').annotate(c_count=Count('children'))

for p in parents:
  print p.c_count

Respectively for queryset you will use

Parent.objects.filter(children__status='ok').distinct()

(we use distinct to eliminate duplication)

链接地址: http://www.djcxy.com/p/60214.html

上一篇: 广度第一VS深度第一

下一篇: Django ORM将孩子的数量分组