I want to generate the following query:
select id, (select count(*) from B where B.x = A.x) as c from A
Which should be simple enough with the Subquery expression. Except I get a group by statement added to my count query which I can't get rid of:
from django.contrib.contenttypes.models import ContentType
str(ContentType.objects.annotate(c=F('id')).values('c').query)
# completely fine query with annotated field
'SELECT "django_content_type"."id" AS "c" FROM "django_content_type"'
str(ContentType.objects.annotate(c=Count('*')).values('c').query)
# gets group by for every single field out of nowhere
'SELECT COUNT(*) AS "c" FROM "django_content_type" GROUP BY "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model"'
Which makes the result be [{'c': 1}, {'c': 1}, {'c': 1}, {'c': 1},...] instead of [{c:20}]. But subqueries have to have only one row of result to be usable.
Since the query is supposed to be used in a subquery I can't use .count() or .aggregate() either since those evaluate instantly and complain about the usage of OuterRef expression.
Example with subquery:
str(ContentType.objects.annotate(fields=Subquery(
Field.objects.filter(model_id=OuterRef('pk')).annotate(c=Count('*')).values('c')
)).query)
Generates
SELECT "django_content_type"."id",
"django_content_type"."app_label",
"django_content_type"."model",
(SELECT COUNT(*) AS "c"
FROM "meta_field" U0
WHERE U0."model_id" = ("django_content_type"."id")
GROUP BY U0."id", U0."model_id", U0."module", U0."name", U0."label", U0."widget", U0."visible", U0."readonly",
U0."desc", U0."type", U0."type_model_id", U0."type_meta_id", U0."is_type_meta", U0."multi",
U0."translatable", U0."conditions") AS "fields"
FROM "django_content_type"
Expected query:
SELECT "django_content_type"."id",
"django_content_type"."app_label",
"django_content_type"."model",
(SELECT COUNT(*) AS "c"
FROM "meta_field" U0
WHERE U0."model_id" = ("django_content_type"."id")) AS "fields"
FROM "django_content_type"
Update: (to add models from real app requested in comments):
class Translation(models.Model):
field = models.ForeignKey(MetaField, models.CASCADE)
ref_id = models.IntegerField()
# ... other fields
class Choice(models.Model):
meta = models.ForeignKey(MetaField, on_delete=models.PROTECT)
# ... other fields
I need a query to get number of Translations available for each choice where Translation.field_id refers to Choice.meta_id and Translation.ref_id refers to Choice.id.
The reason there are no foreign keys is that not all meta fields are choice fields (e.g. text fields may also have translations). I could make a separate table for each translatable entity, but this setup should be easy to use with a count subquery that doesn't have a group by statement in it.
UPDATE Here's a query using subquery that should come close to what you want:
str(ContentType.objects.annotate(fields=Subquery(
Field.objects.filter(model_id=OuterRef('pk')).values('model').annotate(c=Count('pk')).values('c')
)).query)
The only thing I did was adding the values('model') group_by clause which makes the Count('pk') actually work since it aggregates all rows into one.
It will return null instead of 0 when there are no related rows, which you can probably transform to 0 using a Coalesce function or a Case ... When ... then.
The exact query you want isn't possible with the Django ORM, although you can achieve the same result with
Choice.objects.annotate(c=Count(
'meta__translation',
distinct=True,
filter=Q(meta__translation__ref_id=F('id'))
))
Alternatively look at the django-sql-utils package, as also mentioned in this post.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments