#26426 closed New feature (fixed)
Add a way to customize a QuerySet's joins
Reported by: | Charlie DeTar | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.9 |
Severity: | Normal | Keywords: | QuerySet.extra |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
This ticket is just to document a use case for QuerySet.extra as requested by the docs: https://docs.djangoproject.com/en/1.9/ref/models/querysets/#extra
I have a Category model like this:
class Category(models.Model): name = models.CharField(max_length=200) followers = models.ManyToManyField(User)
I want to get a list of all categories, but to annotate each category with whether the currently logged in user is a "follower" of the category. Neither prefetch_related
nor annotate
work here, because I don't want to fetch nor aggregate over all "followers" (potentially many), I just want the presence of the current user. The extra query looks like this:
Category.objects.filter(...).extra( select={'is_following': '''EXISTS( SELECT "id" FROM "projects_category_followers" WHERE "projects_category_followers"."category_id"="projects_category"."id" AND "projects_category_followers"."user_id"=%s )'''}, select_params=(request.user.id,) )
Change History (5)
comment:1 by , 9 years ago
comment:2 by , 9 years ago
I agree that these aren't very intuitive and are less efficient that the extra
or RawSQL
, but technically,
Prefetch:
curr_user_query = User.objects.filter(id=request.user.id) Category.objects.all().prefetch(Prefetch('followers', queryset=curr_user_query, to_attr=is_following))
I am also quite certain an annotate solution can be found involving a variation (which I don't particularly care to get completely right) on Sum(Case(When(followers__id=Value(request.user.id), 1), default=0))
.
But more generally -- this falls into a wide category of cases we should support, that of generalized joins (that is, joins whose conditions are specified by the user, and are not just along FK relationships).
(I'm not sure there's a ticket for generalized joins, although it has been mentioned before; if there is, this should be marked duplicate, and if there isn't, it should be accepted).
comment:3 by , 9 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Type: | Uncategorized → New feature |
Something like #25590?
comment:4 by , 9 years ago
Description: | modified (diff) |
---|---|
Summary: | Use case for QuerySet.extra: annotate query with the existence of a relation → Add a way to customize a QuerySet's joins |
Triage Stage: | Unreviewed → Accepted |
comment:5 by , 7 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
This specific issue has been fixed with the addition of Exists expressions: https://docs.djangoproject.com/en/stable/ref/models/expressions/#exists-subqueries
The more general problem of supporting custom joins is: #29262.
It's not a great deal better, it is possible using RawSQL in an annotation rather than extra: