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  

2 answers

  • answered 2017-10-11 10:15 Harman

    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 2017-10-11 10:15 codeadict

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