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

Answer 1

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:

1. Debug the SQL generated (DEBUG=True, logging with logger 'django.db.backends')
2. Copy it and replace INNER JOIN with LEFT OUTER JOIN
3. 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