python - Django ORM 无法为不在其中的多对多生成正确的 SQL

Question

我在使用 Django 从 ORM 生成的 SQL 时遇到问题。

Cartons 通过 cartons_shipments 与 Shipments 具有多对多关系。

我希望排除至少有一个 INBOUND 纸箱状态为 ['TRANSIT', 'DELIVERED', 'FAILURE'] 的货件.

但是我没有得到预期的结果，所以我打开了 SQL 日志记录。

return Shipment.objects.filter(
    ... # other filtering
    # does not have any inbound cartons in_transit/delivered/failed
    ~Q(
        Q(cartons__type='INBOUND') &
        Q(cartons__status__in=['TRANSIT', 'DELIVERED', 'FAILURE'])
    ) &
).distinct()

我也尝试过将此作为我的过滤器，但得到了相同的 SQL 输出。

~Q(
    cartons__type='INBOUND', 
    cartons__status__in=['TRANSIT', 'DELIVERED', 'FAILURE']
)

这会生成以下 SQL:

AND NOT (
    "shipments"."id" IN (
        SELECT U1."shipment_id" 
        FROM "cartons_shipments" U1 
        INNER JOIN "cartons" U2 ON (U1."carton_id" = U2."id") 
        WHERE U2."type" = 'INBOUND'
    ) 
    AND "shipments"."id" IN (
        SELECT U1."shipment_id" FROM "cartons_shipments" U1 
        INNER JOIN "cartons" U2 ON (U1."carton_id" = U2."id") 
        WHERE U2."status" IN ('TRANSIT', 'DELIVERED', 'FAILURE')
    )
) 

但这并不正确，因为它会排除具有任何 INBOUND 纸箱的货件以及任何 纸箱(不一定INBOUND 纸箱)的状态为 ['TRANSIT', 'DELIVERED', 'FAILURE']。我需要结合这个逻辑。

此外，现在我正在运行 2 个子选择，并且性能受到显着影响，因为我们有大量处于这些状态的纸箱。

正确的 SQL 应该是这样的:

AND NOT ("shipments"."id" IN (
    SELECT U1."shipment_id" 
    FROM "cartons_shipments" U1 
    INNER JOIN "cartons" U2 ON (U1."carton_id" = U2."id") 
    WHERE U2."type" = 'INBOUND'
    and U2."status" IN ('TRANSIT', 'DELIVERED', 'FAILURE')
))

这样我就可以只排除处于这些状态的 INBOUND 纸箱的货件。

这两者之间的查询时间很长，当然我能够通过第二个 SQL 示例获得正确的结果。我认为我可以通过组合 Q() 对象来组合该逻辑。但想不通。

我还认为也许我可以纠正第二个示例中的原始 SQL。但我很难弄清楚如何将原始 sql 与其他 ORM 过滤器结合起来。

任何帮助将不胜感激。

<小时/>

编辑:

我可以通过在代码中进行过滤并从查询中删除过滤器来获得正确的结果:

returned_cartons = Carton.objects.prefetch_related('shipments').filter(
    type='INBOUND',
    status__in=['TRANSIT', 'DELIVERED', 'FAILURE']
)

returned_shipment_ids = list(map(
    lambda carton: carton.shipments.first().id,
    returned_cartons
))

return list(filter(
    lambda shipment: shipment.id not in returned_shipment_ids,
    shipments
))

不幸的是，这太慢了，没有什么用处。

<小时/>

基于 Endre Both 想法的最终解决方案 🙌

return Shipment.objects.filter(
    ...,  # other filtering
    # has at least 1 inbound carton
    Q(cartons__type='INBOUND')
).exclude(
    # we want to exclude shipments that have at least 1 inbound cartons
    # with a status in transit/delivered/failure
    id__in=Shipment.objects.filter(
        ...,  # filters to limit the number of records returned
        cartons__type='INBOUND',
        cartons__status__in=['TRANSIT', 'DELIVERED', 'FAILURE'],
    ).distinct()
).distinct()

此行 Q(cartons__type='INBOUND') 是必需的，因为我们排除 ['TRANSIT', 'DELIVERED' 中具有 INBOUND 纸箱的货件', 'FAILURE'] 状态。但我们也会保留没有纸箱的 cargo 。

希望这可以帮助更多的人。

Answer 1

对于我们这些凡人来说，ORM 中的“M”有时可能有点难以理解。但你可以尝试一种不同的、更简单的方法。它仍然使用子查询而不是联接，但这不一定会拖累性能。

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

从 Carton 模型返回到 Shipment 主键的引用的确切名称取决于模型的确切定义。我使用了 shipments__id，但也可能是 shipment_set__id 或其他内容。

<小时/>

新想法:您需要将子选择基于中间模型而不是Cartons。如果你有一个明确的中间模型，这很容易，如果没有，你首先需要一个 Shipment 或 Cartons 对象，因为据我所知你无法获得仅从实例从类本身引用中间模型。

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()
)