0

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()
2
  • Can you show your model? from what I can see the problem is that it actually adds apply_review.id to both GROUP BY and output fields. Therefore COUNT(apply_review.id) should be always 1. Commented Jan 13, 2013 at 1:57
  • added above. I know I have an oddly named applicant field -- it should really be application_id. Have yet to change it. Commented Jan 13, 2013 at 2:07

1 Answer 1

1

I figured it out. I needed Count('review__applicant') instead of Count('review'). I figured out that those extra GROUP BY statements really have no effect, as they are in the SELECT clause as well.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.