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

Injector

I have same problem with order_by , I want to sorting book base on author name like this book__author__name, but this appear the duplicate record. How to I do with annotate, thank you ! – Sabotage

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.

Injector

I have same problem with order_by, I want to sort the book base on the author name like this book__author__name, but this appears a duplicate record. How to I do annotate, thank you ! – Sabotage

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")

Bordereau

I haven't used annotate before, but using it as per your example results in the error: ValueError: The annotation 'work_order_status' conflicts with a field on the model. I'll check into the documentation on annotate to see if there is a way I can use it instead of filter though. Thanks for the idea – Injector

you might also need to use values in conjunction with annotate...updated my answer to reflect this – Bordereau

Unfortunately still no luck. This gives me AttributeError: 'str' object has no attribute 'default_alias' From reading the docs it looks like I might want to annotate a column that consists of the appointment.start that I can then filter and order by, but I haven't been able to get the syntax working for that yet either. – Injector

sorry I'm not much help. you might check out the following links distinct and order_by and the docs Good Luck :) – Bordereau

I definitely appreciate the effort and even if this doesn't solve the problem, it gave me some new stuff to learn and try out. – Injector