Django Left Outer Join
<p>I have a website where users can see a list of movies, and create reviews for them.</p>

<p>The user should be able to see the list of all the movies. Additionally, IF they have reviewed the movie, they should be able to see the score that they gave it. If not, the movie is just displayed without the score.</p>

<p>They do not care at all about the scores provided by other users.</p>

<p>Consider the following <code>models.py</code></p>

<pre><code>from django.contrib.auth.models import User
from django.db import models


class Topic(models.Model):
    name = models.TextField()

    def __str__(self):
        return self.name


class Record(models.Model):
    user = models.ForeignKey(User)
    topic = models.ForeignKey(Topic)
    value = models.TextField()

    class Meta:
        unique_together = ("user", "topic")
</code></pre>

<p>What I essentially want is this</p>

<pre><code>select * from bar_topic
left join (select topic_id as tid, value from bar_record where user_id = 1)
on tid = bar_topic.id
</code></pre>

<p>Consider the following <code>test.py</code> for context:</p>

<pre><code>from django.test import TestCase

from bar.models import *


from django.db.models import Q

class TestSuite(TestCase):

    def setUp(self):
        t1 = Topic.objects.create(name="A")
        t2 = Topic.objects.create(name="B")
        t3 = Topic.objects.create(name="C")
        # 2 for Johnny
        johnny = User.objects.create(username="Johnny")
        johnny.record_set.create(topic=t1, value=1)
        johnny.record_set.create(topic=t3, value=3)
        # 3 for Mary
        mary = User.objects.create(username="Mary")
        mary.record_set.create(topic=t1, value=4)
        mary.record_set.create(topic=t2, value=5)
        mary.record_set.create(topic=t3, value=6)

    def test_raw(self):
        print('\nraw\n---')
        with self.assertNumQueries(1):
            topics = Topic.objects.raw('''
                select * from bar_topic
                left join (select topic_id as tid, value from bar_record where user_id = 1)
                on tid = bar_topic.id
                ''')
            for topic in topics:
                print(topic, topic.value)

    def test_orm(self):
        print('\norm\n---')
        with self.assertNumQueries(1):
            topics = Topic.objects.filter(Q(record__user_id=1)).values_list('name', 'record__value')
            for topic in topics:
                print(*topic)
</code></pre>

<p>BOTH tests should print the exact same output, however, only the raw version spits out the correct table of results:</p>

<pre>raw
---
A 1
B None
C 3</pre>

<p>the orm instead returns this</p>

<pre>orm
---
A 1
C 3</pre>

<p>Any attempt to join back the rest of the topics, those that have no reviews from user "johnny", result in the following:</p>

<pre><code>orm
---
A 1
A 4
B 5
C 3
C 6
</code></pre>

<p>How can I accomplish the simple behavior of the raw query with the Django ORM?</p>

<p>edit: This sort of works but seems very poor:</p>

<pre>topics = Topic.objects.filter(record__user_id=1).values_list('name', 'record__value')
noned = Topic.objects.exclude(record__user_id=1).values_list('name')
for topic in chain(topics, noned):
    ...</pre>

<p>edit: This works a little bit better, but still bad:</p>

<pre>    topics = Topic.objects.filter(record__user_id=1).annotate(value=F('record__value'))
    topics |= Topic.objects.exclude(pk__in=topics)</pre>

<pre>orm
---
A 1
B 5
C 3</pre>