为什么django queryset.extra()抛出OperationalError:(1242，'Subquery返回多于1行')? [英] Why does django queryset.extra() throws OperationalError: (1242, 'Subquery returns more than 1 row')?

问题描述

我在 MySQL 中使用了 Django ORM ，并浪费了很多查询时间.对于某些高级"情况，我决定进行原始查询，因为对于那些情况，我无法使用批注进行查询.原始查询的问题是不要在查询集中添加字段"，例如注释或聚合.因此，我正在使用 额外.但是现在我遇到了一个问题:

I'm using Django ORM with MySQL and wasting a lot of time with querys. For some "advanced" cases i decided to go for raw queries, since for those, i couldn't make it with annotations. The problem with raw queries is that the don't add a "field" to the queryset like annotations or aggregations. So, i'm using extra. But now i'm facing a problem:

qs_products = Productos.objects.all()
qs_productos.extra({
    "stock":
    """
        SELECT SUM(items.cantidad)
        FROM 
            `encargosProveedor_listado_articulos` AS encargos, 
            `itemArticulosProveedor`AS items, `articulos` as articulos
        WHERE 
            encargos.itemarticulosproveedor_id=items.id and 
            articulos.id=items.articulos_id
        GROUP BY articulos.producto_id
    """
    })

这是直接来自我的数据库管理员的该查询的结果:

This is the result for this query directly from my db admin:

+---------------------+
| SUM(items.cantidad) |
+---------------------+
|          14         |
+---------------------+
|          4          |
+---------------------+

但是当使用 extra()

MySQLdb._exceptions.OperationalError:(1242，子查询返回多于1行")

MySQLdb._exceptions.OperationalError: (1242, 'Subquery returns more than 1 row')

返回多于一行的问题是什么?该查询返回两行，因为我有两种产品，这是合理的.我想为每种产品分配 stock .

What is the problem for returning more than one row? The query is returning two rows because i have two products, its reasonable. I want to assign stock to each one of the products.

替代品?有什么建议吗?提示?

推荐答案

您正在尝试在外部查询和extra子查询之间进行联接，并且数据库需要一个显式的join子句来做到这一点.我相信您可以在extra上添加WHERE子句以使其起作用:

You're trying to do a join between the outer query and the extra subquery, and the database needs an explicit join clause to do that. I believe you can add a WHERE clause to the extra for it to work:

qs_productos.extra({
    "stock":
    """
        SELECT SUM(items.cantidad)
        FROM 
            `encargosProveedor_listado_articulos` AS encargos, 
            `itemArticulosProveedor`AS items, `articulos` as articulos
        WHERE 
            articulos.producto_id = productos.id and
            encargos.itemarticulosproveedor_id=items.id and 
            articulos.id=items.articulos_id       
        GROUP BY articulos.producto_id
    """
})

articulos.producto_id = productos.id中的

productos将需要用主查询中的实际表名代替.

productos in articulos.producto_id = productos.id would need to be replaced by the actual table name from the main query.

由于extra已准备好弃用(虽然从Django 2.2开始尚未弃用)，所以这是等效的

Since extra is lined up for deprecation (not quite deprecated yet though as of Django 2.2), here's the equivalent RawSQL query:

qs_productos.annotate(stock=RawSQL(
    """
        SELECT SUM(items.cantidad)
        FROM 
            `encargosProveedor_listado_articulos` AS encargos, 
            `itemArticulosProveedor`AS items, `articulos` as articulos
        WHERE 
            articulos.producto_id = productos.id and
            encargos.itemarticulosproveedor_id=items.id and 
            articulos.id=items.articulos_id       
        GROUP BY articulos.producto_id
    """,
    ()
))

RawSQL中的空元组是必需的，因为该函数接受一个params元组参数，即使您没有要传递的参数，该参数也不是可选的.

The empty tuple in RawSQL is required since the function takes a params tuple argument that is not optional even when you have no parameters to pass.

作为奖励，RawSQL对以前的values调用不太敏感，并在需要时提供可选的output_field参数.

As a bonus, RawSQL is less sensitive to prior values calls and offers an optional output_field parameter when needed.

这篇关于为什么django queryset.extra()抛出OperationalError:(1242，'Subquery返回多于1行')?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！