Django Orm Not Generating Correct Sql For Many To Many Not In

I'm having a problem with Django's generated SQL from the ORM. Cartons have a many to many relationship with Shipments through cartons_shipments. I'm looking to exclude Shipments w

Solution 1:

To us mere mortals, the "M" in ORM can be a bit inscrutable at times. But you could try a different, simpler tack. It still uses a subquery and not a join, but this is not necessarily a performance drag.

Shipment.objects.exclude(
    id__in=Cartons.objects
        .filter(type='INBOUND',
                status__in=['TRANSIT', 'DELIVERED', 'FAILURE'])
        .values('shipments__id')
        .distinct()
)

The exact name of the reference back to the Shipment primary key from the Carton model depends on the exact definition of the models. I've used shipments__id, but it could be shipment_set__id or something else.

---

New idea: You need to base the subselect on the intermediate model rather than Cartons. If you have an explicit intermediate model, it's easy, if you don't, you first need a Shipment or Cartons object, because as far as I know you cannot get a reference to the intermediate model from the class itself, only from an instance.

IModel = Shipment.objects.first().cartons.through
Shipment.objects.exclude(
    id__in=IModel.objects
        .filter(cartons__type='INBOUND',
                cartons__status__in=['TRANSIT', 'DELIVERED', 'FAILURE'])
        .values('shipment__id')
        .distinct()
)