[Fixed]-Create model for MySQL view and join on it

by

1👍

✅

I couldn’t find any ORM method to get what you want in one query but we could kind of do this with two queries:

First, we get max timestamp for all the users

latest_timestamps = SampleModel.objects.values('user_id')
                        .annotate(max_ts=Max('timestamp')).values('max_ts')

Here values(user_id) works as group by operation.

Now, we get all the instanecs of SampleModel with the exact timestamps

qs = SampleModel.objects.filter(timestamp__in=latest_timestamps)

PostgreSQL speficic answer:

You could mix order_by and distinct to achieve what you want:

SampleModel.objects.order_by('user_id', '-timestamp').distinct('user_id')

Breaking it down:

# order by user_id, and in decreasing order of timestamp
qs = SampleModel.objects.order_by('user_id', '-timestamp')

# get distinct rows using user_id, this will make sure that the first entry for 
# each user is retained and since we further ordered in decreasing order of
# timestamp for each user the first entry will have last row added 
# for the user in the database.
qs = qs.distinct('user_id')
👤AKS

Leave a comment