时间戳

首页 > 解决方案 > Django query for multiple Group By

django - Django query for multiple Group By

问题描述

A

id name city
1  A     USA
2 BB    UK
3 CC    USA

B
id house_color
1   RED
2   Blue
3   Green

i want to group by both the tables in Django So the result can be.

[
{
"city": "USA",
"RED": 1,
"Green": 1
},
{
"city": "UK",
"Blue": 1
}
] 

so far i have done this
b = B.objects.all()
b.annotate(city=F('A__id')).values("city","house_color").annotate(Count('house_color')))

can anyone please help me to get the desired result Thanks.

here id is a foreign key.

class A(CommonModel):
   id = models(primary_key=True, on_delete=models.CASCADE)
   city = models.CharField(max_length=20, null=False, default=None)

class B(CommonModel):
   id=models.OneToOneField('A',primary_key=True, on_delete=models.CASCADE)
    colour = models.CharField(max_length=20, null=False, default=None)

标签： djangodjango-models

解决方案

You can post-process the query. So here we can first query the database with:

qs = B.objects.values(city=F('id__city'), house_color=F('colour')).annotate(
    n=Count('house_color')
).order_by('city', 'house_color')

Next we can make a list of dictionaries with the groupby function from itertools:

from itertools import groupby
from operator import itemgetter

result = [
    {
        'city': k,
        **{v['house_color']: v['n'] for v in vs}
    }
    for k, vs in groupby(qs, itemgetter('city'))
]

推荐阅读