Opened 9 years ago

Closed 9 years ago

#26115 closed Bug (duplicate)

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

Reported by: Kevin Mann Owned by: nobody
Component: contrib.admin Version: 1.9
Severity: Normal Keywords: admin, sort, admin_order_field, list_display callable
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

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.

Attachments (2)

models.py (747 bytes ) - added by Kevin Mann 9 years ago.
Three models as described in bug report.
admin.py (442 bytes ) - added by Kevin Mann 9 years ago.
Admin site configuration that yields bug.

Download all attachments as: .zip

Change History (5)

by Kevin Mann, 9 years ago

Attachment: models.py added

Three models as described in bug report.

by Kevin Mann, 9 years ago

Attachment: admin.py added

Admin site configuration that yields bug.

comment:1 by Kevin Mann, 9 years ago

Description: modified (diff)

comment:2 by Tim Graham, 9 years ago

Description: modified (diff)

comment:3 by Simon Charette, 9 years ago

Resolution: duplicate
Status: newclosed

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')
        ).
Note: See TracTickets for help on using tickets.
Back to Top