[Django] #32879: Using annotate and extra in ORM
Django
-------------------------------------+-------------------------------------
Reporter: Zerq | Owner: nobody
Type: | Status: new
Uncategorized |
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
In simple example:
{{{
class Foo(models.Model):
pass
class Bar(models.Model):
baz = models.ForeignKey('Foo', on_delete=models.PROTECT)
}}}
I want to count reverse relation count and then select_for_update:
{{{
Foo.objects.annotate(Count('bar')).select_for_update().get(...)
}}}
but this does not work: django.db.utils.NotSupportedError: FOR UPDATE is
not allowed with GROUP BY clause
To circumvent this problem I used:
{{{
Foo.extra(select={'bar_count': 'SELECT COUNT(*) FROM "app_bar" U0 WHERE
U0."baz_id" = "app_foo"."id"'}).select_for_update().get(...)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32879>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Zerq | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> In simple example:
> {{{
> class Foo(models.Model):
> pass
> class Bar(models.Model):
> baz = models.ForeignKey('Foo', on_delete=models.PROTECT)
> }}}
> I want to count reverse relation count and then select_for_update:
> {{{
> Foo.objects.annotate(Count('bar')).select_for_update().get(...)
> }}}
> but this does not work: django.db.utils.NotSupportedError: FOR UPDATE is
> not allowed with GROUP BY clause
>
> To circumvent this problem I used:
> {{{
> Foo.extra(select={'bar_count': 'SELECT COUNT(*) FROM "app_bar" U0 WHERE
> U0."baz_id" = "app_foo"."id"'}).select_for_update().get(...)
> }}}
New description:
In simple example:
{{{
class Foo(models.Model):
pass
class Bar(models.Model):
baz = models.ForeignKey('Foo', on_delete=models.PROTECT)
}}}
I want to count reverse relation count and then select_for_update:
{{{
Foo.objects.annotate(Count('bar')).select_for_update().get(...)
}}}
but this does not work: django.db.utils.NotSupportedError: FOR UPDATE is
not allowed with GROUP BY clause
To circumvent this problem I used:
{{{
Foo.extra(select={'bar_count': 'SELECT COUNT(*) FROM "app_bar" U0 WHERE
U0."baz_id" = "app_foo"."id"'}).select_for_update().get(...)
}}}
I file this issue to show usage of extra, as asked in documentation.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32879#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Zerq | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
--
Ticket URL: <https://code.djangoproject.com/ticket/32879#comment:2>
Django
Reporter: Zerq | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
Duplicate of #28296. You should be able to use a `Subquery` annotation to
avoid the use of `extra` here.
--
Ticket URL: <https://code.djangoproject.com/ticket/32879#comment:3>
Django
Reporter: Zerq | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Zerq):
For anyone who have the same problem:
{{{
sub =
Subquery(Bar.objects.filter(pk=OuterRef('pk')).annotate(count=Count('*')).values('count'))
sub = Coalesce(sub, 0)
query = Foo.objects.annotate(times_used=sub)
}}}
Small warning: If subquery has no values, it will return None, this is the
reason for Coalesce.
--
Ticket URL: <https://code.djangoproject.com/ticket/32879#comment:4>
Django
Reporter: Zerq | Owner: nobody
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by George Tantiras):
Replying to [comment:4 Zerq]:
> For anyone who have the same problem:
>
> {{{
> sub =
Subquery(Bar.objects.filter(pk=OuterRef('pk')).annotate(count=Count('*')).values('count'))
> sub = Coalesce(sub, 0)
> query = Foo.objects.annotate(times_used=sub)
> }}}
>
> Small warning: If subquery has no values, it will return None, this is
the reason for Coalesce.
The above code returns incorrect results compared to the results brought
by the following query:
{{{
query =
Foo.objects.annotate(times_used=Count(F("bar"))).values("times_used")
}}}
The query that managed to bring the same results, according to the docs
about [https://docs.djangoproject.com/en/dev/ref/models/expressions
/#using-aggregates-within-a-subquery-expression using aggregates within a
subquery expression] and
[https://docs.djangoproject.com/en/dev/ref/models/expressions/#func-
expressions-1 func expressions] is the following:
{{{
sub =
Subquery(Bar.objects.filter(baz=OuterRef('pk')).order_by().annotate(count=Func(F("id"),
function="Count")).values_count("count"))
query = Foo.objects.annotate(times_used=Coalesce(sub, 0))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32879#comment:5>