Django 左外连接

新手上路,请多包涵

我有一个网站,用户可以在其中查看电影列表,并为它们创建评论。

用户应该能够看到所有电影的列表。此外,如果他们已经评论了这部电影,他们应该能够看到他们给这部电影的分数。如果没有,则只显示电影而不显示乐谱。

他们根本不关心其他用户提供的分数。

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

两个测试都应该打印完全相同的输出,但是,只有原始版本会吐出正确的结果表:

生的
---
一个 1
B 无
丙3

orm 反而返回这个

规范
---
一个 1
丙3

任何试图加入其余主题的尝试,那些没有来自用户“johnny”的评论的主题,都会导致以下结果:

 orm
---
A 1
A 4
B 5
C 3
C 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')
对于链中的主题(主题,无):
    ...

编辑:这个效果好一点,但仍然不好:

 topics = Topic.objects.filter(record__user_id=1).annotate(value=F('record__value'))
    主题 |= Topic.objects.exclude(pk__in=topics)
规范
---
一个 1
乙 5
丙3

原文由 RodericDay 发布,翻译遵循 CC BY-SA 4.0 许可协议

阅读 906
2 个回答

首先,没有办法(atm Django 1.9.7) 完全 按照您的意愿 使用您发布的原始查询 的 Django 的 ORM 进行表示;但是,您可以通过以下方式获得相同的期望结果:

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

 >>> 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 无法获得相同的结果;在其他情况下,您可以,但偶尔拥有干净且易于理解的代码比 这段 代码的性能更重要。
  • distinct 在此答案中使用了位置参数,仅适用于 PostgreSQL,atm。在文档中,您可以看到更多关于 条件表达式 的信息。

原文由 trinchet 发布,翻译遵循 CC BY-SA 4.0 许可协议

我本质上想要的是这个

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

...或者,也许这个等价物避免了子查询...

select * from bar_topic left join bar_record on bar_record.topic_id = bar_topic.id and bar_record.user_id = 1


* * *

> 我想知道如何有效地做到这一点,或者,如果不可能,解释为什么不可能......

除非您使用原始查询,否则使用 Django 的 ORM 是不可能的,这就是原因。

`QuerySet` objects ( `django.db.models.query.QuerySet` ) have a `query` attribute ( `django.db.models.sql.query.Query` ) which is a representation of the actual query which will be performed.这些 `Query` 对象有一个有用的 `__str__` 方法,所以你可以打印出来看看它是什么。

让我们从一个简单的开始 `QuerySet` ...

from bar.models import * qs = Topic.objects.filter(record__user_id=1) print qs.query SELECT “bar_topic”.“id”, “bar_topic”.“name” FROM “bar_topic” INNER JOIN “bar_record” ON (“bar_topic”.“id” = “bar_record”.“topic_id”) WHERE “bar_record”.“user_id” = 1


...由于 `INNER JOIN` ,这显然是行不通的。

深入了解 `Query` 对象,有一个 `alias_map` 属性决定将执行哪些表连接...

from pprint import pprint pprint(qs.query.alias_map) {u’bar_record’: JoinInfo(table_name=u’bar_record’, rhs_alias=u’bar_record’, join_type=‘INNER JOIN’, lhs_alias=u’bar_topic’, lhs_join_col=u’id’, rhs_join_col=‘topic_id’, nullable=True), u’bar_topic’: JoinInfo(table_name=u’bar_topic’, rhs_alias=u’bar_topic’, join_type=None, lhs_alias=None, lhs_join_col=None, rhs_join_col=None, nullable=False), u’auth_user’: JoinInfo(table_name=u’auth_user’, rhs_alias=u’auth_user’, join_type=‘INNER JOIN’, lhs_alias=u’bar_record’, lhs_join_col=‘user_id’, rhs_join_col=u’id’, nullable=False)}


请注意,Django 仅支持两种可能的 `join_type` s, `INNER JOIN` 和 `LEFT OUTER JOIN` ( [来源](https://github.com/django/django/blob/59bea9efd2768102fc9d3aedda469502c218e9b7/django/db/models/sql/constants.py#L22))。

现在,我们 _可以_ 使用 `LEFT OUTER JOIN` `Query` 对象 `promote_joins` `bar_record`

qs.query.promote_joins([‘bar_record’]) pprint(qs.query.alias_map) {u’bar_record’: JoinInfo(table_name=u’bar_record’, rhs_alias=u’bar_record’, join_type=‘LEFT OUTER JOIN’, lhs_alias=u’bar_topic’, lhs_join_col=u’id’, rhs_join_col=‘topic_id’, nullable=True), u’bar_topic’: JoinInfo(table_name=u’bar_topic’, rhs_alias=u’bar_topic’, join_type=None, lhs_alias=None, lhs_join_col=None, rhs_join_col=None, nullable=False), u’auth_user’: JoinInfo(table_name=u’auth_user’, rhs_alias=u’auth_user’, join_type=‘LEFT OUTER JOIN’, lhs_alias=u’bar_record’, lhs_join_col=‘user_id’, rhs_join_col=u’id’, nullable=False)}


...这会将查询更改为...

print qs.query SELECT “bar_topic”.“id”, “bar_topic”.“name” FROM “bar_topic” LEFT OUTER JOIN “bar_record” ON (“bar_topic”.“id” = “bar_record”.“topic_id”) WHERE “bar_record”.“user_id” = 1


...但是,这仍然没有用,因为连接将始终匹配一行,即使它不属于正确的用户,并且 `WHERE` 子句会将其过滤掉。

使用 `values_list()` 自动影响 `join_type` ...

qs = Topic.objects.filter(recorduser_id=1).values_list(‘name’, ‘recordvalue’) print qs.query SELECT “bar_topic”.“name”, “bar_record”.“value” FROM “bar_topic” LEFT OUTER JOIN “bar_record” ON (“bar_topic”.“id” = “bar_record”.“topic_id”) WHERE “bar_record”.“user_id” = 1


...但最终遇到同样的问题。

不幸的是,ORM 生成的连接存在一个基本限制,因为它们只能是以下形式...

(LEFT OUTER|INNER) JOIN ON (. = .)


...所以除了使用原始查询之外,真的没有办法实现你想要的 SQL。

当然,您可以使用 `annotate()` 和 `extra()` 之类的东西进行破解,但它们可能会生成性能远低于原始 SQL 的查询,并且可以说可读性不强。

* * *

> ...和建议的替代方案。

就个人而言,我只是使用原始查询...

select * from bar_topic left join bar_record on bar_record.topic_id = bar_topic.id and bar_record.user_id = 1 “`

…这很简单,可以与 Django 支持的所有后端兼容。

原文由 Aya 发布,翻译遵循 CC BY-SA 4.0 许可协议

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题