7. 如何在Django中执行子查询表达式?¶
Django允许使用SQL子查询。
让我们从一些简单的东西开始,我们有一个UserParent
模型,它与auth用户有OnetoOne
关系。 我们将找到具有UserParent
的所有UserParent
。
>>> from django.db.models import Subquery
>>> users = User.objects.all()
>>> UserParent.objects.filter(user_id__in=Subquery(users.values('id')))
<QuerySet [<UserParent: UserParent object (2)>, <UserParent: UserParent object (5)>, <UserParent: UserParent object (8)>]>
现在换一些更复杂的东西。 For each Category
, we want to find the most benevolent Hero
.
The models look something like this.
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
)
You can find the most benevolent Hero like this
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]
)
)
If you look at the generated sql, you will see
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"
Let’s break down the queryset logic. The first part is
hero_qs = Hero.objects.filter(
category=OuterRef("pk")
).order_by("-benevolence_factor")
We are ordering the Hero
object by benevolence_factor
in DESC order, and using
category=OuterRef("pk")
to declare that we will be using it in a subquery.
Then we annotate with most_benevolent_hero=Subquery(hero_qs.values('name')[:1])
, to get use the subquery with a Category
queryset. The hero_qs.values('name')[:1]
part picks up the first name from subquery.