CODEWITHSUNDEEP
pythondjangodjango-modelsjoindjango-orm
Ron
Ron

Reputation: 23486

django ORM turns two conditions on related table into two separate JOINs

I have the case that I need to filter on two attributes from a related table.

class Item(models.Model):
     vouchers = models.ManyToManyField()


class Voucher(models.Model):
     is_active = models.BooleanField()
     status = models.PositiveIntegerField()

When I query the ORM like this:

Item.objects.exclude(
    vouchers__is_active=False, 
    vouchers__status__in=[1, 2])

The created query looks like this:

SELECT *
FROM `item`
WHERE NOT (`item`.`id` IN (
   SELECT U1.`item_id`
   FROM `itemvouchers` U1
   INNER JOIN `voucher` U2 ON (U1.`voucher_id` = U2.`id`)
   WHERE U2.`is_active` = FALSE) 
 AND 
`item`.`id` IN (
   SELECT U1.`item_id`
   FROM `itemvouchers` U1
   INNER JOIN `voucher` U2 ON (U1.`voucher_id` = U2.`id`)
   WHERE U2.`status` IN (1, 2))
)

I want to exclude vouchers which are both inactive AND have status 1 or 2.

What the query does is creating two separate joins. This is at first unnecessary and bad for performance. Second it's just wrong.

Case:

voucher_a = Voucher.objects.create(status=3, is_active=True)
voucher_b = Voucher.objects.create(status=1, is_active=False)

If I have an item in related with voucher_a and voucher_b it does not get found because it is in JOIN 1 but not in JOIN 2.

It looks like a bug in django but I wasn't able to find anything useful on the web to this topic.

We are on django==2.1.1 and tried out switching exclude with filter or using Q-expressions. Nothing worked so far.

Upvotes: 1

Views: 65

Answers (1)

Risadinha
Risadinha

Reputation: 16666

Your setup is an m2m relation, and you want to exclude any single object that has at least one m2m relation for which this AND combination of conditions is true.

M2M relationships are special when it comes to filter/exclude querysets, see https://docs.djangoproject.com/en/2.1/topics/db/queries/#spanning-multi-valued-relationships

Also note in that documentation:

The behavior of filter() for queries that span multi-value relationships, as described above, is not implemented equivalently for exclude(). Instead, the conditions in a single exclude() call will not necessarily refer to the same item.

The solution presented in the documentation is the following:

Blog.objects.exclude(
    entry__in=Entry.objects.filter(
        headline__contains='Lennon',
        pub_date__year=2008,
    ),
)

Upvotes: 3

Related Questions