CODEWITHSUNDEEP
pythondjangodjango-views
user5914374
user5914374

Reputation:

Django group by multiple fields

I want to transform this SQL request into a Django view:

SELECT color, shape, eyes, count(1) FROM animals WHERE master_id = 1 GROUP BY color, shape, eyes

In order to do that I've tried:

animals = Animals.objects.filter(id=1).values('color', 'shape', 'eyes').annotate(ct=Count('color'), dg=Count('shape'), br=Count('eyes'))

And then loop on the result to find the count for each one (not very optimized) and the result isn't good. That's group by as I want but doesn't care about the id.

EDIT:

<QuerySet [
    { 'color': brown, 'shape': blabla, 'eyes': toto, 'ct': 2 },
    { 'color': black, 'shape': blabla, 'eyes': toto, 'ct': 1 },
    { 'color': yellow, 'shape': xxxxx, 'eyes': ok, 'ct': 4 }
]>

SECOND EDIT:

If I try that:

Animals.objects.filter(
    master_id=1
).values('color', 'shape', 'eyes').annotate(
    ct=Count('id')
).order_by('color', 'shape', 'eyes')

I have this result without count:

<QuerySet [
    { 'color': brown, 'shape': blabla, 'eyes': toto},
    { 'color': black, 'shape': blabla, 'eyes': toto},
    { 'color': yellow, 'shape': xxxxx, 'eyes': ok }
]>

LAST EDIT:

The Animal table doesn't have count or ct column but I need it in my result

Upvotes: 4

Views: 4905

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

A complete equivalent query would be:

Animals.objects.filter(
    master_id=1
).values('color', 'shape', 'eyes').annotate(
    ct=Count('id')
).order_by('color', 'shape', 'eyes')

This will then result in a QuerySet that wraps dictionaries, for example:

<QuerySet [
    { 'color': 0, 'shape': 2, 'eyes': 1, 'ct': 1 },
    { 'color': 0, 'shape': 3, 'eyes': 3, 'ct': 4 },
    { 'color': 1, 'shape': 0, 'eyes': 0, 'ct': 2 },
    { 'color': 2, 'shape': 2, 'eyes': 1, 'ct': 5 },
]>

For example:

>>> Animals.objects.create(color='foo', shape='bar', eyes='brown', master_id=1)
<Animal: Animal object (2)>
>>> Animals.objects.create(color='foo', shape='bar', eyes='brown', master_id=1)
<Animal: Animal object (3)>
>>> Animals.objects.create(color='foo', shape='bar', eyes='blue', master_id=1)
<Animal: Animal object (4)>
>>> Animals.objects.create(color='foo', shape='rectangle', eyes='brown', master_id=1)
<Animal: Animal object (5)>
>>> Animals.objects.create(color='red', shape='rectangle', eyes='brown', master_id=1)
<Animal: Animal object (6)>
>>> Animals.objects.filter(
...     master_id=1
... ).values('color', 'shape', 'eyes').annotate(
...     ct=Count('id')
... ).order_by('color', 'shape', 'eyes')
<QuerySet [{'color': 'foo', 'shape': 'bar', 'eyes': 'blue', 'ct': 1}, {'color': 'foo', 'shape': 'bar', 'eyes': 'brown', 'ct': 2}, {'color': 'foo', 'shape': 'rectangle', 'eyes': 'brown', 'ct': 1}, {'color': 'red', 'shape': 'rectangle', 'eyes': 'brown', 'ct': 1}]>

Upvotes: 1

Related Questions