Joining Tables in Django ORM with Left Outer Join and Additional Condition

Question

I'm trying to perform a left outer join between two models in Django using the ORM. I need to include an additional condition within the ON clause of the join to filter the results based on specific criteria.

from django.db import models

class Author(models.Model):
    id = models.BigIntField(primary_key=True)
    name = models.CharField(max_length=50)
    book_title = model.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, null=True, db_column='author')

Is there a way to retrieve all the book objects along with the name of the author where the title and book_title are equal? The SQL will look like this:

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

Since the table is very large ( 13m ) I cannot resort to raw query because I will lose the ability to use PageNumberPagination. I don't want to use extra because this may lead to problems in the future. Is there any other way I can achieve the above SQL using Django orm??

Answer 1

You can work with an F object ^[Django-doc]:

from django.db.models import F

Book.objects.filter(title=F('author__book_title'))

or if you want to have something functionally equivalent as including it in the LEFT OUTER JOIN:

from django.db.models import Case, F, When

Book.objects.annotate(
    author_name=Case(
        When(title=F('author__book_title'), then=F('author__name')), default=None
    )
)

It however looks very strange that an Author has a book_title field. This would essentially imply an Author can only have one book? Even if that is somehow the case, it would be a form of data duplication, and it turns out that keeping data in sync is not as easy as it may look like.