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

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 ! — Kev, Nov 16 '22 at 07:05

Del · Accepted Answer · 2014-05-12T04:37:22.277

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.

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 ! — Kev, Nov 16 '22 at 07:06

sfletche · Answer 2 · 2014-05-12T03:20:03.987

1

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

edited May 12 '14 at 03:20

answered May 12 '14 at 02:49

sfletche

47,248
30
103
119

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 – Del May 12 '14 at 02:53
you might also need to use `values` in conjunction with `annotate`...updated my answer to reflect this – sfletche May 12 '14 at 02:58
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. – Del May 12 '14 at 03:05
1

sorry I'm not much help. you might check out the following links [distinct and order_by](http://archlinux.me/dusty/2010/12/07/django-dont-use-distinct-and-order_by-across-relations/) and [the docs](https://docs.djangoproject.com/en/dev/topics/db/aggregation/) Good Luck :) – sfletche May 12 '14 at 03:07
1

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. – Del May 12 '14 at 03:14

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

2 Answers2

Linked