CODEWITHSUNDEEP
pythonpython-3.xdjangodjango-viewsdjango-orm
Underoos
Underoos

Reputation: 5190

How to get latest db record based on 2 columns in django

I have a table which have combination of foreign keys F1, F2 and a column for storing the created datetime.

How can I query the latest records for the combinations of the foreign keys.

These are the columns in table.

ChallengeID, UserID, createdDatetime, points

If a challengeID is CH1 and we have 2 users U1, U2, and there could be multiple records for that combination. for ex. CH1 and U1, there are 5 records. and out of those one is latest record based on createdDatetime.

How do I get the points value from the latest records for all the users for a particular challenge using Django ORM?

something like:

CH1, U1 - PV1

CH1, U2 - PV2

CH1, U3 - PV3

Here's the code that I tried.

def challenge_leaderboard(request):
    challenge_id = request.GET.get('challenge_id')
    users = RewardPointLog.objects.filter(challenge_user__challenge_id=challenge_id).values_list('user', flat=True)
    users_points = {}
    for user in users:
        points = RewardPointLog.objects.filter(challenge_user__challenge_id=challenge_id, challenge_user__user=user).latest('created_datetime').points
        users_points[user.id] = points
    return Response(users_points, status=200)

Upvotes: 0

Views: 227

Answers (1)

lucutzu33
lucutzu33

Reputation: 3700

This works if you're using PostgreSQL

RewardPointLog.objects.filter(challenge_user__challenge_id=challenge_id).order_by('challenge_user__user', '-created_datetime').distinct('challenge_user__user')

https://docs.djangoproject.com/en/3.2/ref/models/querysets/#distinct

Upvotes: 1

Related Questions