Why aren't my Django Postgres `ArrayAgg` members sorting?

Question

I'm exploring the use of ArrayAgg and I don't understand why 'histidine-[13C6,15N3]' doesn't occur before 'isoleucine-[13C6,15N1]' in this example:

In [25]: for i in Infusate.objects.annotate(tns=ArrayAgg("tracer_links__tracer__name", order_by="tracer_links__tracer__name")).order_by("tns").distinct():
    ...:     print(i.tns)
    ...: 
['inosine-[15N4]']
['isoleucine-[13C6,15N1]', 'leucine-[13C6,15N1]', 'valine-[13C5,15N1]']
['isoleucine-[13C6,15N1]', 'lysine-[13C6,15N2]', 'phenylalanine-[13C9,15N1]', 'threonine-[13C4,15N1]', 'tryptophan-[13C11]', 'histidine-[13C6,15N3]']

The records/rows are sorted based on the first value, which is great, but that first value isn't sorted WRT the other values in the list.

For that matter, why doesn't the order of the members of the list change at all when I negate the order_by in the ArrayAgg? According to what I read, this should sort the list items. Am I blind? What am I doing wrong?

In [25]: for i in Infusate.objects.annotate(tns=ArrayAgg("tracer_links__tracer__name", order_by="-tracer_links__tracer__name")).order_by("tns").distinct():
    ...:     print(i.tns)
    ...: 
['inosine-[15N4]']
['isoleucine-[13C6,15N1]', 'leucine-[13C6,15N1]', 'valine-[13C5,15N1]']
['isoleucine-[13C6,15N1]', 'lysine-[13C6,15N2]', 'phenylalanine-[13C9,15N1]', 'threonine-[13C4,15N1]', 'tryptophan-[13C11]', 'histidine-[13C6,15N3]']

Not in this code, no. I was just playing with the ArrayAgg capabilities to see what I could do with it. This will be just one component of a much larger effort. I've read your answers elsewhere, so I know the intention of your question. ;) — hepcat72
– hepcat72, Commented Jul 3 at 19:33

hepcat72 · Accepted Answer · 2025-07-02 21:42:11Z

1

D'oh! I'm on Django 4.2 and I was looking at the 5.2 documentation! It's ordering in 4.2. order_by is 5.2:

In [28]: for i in Infusate.objects.annotate(tns=ArrayAgg("tracer_links__tracer__name", ordering="tracer_links__tracer__name")).order_by("tns").distinct():
    ...:     print(i.tns)
    ...: 
['histidine-[13C6,15N3]', 'isoleucine-[13C6,15N1]', 'lysine-[13C6,15N2]', 'phenylalanine-[13C9,15N1]', 'threonine-[13C4,15N1]', 'tryptophan-[13C11]']
['inosine-[15N4]']
['isoleucine-[13C6,15N1]', 'leucine-[13C6,15N1]', 'valine-[13C5,15N1]']

answered Jul 2 at 21:42

hepcat72

1,2258 silver badges31 bronze badges

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

Comments

willeM_ Van Onsem · Accepted Answer · 2025-07-04 08:54:53Z

0

If this is only for the purpose of ordering, you can just determine the smallest item first:

from django.db.models import Min


Infusate.objects.alias(
    lowest_name=Min('tracer_links__tracer__name')
).order_by('lowest_name')

If you put a db_index=True on the name field, this could probably boost ordering.

answered Jul 4 at 8:54

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

1 Comment

hepcat72 Jul 5 at 11:57

The cool thing about ArrayAgg is that compared to a many-related model alternative, the number of results doesn't change when you want to order by this name field. In a many-related model situation, you have to use Min to not change the number of results, but this means that if 2 related model records have the same minimum value, the row sort between them is arbitrary. Not so with ArrayAgg. I have not yet gotten to performance considerations, so thanks for that. I'll keep this in mind. (I use this in an annotation for sort.)

Collectives™ on Stack Overflow

Why aren't my Django Postgres `ArrayAgg` members sorting?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related