I am using Django. I have three different models, with this parent-child layout:
Applicant->Application->Review
An applicant can have multiple applications which can have multiple reviews.
I'm trying to write a query with first name, last name, application ID, and the number of reviews that are associated with a person, which could be none to several. I have the following query statement:
list = MUSApplicant.objects.values(
'applicant', 'id', 'applicant__first_name',
'applicant__last_name', 'review').filter(department=department).annotate(acount=Count('review'))
which is giving me this SQL:
SELECT apply_application.applicant_id,
apply_application.id,
apply_applicant.first_name,
apply_applicant.last_name,
apply_review.id,
COUNT(apply_review.id) AS acount
FROM apply_application
INNER JOIN apply_applicant
ON (apply_application.applicant_id = apply_applicant.id)
LEFT OUTER JOIN apply_review
ON (apply_application.id = apply_review.applicant_id)
WHERE apply_application.department = 'departmentname'
GROUP BY apply_application.applicant_id, apply_application.id, apply_applicant.first_name, apply_applicant.last_name, apply_review.id
Which is very close, but the result is not correct -- I end up with too many rows that aren't grouped correctly. I only want it to group by apply_review.id, not everything else. How can I keep it from adding all this extra stuff to my GROUP BY statement?
I've stared at the docs endlessly to no avail. It's very hard sometimes to match your exact data model to the few examples on there, in order to learn anything.
EDIT: Here's the model:
class Review(models.Model):
reviewer_eid = models.CharField(max_length=20)
applicant = models.ForeignKey('Application')
overall_score = models.FloatField()
category1 = models.FloatField()
category2 = models.FloatField()
category3 = models.FloatField()
comments = models.TextField()
apply_review.idto bothGROUP BYand output fields. ThereforeCOUNT(apply_review.id)should be always 1.