Getting actual model instance after aggregating by maximum value (Django)

Question

Given the following models:

Browsers

id
1
2
3

Sessions

id	last_activity	browser
1	2021-01-01	1
2	2021-01-02	1
3	2021-01-03	2
4	2021-01-04	2
5	2021-01-05	3

I want to select the Session object with the latest last_activity for each unique Browser, which in this example will be:

Sessions

id
2
4
5

I've tried Session.objects.values('browser').annotate(Max('last_activity')), but this does not give the actual Session instance. Is this possible in Django?

I would like to avoid raw SQL if possible, and avoid looping over a queryset.

Note: This is for an sqlite backend, so DISTINCT ON doesn't work.

Answer 1

I found the following solution, which unfortunately uses multiple SELECTs

query = Session.objects.filter(browser=OuterRef('pk')).order_by('-last_activity')
query = Browser.objects.annotate(session_id=Subquery(query.values('pk')[:1]))
query = Session.objects.filter(id__in=query.values('session_id'))
print(query)

Answer 2

I do not understand well your question

latest_browser = Session.objects.order_by("-last_activity").first().browser

to get each last session per browser:

latest_sessions = []
for browser in Browser.objects.all():
    latest_sessions.append(browser.session_set.order_by("-last_activity").first())

or if you want just a query:

id_browsers = Session.objects.values_list('browser', flat=True).annotate(Max('last_activity'))
sessions = Session.objects.filter(browser_id__in=id_browsers)