title: Django Left Outer Join
body: <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>