CODEWITHSUNDEEP
sqldjangodjango-orm
josh
josh

Reputation: 10348

Join with subquery in Django ORM

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.

Upvotes: 2

Views: 3989

Answers (2)

ruddra
ruddra

Reputation: 52018

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.

Update:

You can try:

Session.objects.values('user') \
  .annotate(last_accessed=Max('last_accessed')) \
  .orde‌​r_by('last_accessed').distinct()

Upvotes: 1

akaariai
akaariai

Reputation: 734

Calling distinct('username') shouldn't return duplicate usernames ever. Are you sure you are using Django version that supports .dictinct(fields), that is Django version later than 1.4? Prior to Django 1.4 .distinct(fields) was accepted by the oRM, but it didn't actually do the correct DISTINCT ON query.

Another hint that things aren't working as expected is that .distinct(username).order_by(session__last_accessed) isn't a valid query - the order_by should have username as first argument because order_by must be prefixed with the field names in .distinct() call. See https://docs.djangoproject.com/en/1.4/ref/models/querysets/#django.db.models.query.QuerySet.distinct for details.

Upvotes: 0

Related Questions