Efficiently Left Joining Two Tables on Multiple Fields in Django

Question

I would like to left outer join on these tables.

class Author(models.Model):
    name = models.CharField(max_length=100, primary_key=True)
    date = models.CharField()
    other_field = models.CharField()

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    publication_date = models.CharField()

The sql query resulting from the django orm I am looking for should be like

select title, author, name as nam from book 
left outer join author on (book.author = author.id AND book.title = author.email)

How to achieve this? I tried using FilteredRelation but I am not getting the syntax correctly or is it impossible using django ORM ?

Answer 1

You can perform a left outer join using the annotate() and F() expressions.

Raw SQL Query:

select title, author.name as nam, publication_date
from book
left outer join author on book.author = author.name

from django.db.models import F

queryset = Book.objects.annotate(
nam=F('author__name')
).values('title', 'nam', 'publication_date')

Note: Django does not support SQL queries directly. If you want to see the SQL query of the above queryset.

print(str(queryset.query))

See more how to use F() expression: https://docs.djangoproject.com/en/5.0/ref/models/expressions/

AND clause, sample Query:

select title, author.name as nam, publication_date
from book
left outer join author on book.author = author.name
WHERE publication_date >= '2020-01-01' AND author.date = '2023-10-26'