django 1.7 left outer join

Question

I have a model like so

class Job(models.Model):
    description = models.CharField(max_length=255)
    user = models.ForeignKey(User)
    date = models.DateField()
    slot = models.CharField(max_length=10, choices=SLOT_CHOICES)
    location = models.ForeignKey(Location)        
    objects = JobManager()
    searches = geomodels.GeoManager()

    class Meta:
        verbose_name_plural = "Job"
        unique_together = ('date', 'slot', 'user')

    def __str__(self):
        return "{0}-{1}".format(self.user.first_name, self.date)

class Applied(models.Model):
    user = models.ForeignKey(User)
    job = models.ForeignKey(Job, null=True, blank=True)
    action_taken = models.BooleanField(default=False)
    is_declined = models.BooleanField(default=False)

    class Meta:
        verbose_name_plural = "Job Applications"
        unique_together = ('user', 'job', )

I want to search for all the jobs between a date range and show whether a user can apply, has already applied or has been declined. The application information is in applied model.

    jobs = Job.searches.filter(**kwargs)\
        .filter(date__range=(date_from, date_to),
                visibility=VisibilityStatus.PUBLIC,
                status=JobStatus.AVAILABLE)\
        .prefetch_related('applied_set')\
        .select_related('user__surgeryprofile__location')\
        .order_by('date')

But I can't get it work, its not doing a left join on applied table in the database. any suggestions how to get it working.

Thanks

Inner join will only bring the rows that exist in both tables, I want a left out join as I want all rows from job table between the date range and rows from applied table if the user has already applied for that job and if its been declined. — Chirdeep Tomar, Oct 05 '15 at 08:59
Your code should give you the right result (filtered jobs with a applied_set which might be empty) even though there are no joins. "prefetch_related in most cases will be implemented using an SQL query that uses the ‘IN’ operator." https://docs.djangoproject.com/en/1.7/ref/models/querysets/#prefetch-related — JimmyYe, Oct 07 '15 at 08:03
Not working though, query being generated doesn't even have any reference to Applied table. — Chirdeep Tomar, Oct 07 '15 at 21:03

score 4 · Answer 1 · edited May 23 '17 at 12:16

Django ORM perform LEFT OUTER JOIN when FK's are NULLABLE.

Solution: just add null=True on this FK job = models.ForeignKey(Job, null=True, blank=True) you wish to join getting nulls and Django will change INNER JOIN by LEFT OUTER JOIN.

This is logical, as left outer join has sense when the targeted tables may not have an exact match prevailing the most-left table in the query.

UPDATE: This only works for ForeignKey field and select_related, not for ManyToMany fields with prefetch_related.

Possible Solution for M2M fields:

Debug the SQL generated (DEBUG=True, logging with logger 'django.db.backends')
Copy it and replace INNER JOIN with LEFT OUTER JOIN
Perform Model.objects.raw(sql_with_left_join)

https://docs.djangoproject.com/en/1.8/topics/db/sql/#performing-raw-sql-queries

This should give same results than before but adding those Job that don't have M2M with Applied.

UPDATE2: Custom JOINS in Django for versions <=1.5, not working on 1.6+

Found in this blog entry: https://www.caktusgroup.com/blog/2009/09/28/custom-joins-with-djangos-queryjoin/

From StackOverflow: https://stackoverflow.com/a/12943120/1090700

I have updated the model as per your suggestion but still is not working. I am not getting any reference to applied table in the query. — Chirdeep Tomar, Oct 08 '15 at 20:09
This is probably due to ManyToMany, what I was telling was for select_related and ForeignKey, after testing it, checked that in M2M is not doing it. You would probably have to make an Job.objects.raw if you want to skip M2M with an LEFT OUTER JOIN (just debug the SQL generated and replace INNER JOIN by LEFT OUTER JOIN): https://docs.djangoproject.com/en/1.8/topics/db/sql/#performing-raw-sql-queries — danius, Oct 09 '15 at 10:00

django 1.7 left outer join

1 Answers1