Group by in django create wrong query

Question

we have write down this query in django

ManageInterview.objects.filter
(post_id=request.data['job_id'])
.order_by('-id')
.annotate(total=Count('pitcher_id'))

After print this we got this query

SELECT *, COUNT(`pitcher_invitations`.`pitcher_id`) AS `total` FROM 
`pitcher_invitations` WHERE `pitcher_invitations`.`post_id` = 254555444
GROUP BY `pitcher_invitations`.`id` 
ORDER BY `pitcher_invitations`.`id` DESC

We are doing group by

pitcher_id

but django query group by

pitcher_invitations.id

we want this query

select * from `pitcher_invitations` where `post_id` =254555444
group by `pitcher_id` order by `id` desc

Django is not creating a wrong query. Your query set is not what you need. Why do you want to select all columns while you are grouping. — scriptmonster
– scriptmonster, Commented Oct 11, 2017 at 10:20

Harman · Accepted Answer · 2017-10-11 10:58:45Z

3

Try this:

query ="select * from `pitcher_invitations` where `post_id` = "+str(request.data['job_id'])+" group by `pitcher_id` order by `id` desc"

result = ManageInterview.objects.raw(query)

If anyone has good solution please share

answered Oct 11, 2017 at 10:58

Harman

1,7536 gold badges22 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

codeadict · Accepted Answer · 2017-10-11 14:40:43Z

1

You can't select * and group_by. If you wanna group pitcher_id and count records you don't need to provide the *, it can be done with:

from django.db.models import Count

query = ManageInterview.objects.filter(post_id=request.data['job_id'])
query = query.values('pitcher_id').annotate(total=Count('pitcher_id'))

answered Oct 11, 2017 at 14:40

codeadict

2,7531 gold badge16 silver badges11 bronze badges

Collectives™ on Stack Overflow

Group by in django create wrong query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related