Django variable number of filters on ManyToManyField objects?

Question

I'd like to filter objects on a ManyToManyField using two attributes of the objects that is pointed to by ManyToManyFeild. See the model below, this is a classical user-items relationship

from django.db import models

class Person(models.Model):
#    user = models.OneToOneField(User)
    name = models.CharField(max_length=50, blank=False, null=False, default=None, unique=True)
    ratings = models.ManyToManyField('Rating',
        related_name="persons"
    )


class Movie(models.Model):
    title = models.CharField(max_length=50, blank=False, null=False, default=None, unique=True)

    def __str__(self):
        return f"{self.title}"

class Rating(models.Model):
    movie = models.ForeignKey(Movie, models.CASCADE)
    value = models.SmallIntegerField(blank=False, null=False, default=None)

    def __str__(self):
        return f"{self.movie}:{self.value}"

So, I'd like to find all users who gave above certain rating to Movie1 AND above certain rating to Movie2.

I can do this with cascading filters:

>>> Person.objects\
 .filter(ratings__movie__title='Movie1', ratings__value__gte=5)\
 .filter(ratings__movie__title='Movie2', ratings__value__gte=1)\
 .all()
[[A<QuerySet [<Person: Person object (1)>]>

But I am looking for a way to filter on any arbitrary number of ratings, not just 2.

I have tried with Q (in the case single filter() for all cases), but the following doesn't work:

>>> Person.objects.filter(
  Q(ratings__movie__title='Movie1') & Q(ratings__value__gte=5),\
  Q(ratings__movie__title='Movie2') & Q(ratings__value__gte=1)
).all()
<QuerySet []>

Update:

I figured out, that since filters are lazy, I could cascade a required number of them them in a loop and then call .all() in the end, like this:

my_filters = [("Movie1", 2), ("Movie2", 3)]
f = Person.objects
for m,r in my_filters:
  f = f.filter(ratings__movie__title=m, ratings__value__gte=r)

selected_persons = f.all()

The question of course remains, how efficient is that query... So the question remains on how to do this with the best efficiency.

Thanks for help!

You can dynamically generate some Q objects. Check this answer: https://stackoverflow.com/a/13076894/4151233 — Marco, Jan 14 '21 at 15:47
yes, but I struggle to make a correct Q expression for my case. The one I gave at the end render empty set, while the one with cascading filters works ok. — Artem Trunov, Jan 14 '21 at 15:59

score 0 · Answer 1 · answered Jan 14 '21 at 15:08

0

You want to or the Q objects your current attempt and's them, which basically means the movie title must be Movie1 AND Movie2 and the rating must be 5 AND 1.
This might work as you want:-

Person.objects.filter(
  (Q(ratings__movie__title='Movie1') & Q(ratings__value__gte=5)) | (Q(ratings__movie__title='Movie2') & Q(ratings__value__gte=1))
).all()

answered Jan 14 '21 at 15:08

Abdul Aziz Barkat

19,475
3
20
33

But I think this means I search for users who either give >=rating1 to movie1 OR >=rating2 to movie2, but I want to search for users who reviewed both Movie1 and Movie2 and gave them respectively above rating1 and rating2. – Artem Trunov Jan 14 '21 at 15:38
In that case your approach with the cascading filters is the only one I can think of. As even if we think just about the SQL the only way I can think is multiple joins on the ratings table. Also why have you setup a many to many field in Person a better approach would be to put a foreign key to Person in Rating. – Abdul Aziz Barkat Jan 14 '21 at 16:00
Yes, you are right about the fereign key. I guess I need to rethink the model, since the search case I outlined is the my main goal... – Artem Trunov Jan 14 '21 at 18:34

Django variable number of filters on ManyToManyField objects?

1 Answers1