Django中使用内连接(子查询)

目录

• 在Django中使用内连接(子查询)
  • Subquery()
  • Exists()

在Django中使用内连接(子查询)

Subquery()

模型类可能如下所示：

class Category(models.Model):
    name = models.CharField(max_length=100)


class Hero(models.Model):
    # ...
    name = models.CharField(max_length=100)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)

    benevolence_factor = models.PositiveSmallIntegerField(
        help_text="How benevolent this hero is?",
        default=50
    )

你用以下方式找到最勇敢的英雄：

hero_qs = Hero.objects.filter(
    category=OuterRef("pk")
).order_by("-benevolence_factor")
Category.objects.all().annotate(
    most_benevolent_hero=Subquery(
        hero_qs.values('name')[:1]
    )
)

使用OuterRef("pk")，这里的意思是找到Hero表中category_id与Category表中id相等的数据，其实就相当于内连接

如果你查看生成的sql，你会看到：

SELECT "entities_category"."id",
       "entities_category"."name",

  (SELECT U0."name"
   FROM "entities_hero" U0
   WHERE U0."category_id" = ("entities_category"."id")
   ORDER BY U0."benevolence_factor" DESC
   LIMIT 1) AS "most_benevolent_hero"
FROM "entities_category"

我们来分解下请求集的逻辑。第一步是：

hero_qs = Hero.objects.filter(
    category=OuterRef("pk")
).order_by("-benevolence_factor")

我们正在给 Hero 以倒序方式排序，并且使用 category=OuterRef("pk") 来声明我们会在子查询中用到它。

然后我们使用 most_benevolent_hero=Subquery(hero_qs.values('name')[:1]) 以获得 Category 查询集的子查询。 hero_qs.values('name')[:1] 选取了子查询中的一个名称。

Exists()

Exists 是一个 Subquery 子类，它使用 SQL EXISTS 语句。在许多情况下，它的性能比子查询更好，因为当找到第一条匹配的记录时，数据库能够停止对子查询的执行。

from django.db.models import Exists, OuterRef
from datetime import timedelta
from django.utils import timezone
one_day_ago = timezone.now() - timedelta(days=1)
recent_comments = Comment.objects.filter(
     post=OuterRef('pk'),
     created_at__gte=one_day_ago,
 )
Post.objects.annotate(recent_comment=Exists(recent_comments))

上面的查询是找出Comment表的post_id等于Post表的id的数据，并且Comment中的创建时间要大于一天

可以用 ~Exists() 来查询 NOT EXISTS。

query = A.objects.filter(
        ~Exists(
            B.objects.filter(
                old_uuid=OuterRef("uuid"), description=2
            )
        ),
       date=2012-07-13,
    )

上面的查询是在A表中找出没有与B表的关联的且description=2的数据， 然后再筛选出时间为2012-07-13的数据
这里的A表和B表并没有ForeignKey那种外键关系，只是使用的逻辑外键，所以只要两个表有关联，就能使用

另一个案例：

class User(models.Model):
    email = models.EmailField()

class Report(models.Model):
    user = models.ForeignKey(User)

过滤所有拥有以“a”开头的电子邮件且没有报告的用户。

User.objects.filter(
    ~Exists(Reports.objects.filter(user=OuterRef('pk'))),
    email__startswith='a'
)

sql语句为

SELECT user.pk, user.email
FROM user
WHERE NOT EXISTS (SELECT U0.pk FROM reports U0 WHERE U0.user = user.pk) AND email LIKE 'a%';