Is it possible to value based on another aggregation?

[Kenny Loveall]

Hi everyone,

I'm trying to figure out if it's possible to get a value for one field based on the aggregation of another? Specifically, I've got a model with a created_at timestamp, a foreign key, and a created_by user foreign key. I want to group the objects based on the foreign key and get the time and user of the most recently created object.

I know how to do this with a subquery and how to get the most recently created timestamp for each group of objects without a subquery, but I'm not sure how to get the user without using a subquery. Is this possible?

Thanks,

Kenny

[Matthew Pava]

Use annotate.

https://docs.djangoproject.com/en/2.2/topics/db/aggregation/#generating-aggregates-for-each-item-in-a-queryset

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAGABHYC3S-9o_ay1%2BPiANm6vv5py7tFBKyuUCzT1%2Bz49Z%2BpAnA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Kenny Loveall]

Right, so that works for getting the most recently updated at time (I can just do `.values('foreign_key_id').annotate(most_recently_updated_at=Max('created_at')`). But how can I get the user that created that record? Max('user_id') gives me just the highest ID.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/f1b62c769d1248a283e4bae3c904a5db%40iss2.ISS.LOCAL.

[Jim Illback]

Sorry this is late.

I’d use the annotate to get the Max of one FK. Then, do a query on the other FK table with inverted order_by and use the [0] notation to just retrieve the latest row. It is 2 queries, but FK processes are indexed and should be fast.

Hope this helps.

Jim Illback

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAGABHYArEeQLg%3DQoKOG5bnm76gbbPB6ASVsf%3DhBWR%2BsfXzgk%2BA%40mail.gmail.com.