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.

Which database are you using?

AKS
– AKS

2017-04-04 10:12:07 +00:00
Commented Apr 4, 2017 at 10:12 — AKS
– AKS, Commented Apr 4, 2017 at 10:12
I am using psql

zyks
– zyks

2017-04-04 10:17:50 +00:00
Commented Apr 4, 2017 at 10:17 — zyks
– zyks, Commented Apr 4, 2017 at 10:17

AKS · Accepted Answer · 2017-04-04 10:27:21Z

26

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 4, 2017 at 10:27

AKS

20k3 gold badges47 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AKS Over a year ago

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

zyks Over a year ago

See my comment below

AKS Over a year ago

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

ThunderHorn Over a year ago

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

zyks · Accepted Answer · 2017-04-06 05:50:45Z

14

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 6, 2017 at 5:50

zyks

6411 gold badge6 silver badges13 bronze badges

4 Comments

Joseph M Over a year ago

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

zyks Over a year ago

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

Dušan Maďar Over a year ago

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.

Milo Persic Over a year ago

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.

Collectives™ on Stack Overflow

Django queryset annotate field to be a list/queryset

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related