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.

score 23 · Accepted Answer · answered Apr 04 '17 at 10:27

23

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.

answered Apr 04 '17 at 10:27

AKS

18,983
3
43
54

Were you able to work out the conditional expression inside the aggregation? I would like to know how. – AKS Apr 06 '17 at 05:31
See my comment below – zyks Apr 06 '17 at 05:56
I see what I was missing. I was using `then=F("related_model__pk")` and that's why it was not working. Thank you. – AKS Apr 06 '17 at 05:58
How can i make it a set of the values though? – ThunderHorn Oct 10 '20 at 09:48

score 8 · Answer 2 · answered Apr 06 '17 at 05:50

8

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.

answered Apr 06 '17 at 05:50

zyks

521
1
4
12

2

Is it possible to filter out the None items from the array field? – Joseph M Mar 16 '18 at 19:22
I've filtered them out in app using python `filter` function. I'm not sure if it is possible on the database level. – zyks May 12 '18 at 08:11
4

`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. – Dušan Maďar Oct 19 '20 at 09:38
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. – Milo Persic Mar 05 '22 at 15:08

Django queryset annotate field to be a list/queryset

2 Answers2

Linked