I am trying to execute a Django ORM request in which there is LEFT JOIN ON (condition) AND (condition). But I don’t know how to do extra AND conditionby throwing JOINs in a long, long way.
Adding a Django filter with the second condition does not help - it ends as a WHERE clause at the end, not an AND clause in JOIN.
Is it possible to set the AND clause to the AND state in ORM, or should I just use the SQL statement? If possible, how do you do it?
What it costs for is an SQL query that 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;
This is what LEFT JOIN, that makes me sick.
My Django request is as follows:
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 does not insert into the LEFT JOIN, as I was hoping it would be magical.
Any ideas?
thank
John
PS . , . :
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)
...