CODEWITHSUNDEEP
djangodjango-models
Ross Rogers
Ross Rogers

Reputation: 24228

How can I filter on two different related fields and have them both be satisfied in one query?

I have some models with a relationship like the following:

class Container(models.Model):
    pass

class Child(models.Model):
    container = models.ForeignKey(Container, related_name='children')
    tag = models.CharField(max_length=40)
    val = models.IntegerField()

I would like to filter the container on whether I can find two separate children, one having a tag of 'foo' with a val in [1,2,3] and the other having a tag of bar with a val in [3,4,5].

When I filter like the following:

print list(Container.filter( 
  Q(children__tag='foo', val__in=[1,2,3]) & 
  Q(children__tag='bar', val__in=[5,6,7]))
).distinct()

Django is too smart. It filters every single Child to make sure it has both a tag of 'foo' and of 'bar' as well as values in both [1,2,3] and [5,6,7] with the following SQL:

SELECT 
    COUNT(DISTINCT `app_container`.`id`)
FROM
    `app_container`
        INNER JOIN
    `app_child` ON (`app_container`.`id` = `app_child`.`container_id`)
WHERE
  app_child.tag = 'foo' 
  AND app_child.val in (1,2,3)
  AND app_child.tag = 'bar' 
  AND app_child.val in (5,6,7)

I want django do do something like the following to get Containers with two different children:

SELECT 
    COUNT(DISTINCT `app_container`.`id`)
FROM
    `app_container`
        LEFT JOIN
    `app_child` c1 ON (`app_container`.`id` = `c1`.`container_id`)
        LEFT JOIN
    `app_child` c2 ON (`app_container`.`id` = `c1`.`container_id`)
WHERE
  c1.tag = 'foo' 
  AND c1.val in (1,2,3)
  AND c2.tag = 'bar' 
  AND c2.val in (5,6,7)

Upvotes: 0

Views: 33

Answers (2)

Peter DeGlopper
Peter DeGlopper

Reputation: 37319

If memory serves, one approach is to chain filter calls:

 Container.objects() \
    .filter(children__tag='foo', children__val__in=[1,2,3]) \
    .filter(children__tag='bar', children__val__in=[5,6,7]) \
    .distinct()

The filter on a one-to-many looks for a single related object that meets the criteria, but the two chained calls should be independent of each other.

Upvotes: 2

Ross Rogers
Ross Rogers

Reputation: 24228

I came up with a solution, but I'm not sure if others are more efficient:

print list(Container.filter(
  Q(pk__in=Container.objects.filter(children__tag='foo', val__in=[1,2,3])) &
  Q(pk__in=Container.objects.filter(children__tag='bar', val__in=[4,5,6]))
).distinct()

Upvotes: 0

Related Questions