django object.filters.order_by() returns the same instance multiple times when the field i am ordering by is a "many to many"

user_9958179 0
Viewed 151

Here is the problem: I have two models Contact and Title.

  • Contact represent a person
  • Title represent a job title

A person can have more than one job title and the two models are related through a many to many relation (see models below).

The issue is that for some reason when I make this query contacts = Contact.objects.filter("some filter").order_by('title') I get contacts with more than one title returned multiple times...because I am ordering by title and I guess django is confused on which title to use? I am losing my mind on how to fix it while keeping ordering by title.

class Title(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    title_name = models.CharField(max_length=40)

class Contact(models.Model):
    name_surname = models.CharField(max_length=60)
    title = models.ManyToManyField(Title)
    ...
django-models django
1 Answers

because I am ordering by title and I guess django is confused on which title to use?

In essence you make a LEFT OUTER JOIN and thus for each item in the second table, it will retrieve a record.

If you want to order by the minimum title, you can work with:

from django.db.models import Min

Contact.objects.filter(…).alias(
    min_title=Min('title')
).order_by('min_title')

or prior to django-3.2, we can annotate the items:

from django.db.models import Min

Contact.objects.filter(…).annotate(
    min_title=Min('title')
).order_by('min_title')

You can use min_title=Min('title__title_name') to order lexicographically by the name of the title.

If you want to enumerate over the related titles of all Contacts in an efficient manner, you can use .prefetch_related(…) [Django-doc] to load related items in bulk instead of one extra query per Contact:

from django.db.models import Min

Contact.objects.filter().alias(
    min_title=Min('title')
).order_by('min_title').prefetch_related('title')
user_675790
Related