Django左外加 [英] Django Left Outer Join

问题描述

我有一个网站，用户可以看到电影列表，并为他们创建评论。

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.

考虑以下 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")

我本来想要的是这个

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

考虑以下 test.py 文本：

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测试应打印完全相同的输出，但只有原始版本吐出正确的结果表：

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

orm代替返回此

orm
---
A 1
C 3

任何尝试加入其余的主题，那些没有用户johnny的评论结果如下：

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

如何使用Django ORM完成原始查询的简单行为？

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

编辑：这种作品似乎很差： / p>

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

推荐答案

总而言之，没有办法（atm Django 1.9.7）您发布的原始查询， 正好 的Django的ORM 表示尽管如此，您可以获得相同的期望结果：

First of all, there is not 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; although, 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)]

这里为第一个查询生成的SQL：

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

一些笔记

• 毫不犹豫地使用原始查询，特别是当性能最重要的时候。而且，有时候它是必须的，因为你不能使用Django的ORM得到相同的结果;在其他情况下，您可以，但一次有一个干净和可理解的代码比这个代码中的性能更重要。


• <$ c $在这个答案中使用了具有位置参数的c> distinct ，这仅适用于PostgreSQL，atm。在文档中，您可以查看有关条件表达式的更多信息。



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.

这篇关于Django左外加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！