I want to run a filter using Django's ORM such that I get a distinct set of users with each user's most recent session. I have the tables set up so that a user has many sessions; there is a User and Session model with the Session model having a user = models.ForeignKey(User).
What I've tried so far is Users.objects.distinct('username').order_by('session__last_accessed'), but I know that this won't work because Django puts the session.last_accessed column into the selection, and so it's returning me, for example, 5 duplicate usernames with 5 distinct sessions rather than the single recent session and user.
Is it possible to query this via Django's ORM?
Edit: Okay, after some testing with SQL I've found that the SQL I want to use is:
select user.username, sub_query.last_accessed from (
select user_id, max(last_accessed) as last_accessed
from session
group by user_id
) sub_query
join user on
user.id = sub_query.user_id
order by sub_query.last_accessed desc
limit 5
And I can do sub_query via Session.objects.values('user').annotate(last_accessed=Max('last_accessed')). How can I use this sub_query to get the data I want with the ORM?
Edit 2: Specifically, I want to do this by performing one query only, like the SQL above does. Of course, I can query twice and do some processing in Python, but I'd prefer to hit the database once while using the ORM.
If you are using mysql backend, the following solution can be useful:
users_in_session = Session.objects.values_list('user_id', flat=True)
sessions_by_the_user_list = Session.objects \
.filter(user__in=set(users_in_session)) \
.order_by('last_accessed').distinct()
If you use the sub_query, then order_by('last_accessed') function should be good enough to get data in ordered list. Although as far as I have tested these results seemed unstable.
You can try:
Session.objects.values('user') \
.annotate(last_accessed=Max('last_accessed')) \
.order_by('last_accessed').distinct()
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句