CODEWITHSUNDEEP
djangomany-to-manydjango-queryset
BleuBizarre
BleuBizarre

Reputation: 378

Queryset for ManyToMany relation ordered by number of match from a list

I have two models, Entity and Tag with a ManyToMany relation :

class Tag(models.Model):
    name = models.CharField(max_length=128)

class Entity(models.Model):
    name              = models.CharField(max_length=128)
    tags              = models.ManyToManyField(Tag)

In a view I have a list of Tag id and would like to get a top 10 of Entity with at least one Tag ordered by the number of Tags they have in common with my list.

I am currently doing the "at least one" part through 'tags__in' and adding a '.distinct()' at the end of my queryset. Is it the proper way ?

I can see that there is a 'Count' in annotate that can have some arguments, is there a way to specify only if id in my list ? If not do I need to go through an SQL query like this (from the documentation) ? I fear I am going to lose quite some perf.

Blog.objects.extra(
    select={
        'entry_count': 'SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id'
    },
)

Upvotes: 0

Views: 31

Answers (1)

Lotram
Lotram

Reputation: 769

If I understood your problem correctly, I think this query should do what you want (I'm using django 3.0):

from django.db.models import Count, Q

Entity.objects.annotate(tag_count=Count("tags", filter=Q(tags__id__in=tag_ids))).filter(
    tag_count__gt=0
).order_by("-tag_count")[:10]

generated SQL (I'm using postgres):

SELECT "people_entity"."id",
       "people_entity"."name",
       COUNT("people_entity_tags"."tag_id") FILTER (WHERE "people_entity_tags"."tag_id" IN (1, 4, 5, 8)) AS "tag_count"
  FROM "people_entity"
  LEFT OUTER JOIN "people_entity_tags"
    ON ("people_entity"."id" = "people_entity_tags"."entity_id")
 GROUP BY "people_entity"."id"
HAVING COUNT("people_entity_tags"."tag_id") FILTER (WHERE ("people_entity_tags"."tag_id" IN (1, 4, 5, 8))) > 0
 ORDER BY "tag_count" DESC
 LIMIT 10

small edit: added Q in imports

Upvotes: 1

Related Questions