Using extra instead annotate in ORM while using select_for_update

[Zerq]

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.

[Simon Charette]

Duplicate of #28296. You should be able to use a Subquery annotation to avoid the use of extra here.

[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.

[George Tantiras]

Replying to 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 ​using aggregates within a subquery expression and ​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))