Get questions and overall results from two model classes

class InspectionQuestion(models.Model):
    item = models.ForeignKey(InspectionItem)
    question = models.CharField(max_length=200)
    question_pass = models.BooleanField()    

class InspectionResult(models.Model):
    question = models.ForeignKey(InspectionQuestion)
    vehicle = models.ForeignKey(Vehicle)
    result = models.BooleanField()

From the above class, I'd like to be able to get a list of 开发者_StackOverflow社区questions answered per vehicle, then for each question, show if the result has always been a pass (question_pass == result), fail (result == False), resolved (used to be a fail, but later on was changed to pass).

My main problem I guess is that each vehicle can have the same question answered several times, so I need a way to factor this in the query. I really don't have an idea on how to get this running, so I'm hoping someone has an idea. Thanks.

---

It seems some SQL has proved handy...if someone has an idea on how to do this in the Django ORM, I'd be happy to try their idea. Here's what I have so far:

I came across this handy link showing a how to run slightly complex SQL from Django. So using that, I used this query to pull up the questions and responses for each:

question_list = query_to_dict("""
  SELECT question_id, question, item, array_agg(pass_fail) AS results
  FROM "vehicles_inspectionresult"
  INNER JOIN "vehicles_inspectionquestion" ON 
  ("vehicles_inspectionresult"."question_id" = "vehicles_inspectionquestion"."id")
  INNER JOIN "vehicles_inspectionitem" ON 
  ("vehicles_inspectionquestion"."item_id" = "vehicles_inspectionitem"."id")
  WHERE "vehicles_inspectionresult"."vehicle_id" = %s
  GROUP BY item, question_id, question
  ORDER BY item;
""", vehicle.id)

The pass_fail variable is an addition to the InspectionResult class...it's for telling if a response is correct or not by comparing the question's answer with what was posted when saving.

The rest is mostly template code to group the questions into their specific item groups using Django's{% regroup %} template tag, but the main logic in my view is the above line. I hope this helps someone in future.