Geek Answers Handbook

Django 1.7 left external join

I have such a model

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 find all tasks between a date range and show whether the user can be applied, has already been applied or has been rejected. Information about the application is in the application 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 to work without making a left join on the application table in the database. any suggestions on how to make it work.

thanks

+6
source share
1 answer

Django ORM executes LEFT OUTER JOIN when FK is NULLABLE .

Solution: just add null=True to this FK job = models.ForeignKey(Job, null=True, blank=True) that you want to join getting zeros, and Django will change the INNER JOIN with the LEFT OUTER JOIN.

This is logical because a left outer join makes sense when the target tables may not have the exact match prevailing on the left side of the table.

UPDATE: this only works for the ForeignKey and select_related fields, not for ManyToMany fields with prefetch_related.

Possible solution for M2M fields:

  • Debug generated SQL (DEBUG = True, django.db.backends log entry)
  • Copy it and replace INNER JOIN LEFT OUTER JOIN
  • Run Model.objects.raw (sql_with_left_join)

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

This should give the same results as before, but adding those Jobs that don't have M2M with Applied.

UPDATE2: Custom JOINS in Django for versions <= 1.5, do not work with 1.6 +

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

From StackOverflow: fooobar.com/questions/206841 / ...

+4
source

More articles:


All Articles