Django query objects grouped on field

Question

If I have a django model like this:

class Person(models.Model):
    given_names = models.CharField(max_length=144)
    surname = models.CharField(max_length=144)

I want to construct a query that will return all the people in the database, grouped by their surnames. It should return something like this:

{
    'Smith': [<Person: Joseph A Smith>, <Person: Joseph B Smith>],
    'Bloggs': [<Person: Joseph A Bloggs>, <Person: Joseph B Bloggs>],
    ...
}

What's the best way to construct this query?

Is [<Person: Joseph A Smith>, <Person: Joseph B Smith>] list or queryset? — f43d65
– f43d65, Commented Jun 2, 2015 at 5:09
For my current case, it doesn't matter. Let's say queryset, for the sake of the argument. — Tom
– Tom, Commented Jun 2, 2015 at 5:26

f43d65 · Accepted Answer · 2015-06-02 06:24:14Z

1

from collections import defaultdict

result = defaultdict(list)
persons = Person.objects.all()
for person in persons:
    result[person.surname].append(person)

Problem is surnames like "SMith", "SMITH" or " Smith". In this case replacing result[person.surname]... with result[person.surname.strip().lower()]... can help.

edited Jun 2, 2015 at 6:24

answered Jun 2, 2015 at 5:35

f43d65

2,30213 silver badges15 bronze badges

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

Comments

YPCrumble · Accepted Answer · 2015-06-02 14:46:14Z

0

One way to do exactly what you're asking for is to create a queryset for each individual surname, loop through the surnames, and add them to a dict like so:

for name in Person.objects.values('surname').distinct():
    qs = Person.objects.filter(surname__icontains=name)
    your_dict[name] = qs

And add that to a dictionary.

You might also consider making surnames their own model with a foreignkey to Person. You can just loop through all the surnames like:

for s in Surname.objects.all():
    people = Person.objects.filter(surname=s.name)
    your_dict[s.name] = people

Last, you could drop to raw SQL, trying something like the following if you're using MySQL (you'd need something different for Postgres):

people = Person.objects.raw(
    'SELECT surname, GROUP_CONCAT(given_names) FROM personapp_person GROUP BY surname'
)

but I think the other solutions would be easier in the end.

edited Jun 2, 2015 at 14:46

answered Jun 2, 2015 at 1:13

YPCrumble

29.1k26 gold badges115 silver badges177 bronze badges

2 Comments

Tom Over a year ago

Hmmm. I was hoping for an answer that could do it all in one query. By the way, you can get all the distinct surnames directly with Person.objects.values('surname').distinct() - no need for the first loop.

YPCrumble Over a year ago

@Tom thanks, great feedback! I don't think this is possible using the ORM directly, but you could try raw SQL depending on your database type...updated my answer.

Collectives™ on Stack Overflow

Django query objects grouped on field

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related