Tango
Tango

Reputation: 145

Split queryset or get multiple querysets by field instead of just ordering by field

I want to query a database in a way such that rather than just ordering by some field, I get a separate QuerySet (or dictionary, list, whatever) for each unique value of that field. Hopefully the below example will help:

Suppose a model like

Class Person(models.Model):
   first_name = models.CharField()
   last_name = models.CharField

Calling Person.objects.all().order_by('last_name') gives me a single long QuerySet. I want instead to have a separate list for each unique last_name. So one list for every Person with last_name="Smith" and another list for every Person with last_name="Nguyen" etc.

Obviously I can't know ahead of time what last_names will be in the database, nor how many people will share a common last_name. Is there any fast, efficient, or automatic way to do this in django or do I just need to process the data myself after getting the one large queryset back?

Upvotes: 7

Views: 11000

Answers (3)

Samy Vilar
Samy Vilar

Reputation: 11130

you can get all the unique lastnames:

from django.db.models import Count
...
last_names = Person.objects.values('last_name').annotate(Count('last_name')) # This will return all the unique last_names
values = dict( ((last_name['last_name'], Person.objects.all().filter(last_name = last_name['last_name'])) for last_name in last_names if last_name['last_name__count']) )
# This will create a dictionary where the keys are all the unique names and the values are querysect of all the values that have that lastname

Daniel-Roseman is right it is pretty inefficient so heres a tweak version ...

from collections import defaultdict
values = defaultdict(list)
_ = map(lambda person: values[person.last_name].append(person), Person.objects.all())

note that _ = ... is so we don't print all the None on the terminal ;)

Upvotes: 1

Daniel Roseman
Daniel Roseman

Reputation: 599630

samy's code is correct, but pretty inefficient: you do n+1 queries (where n is the number of unique last names). Since you're going to be getting all the objects in the table anyway, you might as well do it in one go. Use this instead:

from collections import defaultdict
person_dict = defaultdict(list)
persons = Person.objects.all()
for person in persons:
    person_dict[person.last_name].append(person)

Upvotes: 13

Paulo Scardine
Paulo Scardine

Reputation: 77281

Look at the regroup tag.

{% regroup persons by last_name as surname_list %}

<ul>
{% for last_name in surname_list %}
    <li>{{ last_name.grouper }}
    <ul>
        {% for person in last_name.list %}
          <li>{{ person.last_name }}, {{ person.first_name }}</li>
        {% endfor %}
    </ul>
    </li>
{% endfor %}
</ul>

(this code is untested, please read the docs and fix any problem yourself)

Upvotes: 9

Related Questions