CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-orm
Mark Mishyn
Mark Mishyn

Reputation: 4151

How to sort queryset by annotated attr from ManyToMany field

Simplest example:

class User(models.Model):
    name = ...

class Group(models.Model):
    members = models.ManyToManyField(User, through='GroupMembership')

class GroupMembership(models.Model):
    user = ...
    group = ...

I want to get list of Groups ordered by annotated field of members.

I'm using trigram search to filter and annotate User queryset. To get annotated users I have something like that:

User.objects.annotate(...).annotate(similarity=...)

And now I'm trying to sort Groups queryset by Users' "similarity":

ann_users = User.objects.annotate(...).annotate(similarity=...)
qs = Group.objects.prefetch_related(Prefetch('members', 
    queryset=ann_users))
qs.annotate(similarity=Max('members__similarity')).order_by('similarity')

But it doesn't work, because prefetch_related does the ‘joining’ in Python; so I have the error: 

"FieldError: Cannot resolve keyword 'members' into field."

Upvotes: 1

Views: 247

Answers (1)

hynekcer
hynekcer

Reputation: 15548

I expect that you have a database function for similarity of names by trigram search and its Django binding or you create any:

from django.db.models import Max, Func, Value, Prefetch

class Similarity(Func):
    function = 'SIMILARITY'
    arity = 2

SEARCHED_NAME = 'searched_name'
ann_users = User.objects.annotate(similarity=Similarity('name', Value(SEARCHED_NAME)))
qs = Group.objects.prefetch_related(Prefetch('members', queryset=ann_users))
qs = qs.annotate(
    similarity=Max(Similarity('members__name', Value(SEARCHED_NAME)))
).order_by('similarity')

The main query is compiled to

SELECT app_group.id, MAX(SIMILARITY(app_user.name, %s)) AS similarity
FROM app_group
LEFT OUTER JOIN app_groupmembership ON (app_group.id = app_groupmembership.group_id)
LEFT OUTER JOIN app_user ON (app_groupmembership.user_id = app_user.id)
GROUP BY app_group.id
ORDER BY similarity ASC;
-- params: ['searched_name']

It is not exactly what you want in the title, but the result is the same.

Notes: The efficiency how many times will be the SIMILARITY function evaluated depends on the database query optimizer. The query plan by EXPLAIN command will be an interesting your answer, if the original idea by raw query in some simplified case is better.

Upvotes: 1

Related Questions