CODEWITHSUNDEEP
django
richflow
richflow

Reputation: 2144

django left outer join filter non-matching records

The goal is to find out if a Survey Response doesn't have current answers for all top level questions.

This could be done by getting all top level questions for the Response, and then filter out the questions which don't have current answers.

I can write this in SQL but is there a way I can implement this using django's QuerySet interface?

Models

class Survey(Model):
  ...

class SurveySection(Model):
  survey = ForeignKey(Survey, related_name='survey_sections')

class SurveyQuestion(Model):
  survey_section = ForeignKey(SurveySection, related_name='survey_questions')
  parent = ForeignKey('self') #questions can be nested

class SurveyResponse(Model):
  survey = ForeignKey(Survey, related_name='survey_responses')

class SurveyAnswer(Model):
  survey_response = ForeignKey(SurveyResponse, related_name='survey_answers')
  survey_question = ForeignKey(SurveyQuestion, related_name='survey_answer')
  is_current = BooleanField()

SQL

This should find all the top-level questions for the Survey the Response is for, getting the current answers that match those questions and removing the questions that don't have answers. 

select * from survey_surveyquestion question
join survey_surveysection section on section.id = question.survey_section_id
join survey_survey survey on survey.id = section.survey_id
join survey_surveyresponse response on response.survey_id = survey.id
left outer join survey_surveyanswer answer on answer.survey_question_id = question.id and answer.is_current = true
where response.id = 16
  and answer.id is null
  and question.parent is null

Upvotes: 0

Views: 90

Answers (1)

ruddra
ruddra

Reputation: 51988

You can take this approach:

First, get number of questions which has parent null:

top_question_count = SurveyQuestion.objects.filter(parent__is_null=True).count()

Then, use it in filter:

from django.db.models imprt Count

SurveyResponse.objects.filter(
    survey_answers__survey_question__parent__is_null=True,
    is_current=True
).annotate(
    top_level_questions=Count('survey_answers__survey_question')
).filter(
    top_level_questions=top_question_count
)

Upvotes: 1

Related Questions