Django queryset annotate field to be a list/queryset

Question

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.

Henceforth

Which database are you using? – Horseman

I am using psql – Henceforth

Answer 1

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.

Horseman

Were you able to work out the conditional expression inside the aggregation? I would like to know how. – Horseman

See my comment below – Henceforth

I see what I was missing. I was using then=F("related_model__pk") and that's why it was not working. Thank you. – Horseman

How can i make it a set of the values though? – Intergrade

Answer 2

I have done something like that:

qs = queryset \
    .annotate(
        field_a=ArrayAgg(Case(When(
            related_model__field="A",
            then="related_model__pk")
        )),
        field_b=ArrayAgg(Case(When(
            related_model__field="B",
            then="related_model__pk")
        )),
        field_c=ArrayAgg(Case(When(
            related_model__field="C",
            then="related_model__pk")
        ))
    )

Now there are lists of None or pk under each field_a, field_b and field_c for every object in queryset. You can also define other default value for Case instead of None.

Henceforth

Is it possible to filter out the None items from the array field? – Bushey

I've filtered them out in app using python filter function. I'm not sure if it is possible on the database level. – Henceforth

field_a=ArrayAgg("related_model__pk", filter=Q(related_model__field="A")) is a bit cleaner. However, I am not sure if filter was supported by ArrayAgg when this answer was posted. – Stateroom

Just discovered ArrayAgg thanks to this post and can't believe how awesome it is. I needed to annotate the counts of related objects, but also get a list of their ids. ArrayAgg to the rescue. – Ainslie