CODEWITHSUNDEEP
pythondjangodjango-templates
BALASCJP
BALASCJP

Reputation: 589

Select multiple fields with single group by in django

I used Django query as :

 sports.PYST.objects.using( 'sports-data' ).all().values('season','player','team').annotate(max_count = Max('punt_long') ).query

It gives o/p like :

SELECT `PYST`.`SEASON`, `PYST`.`PLAYER`, `PYST`.`TEAM`, MAX(`PYST`.`PUNT_LONG`) AS `max_count` FROM `PYST` GROUP BY `PYST`.`SEASON`, `PYST`.`PLAYER`, `PYST`.`TEAM` ORDER BY NULL

What I expected :

select season,player,team,max(punt_long)as punt_long from PYST group by season

Can any one help on this or need any additional information ?

Upvotes: 3

Views: 2756

Answers (2)

acarayol
acarayol

Reputation: 137

It's too late, but I hope it will work for someone today:

Model.objects.filter(name__in=["foo", "foo1"]).values('last_name')\
       .order_by('id')\
       .annotate(total=Count('id')).values('first_name')

Generate the following query:

SELECT "models_model"."first_name", COUNT("models_model"."id") AS "total" FROM "models_model" WHERE "models_model"."name" IN (...) GROUP BY "models_model"."last_name", "models_model"."author_id" ORDER BY "models_model"."id" ASC

Upvotes: 0

mariodev
mariodev

Reputation: 15549

I don't think this is possible without either:

  1. raw sql
  2. additional query to retrieve objects filtered by aggregation result (which is possible with little help from Q objects)

Edit 1:

Regarding solution no 2. This still may be not the best idea, but it's the quickest I could come up with:

from django.db.models import Max, Q
from operator import __or__ as OR

result_dict = Score.objects.values('season').annotate(Max('punt'))
q = [Q(season=row['season']) & Q(punt=row['punt__max']) for row in result_dict]
qs = Score.objects.filter(reduce(OR, q))

Check out this link for more details: http://css.dzone.com/articles/best-way-or-list-django-orm-q

Upvotes: 1

Related Questions