Django Orm - Left Outer Join With Two Columns?

This is the relevant code: class Book(models.Model): name = models.CharField(max_length=50) class BookNote(models.Model): text = models.CharField(max_length=50) book

Solution 1:

The reason your query doesn't work is you're expicitly asking for either Books with your user's notes or no notes at all: this excludes books where only other users have notes.

I think what you're looking for is best performed as an annotation. Under django 2.0+, you can use the new FilteredRelation to perform a LEFT OUTER JOIN ON (... AND ...), but I had trouble doing it and maintaining the ForeignKey in the ORM; you'll have to re-export the fields you need with additional annotations.

q =  Book.objects.all().annotate(
        usernote=FilteredRelation('booknote', condition=Q(booknote__user=USER_ID)),
        usernote_text=F('usernote__text'),
        usernote_id=F('usernote'),
        )

Resulting query:

SELECT"books_book"."id", "books_book"."name", usernote."text"AS"usernote_text", usernote."id"AS"usernote_id"FROM"books_book" LEFT OUTER JOIN"books_booknote" usernote ON ("books_book"."id" = usernote."book_id"AND (usernote."user_id" = <USER_ID>))

If you're using 1.11 still, you can get the same result (but less performance and different queries) with Prefetch objects or a case-when annotation.

In models.py:

classBook(models.Model):
     # SNIP     @propertydefusernote(self):
         # raises an error if not prefetchedtry:
             return self._usernote[0] if self._usernote elseNoneexcept AttributeError:
             raise Exception("Book.usernote must be prefetched with prefetch_related(Book.usernote_prefetch(user)) before use")

     @staticmethoddefusernote_prefetch(user):
         return Prefetch(
             'booknote_set',
             queryset=BookNote.objects.filter(user=user)
             to_attr='_usernote'
         )

By your query:

q = Book.objects.all().prefetch_related(Book.usernote_prefetch(USER))

Full tests.py:

# -*- coding: utf-8 -*-from __future__ import unicode_literals

from django.test import TestCase
from django.db.models import *
from books.models import Book, BookNote
from django.contrib.auth import get_user_model

classBookTest(TestCase):
    defsetUp(self):
        User = get_user_model()
        self.u1 = User.objects.create(username="U1")
        self.u2 = User.objects.create(username="U2")
        self.b1 = Book.objects.create(name="B1")  # Has no notes
        self.b2 = Book.objects.create(name="B2")  # Has a note for U1 and U2
        self.b3 = Book.objects.create(name="B3")  # Has a note for just U2
        self.n1 = BookNote.objects.create(text="N1", book=self.b2, user=self.u1)
        BookNote.objects.create(text="N2", book=self.b2, user=self.u2)
        BookNote.objects.create(text="N3", book=self.b1, user=self.u2)

    deftest_on_multiple(self):
        q =  Book.objects.all().annotate(
                usernote=FilteredRelation('booknote', condition=Q(booknote__user=self.u1)),
                usernote_text=F('usernote__text'),
                usernote_id=F('usernote'),
                ).order_by('id')
        print(q.query)
        self.assertEqual(q.count(), Book.objects.count())
        self.assertIsNone(q[0].usernote_text)
        self.assertEqual( q[1].usernote_text, self.n1.text)
        self.assertIsNone(q[2].usernote_text)

    deftest_on_multiple_prefetch(self):
        @propertydefusernote(self):
            return self._usernote[0] if self._usernote elseNone
        Book.usernote = usernote
        q = Book.objects.all().prefetch_related(Prefetch(
            'booknote_set',
            queryset=BookNote.objects.filter(user=self.u1),
            to_attr='_usernote'
            )).order_by('id')

        self.assertEqual(q.count(), Book.objects.count())
        self.assertIsNone(q[0].usernote)
        self.assertEqual( q[1].usernote.text, self.n1.text)
        self.assertIsNone(q[2].usernote)