2

So I have table the looks something like this

user_id value date_created
1 10 2022-8-14
2 11 2022-8-14
3 12 2022-8-14
1 13 2022-8-15
2 14 2022-8-15
3 15 2022-8-15
1 10 2022-8-16
2 11 2022-8-16
3 12 2022-8-16

I want to find latest record before a certain date for given users. Following query filters all records for given users before a certain date.

UserData.objects.filter(user_id__in=user_list, date_created__lte=start_date)

How do I modify this query to get only the latest rows before the start date for each user. For example if the start date is 15 August it should give rows 4 to 6 from the table.

PS: Date created is a simplification, it should be datetime and there can be multiple values on each day by same users.

Piyush Divyankar
  • 149
  • 10
  • It seems like you want to look it up by exact date? Not the less than, right? – vinkomlacic Aug 16 '22 at 07:17
  • @vinkomlacic I want latest records before a certain date – Piyush Divyankar Aug 16 '22 at 09:32
  • You can use slice operator to limit the query to a number of records. For example, limit to 10 latest records: `UserData.objects.filter(user_id__in=user_list, date_created__lte=start_date)[:10]`. Django querysets are lazy so this won't query all records and then slice it - it will only query 10. – vinkomlacic Aug 16 '22 at 09:39

2 Answers2

1

If your database backend is PostgreSQL, Django supports specifying fields in .distinct

UserData.objects.filter(user_id__in=user_list, date__created__lte=start_date)\
    .order_by('user_id', '-date').distinct('user_id')

If it is not, please refer to @Patrick H.

Ezon Zhao
  • 577
  • 2
  • 14
0

I guess you have a separate model user right? It sounds like a greatest-n-per-group question, check out this answer:

Django Query That Get Most Recent Objects From Different Categories

Patrick H.
  • 168
  • 7