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

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:

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 future. Is there any other way I can achieve the above sql using django orm??

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

from django.db.models import F

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

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.

Back to Top