jsonfield - django raw query

¿Cómo usar la subconsulta en django?

Question

Quiero obtener una lista de las últimas compras de cada cliente, ordenadas por fecha.

La siguiente consulta hace lo que quiero a excepción de la fecha:

(Purchase.objects
         .all()
         .distinct(''customer'')
         .order_by(''customer'', ''-date''))

Produce una consulta como:

SELECT DISTINCT ON 
    "shop_purchase.customer_id" 
    "shop_purchase.id" 
    "shop_purchase.date" 
FROM "shop_purchase" 
ORDER BY "shop_purchase.customer_id" ASC, 
         "shop_purchase.date" DESC;

Estoy obligado a usar customer_id como la primera expresión ORDER BY debido a DISTINCT ON .

Quiero ordenar por fecha, por lo que la consulta que realmente necesito debería tener este aspecto:

SELECT * FROM (
  SELECT DISTINCT ON 
      "shop_purchase.customer_id" 
      "shop_purchase.id" 
      "shop_purchase.date" 
  FROM "shop_purchase" 
  ORDER BY "shop_purchase.customer_id" ASC, 
           "shop_purchase.date" DESC;
  )
AS result 
ORDER BY date DESC;

No quiero ordenar usando Python porque todavía tengo que limitar la consulta a la página. Puede haber decenas de miles de filas en la base de datos.

De hecho, actualmente está ordenado por Python y está causando tiempos de carga de página muy largos, por lo que estoy tratando de arreglar esto.

Básicamente quiero algo como esto https://stackoverflow.com/a/9796104/242969 . ¿Es posible expresarlo con querysets django en lugar de escribir SQL en bruto?

Los modelos y métodos reales son de varias páginas, pero aquí está el conjunto de modelos requeridos para el conjunto de consultas anterior.

class Customer(models.Model):
  user = models.OneToOneField(User)

class Purchase(models.Model):
  customer = models.ForeignKey(Customer)
  date = models.DateField(auto_now_add=True)
  item = models.CharField(max_length=255)

Si tengo datos como:

Customer A - 
    Purchase(item=Chair, date=January), 
    Purchase(item=Table, date=February)
Customer B - 
    Purchase(item=Speakers, date=January), 
    Purchase(item=Monitor,  date=May)
Customer C - 
    Purchase(item=Laptop,  date=March), 
    Purchase(item=Printer, date=April)

Quiero poder extraer lo siguiente:

Purchase(item=Monitor, date=May)
Purchase(item=Printer, date=April)
Purchase(item=Table,   date=February)

Hay como máximo una compra en la lista por cliente. La compra es la última de cada cliente. Está ordenado por última fecha.

Esta consulta podrá extraer eso:

SELECT * FROM (
  SELECT DISTINCT ON 
    "shop_purchase.customer_id" 
    "shop_purchase.id" 
    "shop_purchase.date" 
  FROM "shop_purchase" 
  ORDER BY "shop_purchase.customer_id" ASC, 
           "shop_purchase.date" DESC;
) 
AS result 
ORDER BY date DESC;

Estoy tratando de encontrar una manera de no tener que usar SQL en bruto para lograr este resultado.

Answer 1

Este es mi enfoque si necesito algún subconjunto de datos (elementos N) junto con la consulta de Django. Este es un ejemplo que utiliza PostgreSQL y la práctica función json_build_object() (Postgres 9.4+), pero de la misma manera puede usar otra función agregada en otro sistema de base de datos. Para versiones anteriores de PostgreSQL puede usar una combinación de array_agg() y array_to_string() .

Imagine que tiene modelos de Article y Comment y junto con cada artículo de la lista que desea seleccionar 3 comentarios recientes (cambie el LIMIT 3 para ajustar el tamaño del subconjunto u ORDER BY c.id DESC para cambiar la clasificación del subconjunto).

qs = Article.objects.all()
qs = qs.extra(select = {
    ''recent_comments'': """
    SELECT
        json_build_object(''comments'',
            array_agg(
              json_build_object(''id'', id, ''user_id'', user_id, ''body'', body)
            )
        )
    FROM (
        SELECT
          c.id,
          c.user_id,
          c.body
        FROM app_comment c
        WHERE c.article_id = app_article.id
        ORDER BY c.id DESC
        LIMIT 3
    ) sub
    """
})

for article in qs:
    print(article.recent_comments)

# Output:
# {u''comments'': [{u''user_id'': 1, u''id'': 3, u''body'': u''foo''}, {u''user_id'': 1, u''id'': 2, u''body'': u''bar''}, {u''user_id'': 1, u''id'': 1, u''body'': u''joe''}]}
# ....

Answer 2

Puede que esto no sea exactamente lo que estás buscando, pero podría acercarte más. Echa un vistazo a la anotación de Django .

Aquí hay un ejemplo de algo que puede ayudar:

  from django.db.models import Max
  Customer.objects.all().annotate(most_recent_purchase=Max(''purchase__date''))

Esto le dará una lista de los modelos de sus clientes, cada uno de los cuales tendrá un nuevo atributo llamado "la mayoría de las compras recientes" y contendrá la fecha en la que hicieron su última compra. El sql producido se ve así:

SELECT "demo_customer"."id", 
       "demo_customer"."user_id", 
       MAX("demo_purchase"."date") AS "most_recent_purchase"
FROM "demo_customer"
LEFT OUTER JOIN "demo_purchase" ON ("demo_customer"."id" = "demo_purchase"."customer_id")
GROUP BY "demo_customer"."id",
         "demo_customer"."user_id"

Otra opción, sería agregar una propiedad a su modelo de cliente que se vería así:

  @property
  def latest_purchase(self):
    return self.purchase_set.order_by(''-date'')[0]

Obviamente, deberá manejar el caso en el que no haya compras en esta propiedad, y esto posiblemente no funcionará muy bien (ya que estaría ejecutando una consulta para cada cliente para obtener su última compra).

He usado ambas técnicas en el pasado y ambas han funcionado bien en diferentes situaciones. Espero que esto ayude. ¡La mejor de las suertes!

Answer 3

Siempre que haya una consulta difícil de escribir con Django ORM, primero intento la consulta en psql (o cualquier cliente que use). El SQL que desea no es este:

SELECT * FROM (
  SELECT DISTINCT ON 
    "shop_purchase.customer_id" "shop_purchase.id" "shop_purchase.date" 
  FROM "shop_purchase" 
  ORDER BY "shop_purchase.customer_id" ASC, "shop_purchase.date" DESC;
  ) AS result 
ORDER BY date DESC;

En el SQL anterior, el SQL interno está buscando distintos en una combinación de (Id. De cliente, id y fecha) y como id será único para todos, obtendrá todos los registros de la tabla. Supongo que id es la clave principal según la convención.

Si necesita encontrar la última compra de cada cliente, debe hacer algo como:

SELECT  "shop_purchase.customer_id", max("shop_purchase.date")
FROM shop_purchase
GROUP BY 1 

Pero el problema con la consulta anterior es que le dará solo el nombre y la fecha del cliente. El uso de eso no lo ayudará a encontrar los registros cuando use estos resultados en una subconsulta.

Para usar IN necesita una lista de parámetros únicos para identificar un registro, por ejemplo, id

Si en su registro, la identificación es una clave de serie, entonces puede aprovechar el hecho de que la fecha más reciente también será la identificación máxima. Entonces tu SQL se convierte en:

SELECT  max("shop_purchase.id") 
FROM shop_purchase
GROUP BY "shop_purchase.customer_id";

Tenga en cuenta que mantuve solo un campo ( id ) en la cláusula seleccionada para usarlo en una subconsulta usando IN.

El SQL completo ahora será:

SELECT * 
FROM shop_customer 
WHERE "shop_customer.id" IN 
    (SELECT  max("shop_purchase.id") 
     FROM shop_purchase
     GROUP BY "shop_purchase.customer_id");

y usando el ORM de Django parece:

(Purchase.objects.filter(
    id__in=Purchase.objects
                   .values(''customer_id'')
                   .annotate(latest=Max(''id''))
                   .values_list(''latest'', flat=True)))

¡Espero eso ayude!

Answer 4

Tengo una situación similar y así es como planeo hacerlo:

query = Purchase.objects.distinct(''customer'').order_by(''customer'').query
query = ''SELECT * FROM ({}) AS result ORDER BY sent DESC''.format(query)
return Purchase.objects.raw(query)

Al revés me da la consulta que quiero. El inconveniente es que es una consulta sin formato y no puedo agregar ningún otro filtro de conjunto de consultas.