order_by on Many-to-Many field results in duplicate entries in queryset

Question

I am attempting to perform an order_by based a m2m field, but it ends up creating duplicate entries in my queryset. I have been searching through the django documentation and related questions on stack exchange, but I haven't been able to come up with any solutions.

Models:

class WorkOrder(models.Model):
    ...
    appointment = models.ManyToManyField(Appointment, null=True, blank=True, related_name = 'appointment_from_schedule')
    ...

class Appointment(models.Model):

    title = models.CharField(max_length=1000, blank=True)
    allDay = models.BooleanField(default=False)
    start = models.DateTimeField()
    end = models.DateTimeField(null=True, blank=True)
    url = models.URLField(blank=True, null=True)

Query:

qs = WorkOrder.objects.filter(work_order_status="complete").order_by("-appointment__start")

Results:

[<WorkOrder: 45: Davis>, <WorkOrder: 45: Davis>]

In interactive mode:

>>>qs[0] == a[1]
True
>>>qs[0].pk
45
>>>qs[1].pk
45

If I remove the order_by then I get only a single result, but adding it later puts the duplicate entry back in.

>>>qs = WorkOrder.objects.filter(work_order_status="complete")
>>>qs
[<WorkOrder: 45: Davis>]
>>>qs.order_by('appointment__start')
[<WorkOrder: 45: Davis>, <WorkOrder: 45: Davis>]

I have tried adding .distinct() and .distinct('pk'), but the former has no effect and the latter results in an error:

ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Answer 1

I took suggestions provided by sfletche about using annotate and discussed the problem in freenode.net irc channel #django.

Users FunkyBob and jtiai were able to help me getting it working.

Since there can be many appointments for each work order, when we ask it to order by appointments, it will return a row for every instance of appointment since it doesn't know which appointment I was intending for it to order by.

from django.db.models import Max

WorkOrder.objects.annotate(max_date=Max('appointment__start')).filter(work_order_status="complete").order_by('max_date')

So, we were on the right path it was just about getting the syntax correct.

Thank you for the help sfletche, FunkyBob and jtiai.

Answer 2

You might try using annotate with values:

qs = WorkOrder.objects.filter(work_order_status="complete").values("appointment").annotate(status="work_order_status").order_by("-appointment__start")