fields - django-admin

Django 1.11 anotando un agregado de subconsulta

Question

Esta es una característica de vanguardia que actualmente estoy ensartada y que estoy desangrando rápidamente. Quiero anotar una subconsulta agregada en un queryset existente. Hacer esto antes de la 1.11 significaba un SQL personalizado o martillar la base de datos. Aquí está la documentación para esto , y el ejemplo de esto:

from django.db.models import OuterRef, Subquery, Sum
comments = Comment.objects.filter(post=OuterRef(''pk'')).values(''post'')
total_comments = comments.annotate(total=Sum(''length'')).values(''total'')
Post.objects.filter(length__gt=Subquery(total_comments))

Están anotando en el agregado, lo que me parece extraño, pero como sea.

Estoy luchando con esto, así que lo estoy devolviendo al ejemplo más simple del mundo real para el que tengo datos. Tengo Carpark s que contienen muchos Space s. Use Book→Author si eso lo hace más feliz pero, por ahora, solo quiero hacer una anotación en el conteo del modelo relacionado mediante la Subquery *.

spaces = Space.objects.filter(carpark=OuterRef(''pk'')).values(''carpark'')
count_spaces = spaces.annotate(c=Count(''*'')).values(''c'')
Carpark.objects.annotate(space_count=Subquery(count_spaces))

Esto me da un bonito error de ProgrammingError: more than one row returned by a subquery used as an expression y en mi cabeza, este error tiene mucho sentido. La subconsulta está devolviendo una lista de espacios con el total anotado.

El ejemplo sugirió que sucedería algún tipo de magia y terminaría con un número que podría usar. ¿Pero eso no está pasando aquí? ¿Cómo hago anotaciones en los datos agregados de la subconsulta?

Hmm, algo se está agregando al SQL de mi consulta ...

Construí un nuevo modelo Carpark / Space y funcionó. Así que el siguiente paso es resolver qué está envenenando mi SQL. Siguiendo el consejo de Laurent, eché un vistazo al SQL y traté de hacerlo más parecido a la versión que publicaron en su respuesta. Y aquí es donde encontré el verdadero problema:

SELECT "bookings_carpark".*, (SELECT COUNT(U0."id") AS "c"
FROM "bookings_space" U0
WHERE U0."carpark_id" = ("bookings_carpark"."id")
GROUP BY U0."carpark_id", U0."space"
)
AS "space_count" FROM "bookings_carpark";

Lo he resaltado pero es la subconsulta de GROUP BY ... U0."space" . Es volver a sintonizar ambos por alguna razón. Las investigaciones continúan.

Edit 2: De acuerdo, solo con ver la subconsulta SQL puedo ver el segundo grupo entrando a través de ☹

In [12]: print(Space.objects_standard.filter().values(''carpark'').annotate(c=Count(''*'')).values(''c'').query)
SELECT COUNT(*) AS "c" FROM "bookings_space" GROUP BY "bookings_space"."carpark_id", "bookings_space"."space" ORDER BY "bookings_space"."carpark_id" ASC, "bookings_space"."space" ASC

Edición 3 : ¡De acuerdo! Ambos modelos tienen orden de clasificación. Estos están siendo llevados a través de la subconsulta. Son estas órdenes las que están hinchando mi consulta y rompiéndola.

Supongo que esto podría ser un error en Django pero, a menos que se elimine el Meta-order_by en estos dos modelos, ¿hay alguna forma de anular una consulta en el momento de la consulta?

_{* Sé que solo podría anotar una cuenta para este ejemplo . Mi propósito real para usar esto es un recuento de filtros mucho más complejo, pero ni siquiera puedo hacer que esto funcione.}

Answer 1

"funciona para mí" no ayuda mucho. Pero. Probé su ejemplo en algunos modelos que tenía a mano (el Book -> Author Tipo de Book -> Author ), me funciona bien en django 1.11b1.

¿Estás seguro de que estás ejecutando esto en la versión correcta de Django? ¿Es este el código real que está ejecutando? ¿Realmente estás probando esto no en el carpark sino en un modelo más complejo?

Tal vez intente print(thequery.query) para ver qué SQL está intentando ejecutar en la base de datos. A continuación se muestra lo que obtuve con mis modelos (editado para que se ajuste a su pregunta):

SELECT (SELECT COUNT(U0."id") AS "c"
FROM "carparks_spaces" U0
WHERE U0."carpark_id" = ("carparks_carpark"."id")
GROUP BY U0."carpark_id") AS "space_count" FROM "carparks_carpark"

No es realmente una respuesta, pero espero que ayude.

Answer 2

Me topé con un caso MUY similar, en el que tuve que obtener reservas de asientos para eventos en los que el estado de la reserva no se cancela. Después de tratar de resolver el problema durante horas, esto es lo que he visto como la causa raíz del problema:

Prefacio: esta es MariaDB, Django 1.11.

Cuando se anota una consulta, se obtiene una cláusula GROUP BY con los campos que selecciona (básicamente, lo que está en la selección de consulta de sus values() ). Después de investigar con la herramienta de línea de comandos MariaDB por qué obtengo NULL s o None s en los resultados de la consulta, he llegado a la conclusión de que la cláusula GROUP BY hará que COUNT() devuelva NULL s.

Luego, comencé a sumergirme en la interfaz QuerySet para ver cómo puedo eliminar manualmente el GROUP BY de las consultas de la base de datos y se me ocurrió el siguiente código:

from django.db.models.fields import PositiveIntegerField

reserved_seats_qs = SeatReservation.objects.filter(
        performance=OuterRef(name=''pk''), status__in=TAKEN_TYPES
    ).values(''id'').annotate(
        count=Count(''id'')).values(''count'')
# Query workaround: remove GROUP BY from subquery. Test this
# vigorously!
reserved_seats_qs.query.group_by = []

performances_qs = Performance.objects.annotate(
    reserved_seats=Subquery(
        queryset=reserved_seats_qs,
        output_field=PositiveIntegerField()))

print(performances_qs[0].reserved_seats)

Básicamente, debe eliminar / actualizar manualmente el campo group_by el group_by de la subconsulta para que no tenga un GROUP BY agregado en el tiempo de ejecución. Además, tendrá que especificar qué campo de salida tendrá la subconsulta, ya que parece que Django no lo reconoce automáticamente y genera excepciones en la primera evaluación del conjunto de consultas. Curiosamente, la segunda evaluación tiene éxito sin ella.

Creo que esto es un error de Django o una ineficiencia en las subconsultas. Voy a crear un informe de error al respecto.

Editar: el informe de error está aquí .

Answer 3

Se podría implementar una solución que funcionaría para cualquier agregación general utilizando clases de Window de Django 2.0. También he añadido esto al ticket de rastreador de Django.

Esto permite la agregación de valores anotados calculando el agregado sobre particiones en función del modelo de consulta externo (en la cláusula GROUP BY), y luego anotando esos datos a cada fila en el queryset de la subconsulta. La subconsulta puede usar los datos agregados de la primera fila devuelta e ignorar las otras filas.

Performance.objects.annotate(
    reserved_seats=Subquery(
        SeatReservation.objects.filter(
            performance=OuterRef(name=''pk''),
            status__in=TAKEN_TYPES,
        ).annotate(
            reserved_seat_count=Window(
                expression=Count(''pk''),
                partition_by=[F(''performance'')]
            ),
        ).values(''reserved_seat_count'')[:1],
        output_field=FloatField()
    )
)

Answer 4

Shazaam! Según mis ediciones, se estaba generando una columna adicional de mi subconsulta. Esto fue para facilitar el pedido (que simplemente no se requiere en un COUNT).

Solo necesitaba eliminar la meta-orden prescrita del modelo. Puede hacerlo simplemente agregando un .order_by() vacío a la subconsulta. En mis términos de código eso significaba:

spaces = Space.objects.filter(carpark=OuterRef(''pk'')).order_by().values(''carpark'')
count_spaces = spaces.annotate(c=Count(''*'')).values(''c'')
Carpark.objects.annotate(space_count=Subquery(count_spaces))

Y eso funciona. Magníficamente Muy molesto.

Answer 5

Si entiendo correctamente, estás tratando de contar los Space disponibles en un Carpark . La subconsulta parece exagerada por esto, solo el buen comentario antiguo debería hacer el truco:

Carpark.objects.annotate(Count(''spaces''))

Esto incluirá un valor de spaces__count en sus resultados.

OK, he visto tu nota ...

También pude hacer tu misma consulta con otros modelos que tenía a mano. Los resultados son los mismos, por lo que la consulta en su ejemplo parece estar bien (probado con Django 1.11b1):

activities = Activity.objects.filter(event=OuterRef(''pk'')).values(''event'')
count_activities = activities.annotate(c=Count(''*'')).values(''c'')
Event.objects.annotate(spaces__count=Subquery(count_activities))

Tal vez su "ejemplo más simple del mundo real" sea demasiado simple ... ¿puede compartir los modelos u otra información?

Answer 6

También es posible crear una subclase de Subquery , que cambia el SQL que genera. Por ejemplo, puedes usar:

class SQCount(Subquery):
    template = "(SELECT count(*) FROM (%(subquery)s) _count)"
    output_field = models.IntegerField()

Luego, usa esto como lo haría con la clase de Subquery original:

spaces = Space.objects.filter(carpark=OuterRef(''pk'')).values(''pk'')
Carpark.objects.annotate(space_count=SQCount(spaces))

Puede usar este truco (al menos en postgres) con un rango de funciones de agregación: a menudo lo uso para construir una matriz de valores, o sumarlos.