如何与Django进行分组和聚合

I have a fairly simple query I'd like to make via the ORM, but can't figure that out..

我有一个相当简单的查询，我想通过ORM进行查询，但是我不能算出来。

I have three models:

我有三个模型:

Location (a place), Attribute (an attribute a place might have), and Rating (a M2M 'through' model that also contains a score field)

位置(一个地方)、属性(一个地方可能具有的属性)和评级(一个M2M 'through’model，也包含一个score字段)

I want to pick some important attributes and be able to rank my locations by those attributes - i.e. higher total score over all selected attributes = better.

我想挑选一些重要的属性，并能够根据这些属性对我的位置进行排名——也就是说，在所有选定的属性中，总分越高=越好。

I can use the following SQL to get what I want:

我可以使用下面的SQL来获取我想要的:

select location_id, sum(score) 
    from locations_rating 
    where attribute_id in (1,2,3) 
    group by location_id order by sum desc;

which returns

它返回

 location_id | sum 
-------------+-----
          21 |  12
           3 |  11

The closest I can get with the ORM is:

我最接近ORM的是:

Rating.objects.filter(
    attribute__in=attributes).annotate(
    acount=Count('location')).aggregate(Sum('score'))

Which returns

它返回

{'score__sum': 23}

i.e. the sum of all, not grouped by location.

即所有的总和，不是按位置分组的。

Any way around this? I could execute the SQL manually, but would rather go via the ORM to keep things consistent.

解决这个问题的办法吗?我可以手动执行SQL，但我宁愿通过ORM来保持一致。

Thanks

谢谢

2 个解决方案

#1

102

Try this:

试试这个:

Rating.objects.filter(attribute__in=attributes) \
    .values('location') \
    .annotate(score = Sum('score')) \
    .order_by('-score')

#2

Can you try this.

你可以试试这个。

Rating.objects.values('location_id').filter(attribute__in=attributes).annotate(sum_score=Sum('score')).

#1

102