7. How to do a subquery expression in Django?¶
Django allows using SQL subqueries.
Let’s start with something simple, We have a UserParent
model which has OnetoOne
relation with auth user. We will find all the UserParent
which have a 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)>]>
Now for something more complex. 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.