分组在字段上的Django查询对象

If I have a django model like this:

如果我有一个django模型

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?

构造这个查询的最佳方法是什么?

2 个解决方案

#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.

问题是像“史密斯”、“史密斯”或“史密斯”这样的姓氏。在这种情况下，替换结果[人名.姓]……与结果[person.surname.strip().lower()]…能帮上忙。

#2

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

最后，您可以转到原始SQL，如果您使用MySQL，您可以尝试以下操作(对于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.

但我认为其他的解决方案最终会更容易。

#1

from collections import defaultdict

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