CODEWITHSUNDEEP
pythondjango
Daniel Ben-Shabtay
Daniel Ben-Shabtay

Reputation: 350

django get queryset which is latest of other column value

I have a model with some columns, between them there are 2 columns: equipment_id (a CharField) and date_saved (a DateTimeField).
I have multiple rows with the same equipment_id and but different date_saved (each time the user saves the record I save the now date time).
I want to retrieve a list of records that have a specific equipment_id and is the latest saved, i.e.:
| Equipment_id | Date_saved |

| --- ----- | --------------------- -------- |

| 1061a | 26-DEC-2020 10:10:23|
| 1061a | 26-DEC-2020 10:11:52|
| 1061a | 26-DEC-2020 10:22:03|
| 1061a | 26-DEC-2020 10:31:15|
| 1062a | 21-DEC-2020 10:11:52|
| 1062a | 25-DEC-2020 10:22:03|
| 1073a | 20-DEC-2020 10:31:15|

Output:
| 1061a | 26-DEC-2020 10:31:15|
| 1062a | 25-DEC-2020 10:22:03|
| 1073a | 20-DEC-2020 10:31:15|

I have tried various approach without success:

try:  
    progall=Program.objects.filter(equipment_id__startswith=str(b_id_to_search)).latest('saved_date')

The command doesn't work, the program goes to the except line

Upvotes: 1

Views: 609

Answers (2)

Chymdy
Chymdy

Reputation: 660

Program.objects.filter(equipment_id__startswith=b_id_to_search).latest("saved_date")

This should do.

Upvotes: 0

Iain Shelvington
Iain Shelvington

Reputation: 32244

If you are using PostgreSQL you can pass arguments to queryset.distinct() that specify the field(s) to return distinct values for in your query. You need to use order_by correctly here, you should order by the field your are passing to distinct and then order by the second field so that the records that you want are first ('-saved_date' here so you get the latest for each equipment_id)

progall = Program.objects.filter(
    equipment_id__startswith=str(b_id_to_search)
).order_by=(
    'equipment_id',
    '-saved_date'
).distinct(
    'equipment_id'
)

Upvotes: 1

Related Questions