Django LEFT JOIN query with added AND clause: possible?

Question

I'm trying to do a Django ORM query that has a LEFT JOIN ON (condition) AND (condition) in it. But I don't know how to do the extra AND condition, throwing out the JOIN by a long, long way.

Adding a Django filter with the second condition doesn't help - it ends up as a WHERE clause at the end rather than an AND clause in the JOIN.

Is it possible to Join ON condition AND condition in the ORM, or should I just use a SQL statement? If it is possible, how do you do it?

For what it's worth, this is the SQL query I am trying to convert to Django:

SELECT 
  `editions_edition`.`name` AS edition,
  etc
FROM
  `editions_edition` 
INNER JOIN `surveys_survey` 
  ON (`editions_edition`.`survey_id` = `surveys_survey`.`id`) 
INNER JOIN `questions_question` 
  ON (`surveys_survey`.`id` = `questions_question`.`survey_id`) 
INNER JOIN `questionnaires_questionnaire`
  ON(`editions_edition`.`id`=`questionnaires_questionnaire`.`edition_id`) 
INNER JOIN `entities_entity` 
  ON (`entities_entity`.`id` = `questionnaires_questionnaire`.`entity_id`) 
LEFT JOIN `answers_answer` 
  ON (`answers_answer`.`question_id` = `questions_question`.`id`) 
  AND (`answers_answer`.`questionnaire_id` =`questionnaires_questionnaire`.`id`) 
WHERE `editions_edition`.`id` = *VARIABLE HERE* 
  AND `questions_question`.`type` > 99 
ORDER BY `entities_entity`.`name` ASC,`questions_question`.`sort_order` ASC;

It is that LEFT JOIN that is stumping me.

My Django query is like this:

query = Edition.objects
 .filter(questionnaires__edition__survey__questions__type__gte=100)
 .values(
         'name'
        ,'questionnaires'
        ,'questionnaires__entity__name'
        ,'questionnaires__edition__survey__questions'
        ,'questionnaires__edition__survey__questions__name'
        ,'questionnaires__answers__answer')
 .filter(questionnaires__answers__question=F('questionnaires__edition__survey__questions'))

But the last filter isn't inserting into the LEFT JOIN as I had hoped it would magically do.

Any ideas?

thanks

John

PS The models are using plural related_names for the reverse lookups. Those are the plurals you see in the queryset above. For instance:

class Questionnaire(models.Model):
  edition = models.ForeignKey(Edition,related_name='questionnaires')
  entity = models.ForeignKey(Entity)
  last_edited = models.DateTimeField(auto_now_add=False, auto_now=True)

class Question(models.Model):
  name = models.CharField('Item', max_length=255, unique=False, blank=True)
  survey = models.ForeignKey(Survey, related_name='questions')
  type = models.IntegerField(default=100,choices=QUESTION_TYPES)
  ...

class Answer(models.Model):
  question = models.ForeignKey(Question, related_name='answer')
  questionnaire = models.ForeignKey(Questionnaire, related_name='answers')
  answer = models.CharField(max_length=1024,blank=True)

class Edition(models.Model):
  name = models.CharField(max_length=100)
  slug = models.SlugField()
  survey = models.ForeignKey(Survey)
  ...

Answer 1

But the last filter isn't inserting into the LEFT JOIN as I had hoped it would magically do.

No, Django isn't a magician :)

Django ORM will make LEFT JOIN if you use select_related() under condition that the left side has a foreign key that allows NULL (it's nullable).

It could be something like:

Edition.objects.select_related('questionnaires').filter("//your searching criteria")

There is still the issue with the "AND condition", but I hope my answer can give you some clue how to proceed further.

The question is really interesting and I'll try to tinker a little bit tonight.

EDIT:

Here is a corresponding link to the Django documenation: https://docs.djangoproject.com/en/1.8/ref/models/querysets/#django.db.models.query.QuerySet.select_related

And here is what I have found on Google Groups: https://groups.google.com/forum/#!topic/django-users/CjHVVmu-00c