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.
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
.
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With