Get latest item date where item field_1 & item field_2 have duplicates

I’m using Django 4.0, and Python 3.8.

I have a product that has a modification date, department name and designation.

I want to retrieve each product but, for products that have the same designation and the same department, I only want to retrieve the last entry.

Here is what i have now:

products = Product.objects.all().order_by('designation')

I tried sorting on the result and removing duplicate rows it worked for 10 rows but for 100 its dead

For the following data:

item: designation, date, department

1. item_1, 01/01/1970, department_1
2. item_2, 01/01/1970, department_2
3. item_3, 01/01/1970, department_3
4. item_1, 01/02/1970, department_1

I want:

1. item_1, 01/02/1970, department_1
2. item_2, 01/01/1970, department_2
3. item_3, 01/01/1970, department_3

Do you have any advice please ?

edit: department is another model, and the relationship between product and department is Many to Many.

Answer

If you’re using a PostgreSQL database, use the distinct() method on your QuerySet (link to Django documentation) to “eliminate duplicate rows from the query results”.

In your case:

products = Product.objects.all().order_by('designation', 'department').distinct('designation', 'department')

If you’re using another database, you can use distinct() as well but without passing positional arguments (*fields).