Admin list sort using callable, latest, and admin_order_field causes duplicates.

[Kevin Mann]

In a data model that records changes over time to the way two entities relate, I am able to correctly display the latest record in an admin list by creating a callable using latest(). However, when sorting the column, if there is more than one record related to the admin list parent record, the number of rows will increase to that number, showing duplicates.

Example:

Given: A class Employee and a class Title, with a class EmployeeTitle that joins the two with start_date and end_date fields.

I'd like to show the employee's current title on the admin page, and be able to sort by that column. I create a callable current_title() in the Employee class which returns the latest of the employeetitle_set based on start_date.

I set the admin order field to be 'employeetitle__title__name'.

If the employee has been more than one title the unsorted list displays correctly, but the list sorted by current_title shows duplicates where all the titles show the same current title.

The expected behavior is that there is a single list entry for each employee and the list is sorted by the current_title.

I'm attaching a simple models.py and admin.py for reference.

[Kevin Mann]

Three models as described in bug report.

[Kevin Mann]

Admin site configuration that yields bug.

[Simon Charette]

Hi kevincmann, thanks for your report.

I fear this is not an issue with the admin but a limitation of the ORM tracked by #23854 and also know as the greatest-n-per-group problem, let me explain.

What you want to do is perform the following query:

SELECT
    employee.name,
    current_title.name
FROM employee
LEFT JOIN (
    SELECT *
    FROM employee_title
    WHERE start_date = (
        SELECT MAX(start_date)
        FROM employee_title AS inner_employee_title
        WHERE inner_employee_title.employee_id = employee_title.employee_id
    )
) current_employee_title ON
    current_employee_title.employee_id = employee.id
LEFT JOIN
    title AS current_title ON current_title.id = current_employee_title.title_id;

And make sure to ORDER BY current_title.name when the Current Title column is selected for sorting the results in the admin.

Unfortunately there's no API to instruct the ORM about the required LEFT JOIN on the current_employee_title subquery yet.

If the API proposed in #23854 lands you would simply have to override your ModelAdmin.get_queryset() method with the following to get it done:

class EmployeeAdmin(ModelAdmin):
    list_display = ['name', 'current_title__name']

    def get_queryset(self, request):
        queryset = super().get_queryset(request)
        return queryset.select_related(
            current_title=Latest('titles', by='start_date')
        ).