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.
CodePudding user response：
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.
CodePudding user response：
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