Order_by in the Many-to-many field leads to duplicate records in the query set

Question

I am trying to execute order_by based on the m2m field, but it creates the creation of duplicate records in my query set. I am looking at django documentation and related stack exchange issues, but I could not find a solution.

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)

Inquiry:

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 delete order_by, I get only one result, but adding it later puts a duplicate entry.

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

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

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

Answer 1

, sfletche freenode.net ir#django.

FunkyBob jtiai .

, , , , .

from django.db.models import Max

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

, , .

sfletche, FunkyBob jtiai.

Answer 2

annotate values:

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