Django 左外连接 [英] Django Left Outer Join
问题描述
我有一个网站，用户可以在其中查看电影列表并为其创建评论.
用户应该能够看到所有电影的列表.此外，如果他们看过电影，他们应该能够看到他们给它的分数.如果没有，则只显示电影而不显示分数.
他们根本不在乎其他用户提供的分数.
考虑以下 models.py
from django.contrib.auth.models 导入用户从 django.db 导入模型类主题(模型.模型):名称 = 模型.TextField()def __str__(self):返回 self.name类记录(模型.模型):用户 = 模型.外键(用户)主题=models.ForeignKey(主题)值 = 模型.TextField()元类:unique_together = ("用户"，"主题")我真正想要的是这个
select * from bar_topic左连接(选择 topic_id 作为 tid，来自 bar_record 的值，其中 user_id = 1)在 tid = bar_topic.id考虑以下 test.py 作为上下文:
from django.test import TestCase从 bar.models 导入 *从 django.db.models 导入 Q类测试套件(测试用例):定义设置(自我):t1 = Topic.objects.create(name="A")t2 = Topic.objects.create(name="B")t3 = Topic.objects.create(name="C")#2 强尼johnny = User.objects.create(username="Johnny")johnny.record_set.create(topic=t1, value=1)johnny.record_set.create(topic=t3, value=3)#3 给玛丽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):打印('
原始
---')使用 self.assertNumQueries(1):主题 = Topic.objects.raw('''从 bar_topic 中选择 *左连接(选择 topic_id 作为 tid，来自 bar_record 的值，其中 user_id = 1)在 tid = bar_topic.id''')对于主题中的主题:打印(主题，主题.值)def test_orm(self):打印('规范
---')使用 self.assertNumQueries(1):主题 = Topic.objects.filter(Q(record__user_id=1)).values_list('name', 'record__value')对于主题中的主题:打印(*主题)两个测试都应该打印完全相同的输出，但是，只有原始版本会输出正确的结果表:
生的---1B 无C 3
orm 返回这个
orm---1C 3
任何尝试将其他主题(没有用户johnny"的评论)重新加入的尝试都会导致以下结果:
orm---1一个 4乙 5C 3C 6如何使用 Django ORM 完成原始查询的简单行为?
这种作品但看起来很差:
topics = Topic.objects.filter(record__user_id=1).values_list('name', 'record__value')noned = Topic.objects.exclude(record__user_id=1).values_list('name')对于链中的主题(主题，无):...这效果好一点，但仍然很糟糕:
主题 = Topic.objects.filter(record__user_id=1).annotate(value=F('record__value'))主题 |= Topic.objects.exclude(pk__in=topics)orm---1乙 5C 3
首先，没有一种方法(atm Django 1.9.7)可以使用 Django 的 ORM 来表示原始数据您发布的查询，完全，如您所愿；但是，您可以通过以下方式获得相同的预期结果:
<预><代码>>>>主题.objects.annotate(f=情况(什么时候(记录__用户=约翰尼，then=F('记录__值')),output_field=IntegerField())).order_by('id', 'name', 'f').清楚的(身份证"，姓名").values_list('名字'，'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:
<预><代码>>>>打印 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 为f"来自payments_topic"左外连接payments_record"ON ("payments_topic"."id" = "payments_record"."topic_id") ORDER BY "payments_topic"."id"ASC，payments_topic".name"ASC，f"ASC##一些笔记
- 毫不犹豫地使用原始查询，特别是当性能是最重要的事情时.此外，有时它是必须的，因为使用 Django 的 ORM 无法获得相同的结果；在其他情况下你可以，但偶尔拥有干净易懂的代码比这段代码的性能更重要.
distinct在这个答案中使用了位置参数，它仅适用于 PostgreSQL atm.在文档中，您可以看到更多关于条件表达式的信息.
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('
raw
---')
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('
orm
---')
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
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.
distinctwith 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屋！
