CODEWITHSUNDEEP
djangoleft-join
Christopher Broderick
Christopher Broderick

Reputation: 448

Implementing LEFT JOIN explicitly in Django

I have been thru endless posts on Django and the LEFT JOIN problem and but have not found anyone that has a solution in the 2.2 version of Django. Whilst in years past it seems there were ways to get around this problem, the Django team seem to have shut down every mechanism people have employed to achieve the most simple but necessary query - the LEFT JOIN. Here is a simle example of what I am trying to do (it is a simple example to reflect a real world scenario so please do not offer ways to redesign the models... all I want is the age old LEFT JOIN in SQL .... that simple):

from django.db import models
from uuid import uuid4
class People(models.Model):
    id = models.UUIDField(primary_key=True, editable=False, default=uuid4)
    name = models.CharField(max_length=255)


class Book(models.Model):
    id = models.UUIDField(primary_key=True, editable=False, default=uuid4)
    name = models.CharField(max_length=255)
    author = models.ForeignKey(People)
    publisher = models.ForeignKey(People, on_delete=models.PROTECT, null=True)

How would I achive an output showing all books and the publisher if there is a publisher (otherwise just blank)

Books.objects.filter(Q(publisher__isnull=True | Q(publisher__isnull=False)

... produces an INNER JOIN which will obviously only show books that have a publisher assigned. The query I am looking for would be of the form: select * from book LEFT JOIN people ON book.publisher_id=people.id

... or do I have to resort to raw SQL for this most simple of requirements?

Upvotes: 1

Views: 573

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476768

You can perform a .select_related(..) [Django-doc] here:

Book.objects.select_related('publisher')

This will fetch both the Book data and the related Publisher if it exists. So some_book.publisher where some_book originates from this queryset, will not result in an extra query to fetch the publisher. The query this will look like:

SELECT book.id, book.name, book.author_id, book.publisher_id,
       people.id, people.name
FROM book
LEFT OUTER JOIN people ON book.publisher_id = people.id

Upvotes: 1

Related Questions