python - 如何提取具有最大值的 Django 对象？

Question

假设我有一个 django 模型:

class Score(models.Model):
    name = models.CharField(max_length=32)
    value = models.IntegerField()
    status = models.BooleanField()

    def __unicode__(self):
        return u"{}: {} {}".format(self.name, self.value, self.status)

score 表看起来是这样的:

id | name  | value | status
---+-------+-------+-------
1  | john  | 15    | 0
2  | ivan  | 21    | 0
3  | david | 14    | 1
4  | john  | 11    | 1
5  | john  | 25    | 0
6  | david | 8     | 1

我想为作为名称列表给出的每个名称提取具有最大 value 的对象。例如:

names = ['john', 'david', 'paul']

dct = process(names) # How to implement this function???

for name, obj in dct.items():
    print name, obj

应该打印:

john john: 25 False
david david: 14 True
paul None

性能很重要。我应该处理大约 10k 个名称，每个名称在 score 中有大约 1k 条记录。用 django 做这件事的最好方法是什么？在此任务中使用 django ORM 有意义吗？

我使用 MySQL 数据库。

感谢提前。

Answer 1

解决方法:

def process(names):
    # Finding max scores
    result = Score.objects.filter(name__in=names).\
        values('name').annotate(Max('value'))

    value_max_items = set(
        row['value__max']
        for row in result
    )

    # Query for objects having max scores
    result = Score.objects.filter(value__in=value_max_items)

    # Creating dct
    dct = {}
    for obj in result:
        if obj.name not in dct or obj.value > dct[obj.name].value:
            dct[obj.name] = obj

    # If some names missed, set None for them
    for name in names:
        if name not in dct:
            dct[name] = None

    return dct