Django: optimizing queries

Question

I want to list the number of items for each list. How can I find this number in a single query, rather than a query for each list?

Here is a simplified version of my current template code:

{% for list in lists %}
<li>
{{ listname }}:
{% with list.num_items as item_count %}
{{ item_count }} item{{ item_count|pluralize }}
{% endwith %}
</li>
{% endfor %}

lists is passed as: List.objects.filter(user=user)

and num_items is a property of the List model:

def _get_num_items(self):
    return self.item_set.filter(archived=False).count()
num_items = property(_get_num_items)

This queries SELECT COUNT(*) FROM "my_app_item" WHERE... n times, where n is the number of lists. Is it possible to make a single query here?

Ignacio, He is asking how to obtain the "grouped aggregates within a single query" which the documentation you point to, doesn't talk about. — lprsd
– lprsd, Commented Dec 31, 2010 at 6:46
BTW, you should make the title of your question a bit more specific, something along the lines of "inner join + count + where + group_by query using Django ORM" — madewulf
– madewulf, Commented Dec 31, 2010 at 10:45

lprsd · Accepted Answer · 2010-12-31 09:59:18Z

1

You should do this in your view and send the dictionary, instead.

Model.objects.values_list('item').annotate(Count('num_items'))

This will produce the SQL same as, (or equivalent to) the one you have posted.

answered Dec 31, 2010 at 9:59

lprsd

87.9k48 gold badges140 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

madewulf · Accepted Answer · 2010-12-31 11:01:02Z

In the following, I tried to take into account all your constraints: filtering on fields of List, on fields of Item, counting items , and grouping by list.

The solution I see is that you could use values() (here is the django doc about this : http://docs.djangoproject.com/en/dev/topics/db/aggregation/#values)

from django.db.models import Count
lists = list(List.objects.filter(user=user))
items=Item.objects.values(list).filter(archived=False,list__in=lists).annotate(count=Count("id")) 
#you will get a list of dicts of the form [{'count':2,'list':5},...] where 5 is the id of the list
#now, you can match you list with you item counts in python
list_items_count_dict={}
for item in items:
    list_items_count_dict[item['list']]=item['count']
for list in lists : 
    list.item_count = list_items_count_dict.get(list.id)

That will make only 2 queries, one for getting the lists, the other for computing the item counts. Afterwards, you will have two loops (that could probably be replaced by list comprehension one-liners ), but only for the lists you are interested in.

afterwards, in your template, you can use

{{list.item_count}}

There might be a more elegant option, but that is what I have found right now. I am also certain that you could reduce the number of query to one by using custom sql.

Disclaimer: I have not tested this code, but I have tested similar code on similar models. You could have problems because list is one of the keyword of the Python language.

Collectives™ on Stack Overflow

Django: optimizing queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related