[Django]-Django queryset annotate field to be a list/queryset

by

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.

👤AKS

9👍

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.

👤zyks

Leave a comment