CODEWITHSUNDEEP
pythonmysqldjangodjango-models
Hernan
Hernan

Reputation: 1148

Why does django queryset.extra() throws OperationalError: (1242, 'Subquery returns more than 1 row')?

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          |
+---------------------+

But when running this code under django using extra()

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

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.

Alternatives? Suggestions? Hints?

Upvotes: 1

Views: 683

Answers (1)

Endre Both
Endre Both

Reputation: 5730

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
    """
})

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

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

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.

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

Upvotes: 1

Related Questions