[Django] #26426: Use case for QuerySet.extra: annotate query with the existence of a relation
Django
relation
-------------------------------+----------------------------
Reporter: yourcelf | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.9
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+----------------------------
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,)
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26426>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
relation
Reporter: yourcelf | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.9
Keywords: QuerySet.extra | Triage Stage: Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by AlexHill):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
It's not a great deal better, it is possible using RawSQL in an annotation
rather than extra:
{{{
from django.db.models.expressions import RawSQL
exists_sql = """EXISTS (
SELECT "id" FROM "extra_category_followers"
WHERE "extra_category_followers"."category_id"="extra_category"."id"
AND "extra_category_followers"."user_id"=%s
)"""
Category.objects.all().annotate(is_following=RawSQL(exists_sql,
(request.user.id,)))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26426#comment:1>
Django
relation
Reporter: yourcelf | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.9
Keywords: QuerySet.extra | Triage Stage: Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by shaib):
I agree that these aren't very intuitive and are less efficient that the
`extra` or `RawSQL`, but technically,
Prefetch:
{{{#!python
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).
--
Ticket URL: <https://code.djangoproject.com/ticket/26426#comment:2>
Django
relation
Reporter: yourcelf | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* type: Uncategorized => New feature
* component: Uncategorized => Database layer (models, ORM)
Comment:
Something like #25590?
--
Ticket URL: <https://code.djangoproject.com/ticket/26426#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: yourcelf | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* stage: Unreviewed => Accepted
Old description:
> 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,)
> )
> }}}
New description:
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,)
)
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26426#comment:4>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* status: new => closed
* resolution: => duplicate
Comment:
I closed #27332 as a duplicate.
--
Ticket URL: <https://code.djangoproject.com/ticket/26426#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Charlie DeTar | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by MikiSoft):
[comment:5 Tim Graham], you have also closed this issue, is that by
itention or not?
--
Ticket URL: <https://code.djangoproject.com/ticket/26426#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Charlie DeTar | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Josh Smeaton):
* status: new => closed
* resolution: => fixed
Comment:
This specific issue has been fixed with the addition of Exists
expressions: https://docs.djangoproject.com/en/2.0/ref/models/expressions
/#exists-subqueries
The more general problem of supporting custom joins is:
https://code.djangoproject.com/ticket/29262
--
Ticket URL: <https://code.djangoproject.com/ticket/26426#comment:5>