Query with annotated aggregation over the same model

Question

I have the following models:

class P(models.Model):
    name = models.CharField(max_length=30,  blank=False)


class pr(models.Model):
    p = models.ForeignKey(P, on_delete=models.CASCADE, related_name='cs')
    r = models.CharField(max_length=1)
    c = models.ForeignKey(P, on_delete=models.CASCADE, related_name='ps')
    rc = models.PositiveSmallIntegerField()

    class Meta:
        unique_together = (('p', 'c'),)

and the data:

"id","name"
69,"Hunter"
104,"Savannah"
198,"Adrian"
205,"Andrew"
213,"Matthew"
214,"Aiden"
218,"Madison"
219,"Harper"
---
"id","r","rc","c_id","p_id"
7556,"F",1,219,213
7557,"M",1,219,218
7559,"H",3,218,213
7572,"F",1,214,213
7573,"M",1,214,218
7604,"F",1,198,213
7605,"M",1,198,218
7788,"H",3,104,205
7789,"F",1,104,213
7790,"M",1,104,218
7866,"M",1,69,104
7867,"F",1,69,205

For a pr model instance I need to find in how many rows the associated p_id exists. These queries generate the intended result:

ro = pr.objects.get(pk=7604)
cntp = pr.objects.filter(Q(p = ro.p) | Q(c = ro.p)).count()

The above queries will hit the database 2 times, I want to hit the database one time so I wrote this query:

ro = pr.objects.filter(pk=7604).annotate(cntp=Subquery(pr.objects.filter(Q(p = OuterRef('p')) | Q(c = OuterRef('parent'))).count()))

That query generates error "This queryset contains a reference to an outer query and may only be used in a subquery." so used the method mentioned in Simple Subquery with OuterRef:

ro = pr.objects.filter(pk=7604).annotate(cntp=Subquery(pr.objects.filter(Q(p = OuterRef('p')) | Q(c = OuterRef('c'))).annotate(count=Count('pk'))))

Again this query generates another error "subquery must return only one column"! Can Django ORM be used to generate the intended results?

Environment:Django Version: 3.0.4 Python version: 3.7.3 Database:PostgreSQL 11.9 (Debian 11.9-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

score 0 · Answer 1 · answered Jan 03 '21 at 16:35

Fortunately; I found a solution to the mentioned problem, the solution generates the exact desired SQL query:

t=pr.objects.filter(
# Restrict the count query to records in outer query    
Q(p =OuterRef('p')) | Q(c = OuterRef('p'))).
#Anottate a static on which Count group by
annotate(v=Value(1, IntegerField())).
#Count can work only on a single field so restrict the output to v field only
values('v').
#Perform Count on v field with a wildcard so we get no unnecessary 'Group By' clause
annotate(cntr=Count('*')).
#Restrict output to one field because a sub query should return one field
values('cntr')
ro = pr.objects.filter(pk=7604).annotate(cntp=Subquery(t))

The resulting SQL query is:

    SELECT "eftapp_pr"."id", "eftapp_pr"."p_id", "eftapp_pr"."r", "eftapp_pr"."c_id",
 "eftapp_pr"."rc", (SELECT COUNT(*) AS "cntr" FROM "eftapp_pr" U0 WHERE (U0."p_id" = 
"eftapp_pr"."p_id" OR U0."c_id" = "eftapp_pr"."p_id")) AS "cntp" FROM "eftapp_pr" WHERE
 "eftapp_pr"."id" = 7604

Query with annotated aggregation over the same model

1 Answers1