Django queryset annotate field to be a list/queryset

python django django-queryset django-annotate
zyks picture zyks · Apr 4, 2017 · Viewed 14.7k times · Source

I'm trying to use django annotation to create queryset field which is a list of values of some related model attribute.

queryset = ...
qs = queryset.annotate(
    list_field=SomeAggregateFunction(
        Case(When(related_model__field="abc"), then="related_model__id")
    ),
    list_elements=Count(F('list_field'))
)

I was thinking about about concatenating all these id with some separator, but i don't know the appropriate functions. Another solution is to make list_field a queryset. I know this syntax is wrong. Thank you for any help.

Answer

AKS picture AKS · Apr 4, 2017

If you are using postgresql and django >= 1.9, you could use postgres specific aggregating functions e.g. ArrayAgg:

Returns a list of values, including nulls, concatenated into an array.

In case, you need to concatenate these values using a delimiter, you could also use StringAgg.

Related questions

Django custom for complex Func (sql function)

In the process of finding a solution for Django ORM order by exact, I created a custom django Func: from django.db.models import Func class Position(Func): function = 'POSITION' template = "%(function)s(LOWER('%(substring)s') in LOWER(%(expressions)…

Read More
How do I do a not equal in Django queryset filtering?

In Django model QuerySets, I see that there is a __gt and __lt for comparative values, but is there a __ne or != (not equals)? I want to filter out using a not equals. For example, for Model: bool a; int …

Read More
How do I filter query objects by date range in Django?

I've got a field in one model like: class Sample(models.Model): date = fields.DateField(auto_now=False) Now, I need to filter the objects by a date range. How do I filter all the objects that have a date …

Read More