Django: filter() on multiple instances of related models

Question

My model is as following: I have set of Dudes, and each Dude has a set of preferences. I can easily find Dudes based on their single preference, but I need to find those preferring two different things. I tried to pass two Q objects to filter() function, but in generated SQL those two Q object refer to the same related Preference. I want them to refer to two different Preferences. To make matters worse, I need to query several attributes of related model (=Preference), so I cannot use simple __in.

Model:

class Dude(models.Model):
    name = models.CharField(max_length=200)

class Preference(models.Model):
    name =     models.CharField(max_length=200)
    how_much = models.CharField(max_length=200)
    dude =     models.ForeignKey(Dude)

Test Case:

class DudesTestCase(TestCase):
    def setUp(self):
        dude = Dude.objects.create(name = 'Dude')
        cheese = Preference.objects.create(name = 'Cheese', how_much = "very", dude = dude)
        bacon = Preference.objects.create(name = 'Bacon', how_much = "absolutely_love", dude = dude)

    # does work
    def test_cheese_lovers(self):
        d = Dude.objects.filter(preference__name = 'Cheese', how_much = "very")
        self.assertEquals(d[0].name, 'Dude')

    # does not work - wants a single Preference to be both cheese and bacon
    def test_cheese_and_bacon_lovers(self):
        d = Dude.objects.filter(
                Q(preference__name = 'Cheese', how_much = "very"),
                Q(preference__name = 'Bacon', how_much = "absolutely_love"),
        )
        self.assertEquals(d[0].name, 'Dude')

Clarification: I don't want to find dudes liking either cheese or bacon, I need folks satisfying both conditions at the same time.

Anentropic · Accepted Answer · 2022-05-28T19:15:27.647

I think this should work

def test_cheese_and_bacon_lovers(self):
    d = Dude.objects.filter(
        preference__name='Cheese',
        how_much__in=("very", "absolutely_love"),
    ).filter(
        preference__name='Bacon',
        how_much__in=("very", "absolutely_love"),
    )
    self.assertEquals(d[0].name, 'Dude')

This usage is described in the docs here:
https://docs.djangoproject.com/en/1.8/topics/db/queries/#spanning-multi-valued-relationships

score 1 · Answer 2 · answered Dec 03 '15 at 13:30

Add related name to model:

class Preference(models.Model):
    name = models.CharField(max_length=200)
    dude = models.ForeignKey(Dude, related_name='preferences')

And use IN and annotate:

Dude.objects.filter(preferences__name__in=['Cheese', 'Bacon']) \
  .annotate(cnt=Count('preferences__name') \
  .filter(cnt=2)

Django: filter() on multiple instances of related models

2 Answers2