Django Left Outer Join

Question

I have a website where users can see a list of movies, and create reviews for them.

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.

They do not care at all about the scores provided by other users.

Consider the following models.py

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") 

What I essentially want is this

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

Consider the following test.py for context:

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) 

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

raw --- A 1 B None C 3

the orm instead returns this

orm --- A 1 C 3

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

orm --- A 1 A 4 B 5 C 3 C 6 

How can I accomplish the simple behavior of the raw query with the Django ORM?

edit: This sort of works but seems very poor:

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):     ...

edit: This works a little bit better, but still bad:

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

orm --- A 1 B 5 C 3

Answer 1

First of all, there is no a way (atm Django 1.9.7) to have a representation with Django's ORM of the raw query you posted, exactly as you want; however, you can get the same desired result with something like:

>>> Topic.objects.annotate(         f=Case(             When(                 record__user=johnny,                  then=F('record__value')             ),              output_field=IntegerField()         )     ).order_by(         'id', 'name', 'f'     ).distinct(         'id', 'name'     ).values_list(         'name', 'f'     ) >>> [(u'A', 1), (u'B', None), (u'C', 3)]  >>> Topic.objects.annotate(f=Case(When(record__user=may, then=F('record__value')), output_field=IntegerField())).order_by('id', 'name', 'f').distinct('id', 'name').values_list('name', 'f') >>> [(u'A', 4), (u'B', 5), (u'C', 6)] 

Here the SQL generated for the first query:

>>> print Topic.objects.annotate(f=Case(When(record__user=johnny, then=F('record__value')), output_field=IntegerField())).order_by('id', 'name', 'f').distinct('id', 'name').values_list('name', 'f').query  >>> SELECT DISTINCT ON ("payments_topic"."id", "payments_topic"."name") "payments_topic"."name", CASE WHEN "payments_record"."user_id" = 1 THEN "payments_record"."value" ELSE NULL END AS "f" FROM "payments_topic" LEFT OUTER JOIN "payments_record" ON ("payments_topic"."id" = "payments_record"."topic_id") ORDER BY "payments_topic"."id" ASC, "payments_topic"."name" ASC, "f" ASC 

##Some notes

• Doesn't hesitate to use raw queries, specially when the performance is the most important thing. Moreover, sometimes it is a must since you can't get the same result using Django's ORM; in other cases you can, but once in a while having clean and understandable code is more important than the performance in this piece of code.
• distinct with positional arguments is used in this answer, which is available for PostgreSQL only, atm. In the docs you can see more about conditional expressions.