CODEWITHSUNDEEP
pythonmysqldjango
Sandeep
Sandeep

Reputation: 629

Group by in django create wrong query

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

Upvotes: 2

Views: 71

Answers (2)

codeadict
codeadict

Reputation: 2753

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'))

Upvotes: 1

Harman
Harman

Reputation: 1753

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

Upvotes: 3

Related Questions