Esta es una característica de última generación en la que actualmente estoy ensartado y me estoy desangrando rápidamente. Quiero anotar un agregado de subconsulta en un conjunto de consultas existente. Hacer esto antes de 1.11 significaba SQL personalizado o forzar la base de datos. Aquí está la documentación para esto , y el ejemplo de él:
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 lo que 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 anotar en un recuento del modelo relacionado usando 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 error ProgrammingError: more than one row returned by a subquery used as an expression y, en mi cabeza, este error tiene mucho sentido. La subconsulta devuelve una lista de espacios con el total anotado.
El ejemplo sugería que ocurrirí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 datos de subconsulta agregados?
Construí un nuevo modelo Carpark/Space y funcionó. Entonces, el siguiente paso es averiguar 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 GROUP BY ... U0."space" de esa subconsulta. Es volver a sintonizar ambos por alguna razón. Las investigaciones continúan.
Edición 2: Bien, solo mirando la subconsulta SQL puedo ver ese segundo grupo al pasar ☹
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" ASCEdición 3 : ¡Bien! Ambos modelos tienen órdenes de clasificación. Estos se están llevando 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 aparte de eliminar Meta-order_by en ambos modelos, ¿hay alguna forma de desordenar una consulta en el momento de la consulta?
* Sé que podría simplemente anotar un recuento para este ejemplo . Mi verdadero propósito para usar esto es un conteo de filtros mucho más complejo, pero ni siquiera puedo hacer que esto funcione.
Si entiendo correctamente, está tratando de contar Space disponibles en un Carpark . La subconsulta parece exagerada para esto, la buena y antigua anotación por sí sola debería ser suficiente:
Carpark.objects.annotate(Count('spaces')) Esto incluirá un valor de spaces__count de espacios__ en sus resultados.
Vale, he visto tu nota...
También pude ejecutar 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" es demasiado simple... ¿puede compartir los modelos u otra información?
"funciona para mí" no ayuda mucho. Pero. Probé su ejemplo en algunos modelos que tenía a mano (el tipo Book -> Author ), funciona bien para mí 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ás ejecutando? ¿Realmente estás probando esto no en un carpark sino en un modelo más complejo?
Tal vez intente print(thequery.query) para ver qué SQL está tratando de ejecutar en la base de datos. A continuación se muestra lo que obtuve con mis modelos (editado para adaptarse 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.
¡Shazaam! Según mis ediciones, se estaba generando una columna adicional desde mi subconsulta. Esto fue para facilitar el pedido (que simplemente no se requiere en un COUNT).
Solo necesitaba eliminar el meta-orden prescrito del modelo. Puede hacer esto simplemente agregando un .order_by() vacío a la subconsulta. En mis términos de código eso significaba:
from django.db.models import Count, OuterRef, Subquery 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.
Acabo de encontrarme con un caso MUY similar, en el que tenía que conseguir reservas de asientos para eventos en los que el estado de la reserva no se cancelaba. Después de tratar de resolver el problema durante horas, esto es lo que he visto como la causa raíz del problema:
Prefacio: esto es MariaDB, Django 1.11.
Cuando anota una consulta, obtiene una cláusula GROUP BY con los campos que selecciona (básicamente lo que está en su selección de consulta de values() ). Después de investigar con la herramienta de línea de comandos de MariaDB por qué obtengo NULL s o None s en los resultados de la consulta, llegué a la conclusión de que la cláusula GROUP BY hará que COUNT() devuelva NULL s.
Luego, comencé a sumergirme en la interfaz de QuerySet para ver cómo puedo eliminar manualmente, a la fuerza, GROUP BY de las consultas de la base de datos, y obtuve 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 en el conjunto de consultas de la subconsulta para que no tenga un GROUP BY agregado en el tiempo de ejecución. Además, deberá especificar qué campo de salida tendrá la subconsulta, ya que parece que Django no la 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 errores al respecto.
Editar: el informe de error está aquí .
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 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 una variedad de funciones de agregación: a menudo lo uso para crear una matriz de valores o sumarlos.
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 agregado esto al ticket del rastreador de Django.
Esto permite la agregación de valores anotados mediante el cálculo del agregado sobre particiones en función del modelo de consulta externo (en la cláusula GROUP BY), y luego la anotación de esos datos en cada fila del conjunto de consulta de 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() ) ) El problema es que Django agrega GROUP BY tan pronto como ve que usa una función agregada.
Entonces, puede crear su propia función agregada, pero para que Django piense que no es agregada. Así como esto:
total_comments = Comment.objects.filter( post=OuterRef('pk') ).order_by().annotate( total=Func(F('length'), function='SUM') ).values('total') Post.objects.filter(length__gt=Subquery(total_comments))De esta manera obtienes la consulta SQL de esta manera:
SELECT "testapp_post"."id", "testapp_post"."length" FROM "testapp_post" WHERE "testapp_post"."length" > (SELECT SUM(U0."length") AS "total" FROM "testapp_comment" U0 WHERE U0."post_id" = "testapp_post"."id")Puede contar el número de días laborables entre dos fechas, excluyendo fines de semana y días festivos, y agregarlos y resumirlos por empleado:
class NonWorkDay(models.Model): date = DateField() class WorkPeriod(models.Model): employee = models.ForeignKey(User, on_delete=models.CASCADE) start_date = DateField() end_date = DateField() number_of_non_work_days = NonWorkDay.objects.filter( date__gte=OuterRef('start_date'), date__lte=OuterRef('end_date'), ).annotate( cnt=Func('id', function='COUNT') ).values('cnt') WorkPeriod.objects.values('employee').order_by().annotate( number_of_word_days=Sum(F('end_date__year') - F('start_date__year') - number_of_non_work_days) )¡Espero que esto ayude!