Sorting by Value of Object in Many to Many Field Django

user_8055172 0
Viewed 55

I need some sorting help with django querysets that involve many to many relationships.

I'm making a language-based conversation app and I've just finished writing a query that returns a queryset named possibleMatches containing the users someone might want to chat with. However, now I want to sort those users by the level of a specific language they have in their user languages table. I believe the solution lies in using Django's annotate() function for sorting querysets, but I've been having trouble finding the exact syntax to do it. I've also tried using filtered relations, but when running it, I got an error saying that filtered relations don't support nested conditions.

Each user has an id and a list of user languages represented by a many to many field. Each user language corresponds to one user, one language, and one level. For clarity, here are my models:

class CustomUser(AbstractUser):

    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)

class Language(models.Model):

    id = models.IntegerField(primary_key=True, choices=SUPPORTED_LANGUAGES)

class UserLanguage(models.Model):

    language = models.ForeignKey(Language, on_delete=models.DO_NOTHING)
    user = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='languages', on_delete=models.DO_NOTHING)
    level = models.IntegerField(choices=LANGUAGE_PROFICIENCIES, null=False, blank=False)

Anyway, I want to sort the users in possibleMatches in ascending order based on their level of language 1. This is the code and query I have so far:

sortingLanguage= Language.objects.get(id=1)
possibleMatches.annotate(language_1="languages__language__id=sortingLanguage.id").order_by(language_1.level)

But I keep getting syntax errors, and I'm not sure what the right combination of filter(), annotate(), When(), and Case() is in order to make it work (I think I'm getting the syntax confused with that of filtering over many to many relationships). Does anyone know what the right syntax for this would be? Thank you very much in advance.

django-queryset django
1 Answers

You can first filter the many-to-many relation, such that only the UserLanguage that refers to the sortingLanguage is retained, then you can order on the level of that language:

CustomUser.objects.filter(
    languages__language=sortingLanguage
).order_by('languages__level')

If the id of the sorting language is known, one does not have to fetch that object into memory first, you can filter it directly by the id:

CustomUser.objects.filter(
    languages__language_id=1
).order_by('languages__level')

you can use an aggregate to order by the largest of the filtered levels:

If the id of the sorting language is known, one does not have to fetch that object into memory first, you can filter it directly by the id:

from django.db.models import Max

CustomUser.objects.filter(
    languages__language_id=1
).annotate(
    highest_level=Max('languages__level')
).order_by('highest_level')
user_675790
Related