May 20, 2022

Django Query Expressions

Hola.

Esta es la primera entrada de una serie de post en donde hablaremos sobre las query expression en Django, o mejor dicho, cómo hacer querysets mas avanzadas o complejas que se asemejan en su mayoría a SQL.

En Django puedes realizar consultas a la base de datos de tres diversas maneras: mediante el ORM, usando una RawQuerySet, o finalmente mediante la DB-API descrita en el PEP 249.

Teniendo en cuenta el siguiente modelo en Django:

class Book(models.Model):
    title = models.CharField(...)
    price = models.FloatField(...)
    publish_date = models.DateField(...)

Podemos obtener toda la lista de libros mediante tres maneras:

ORM

>>> q = Book.objects.all()
>>> for book in q:
...    print(book.title)
Two Scoops of Django
Django for Professionals
High Performance Django

RawQuerySet

>>> for book in Book.objects.raw('SELECT * FROM myapp_book'):
...    print(book)
Two Scoops of Django
Django for Professionals
High Performance Django

DB-API

>>> from django.db import connection
>>> with connection.cursor() as cursor:
...    cursor.execute(‘SELECT id, title FROM myapp_book’)
...    row = cursor.fetchall()
...
>>> print(row)
((1, ‘Two Scoops of Django’), (2, ‘Django for Professionals’), (3, ‘High Performance Django’))

Expresiones

Django tiene varias expresiones que se asemejan o cumplen la misma función que ciertos tokens en SQL, las expresiones mas comunes son:

  • F(): Representa un campo en especifico del modelo.
  • Func(): Un envoltorio para llamar funciones específicas de tu motor de base de datos.
  • Concat(): A como su nombre lo dice, concatenar dos o mas campos.
  • Lower() y Upper(): Para transformar los valores de un campo en minúsculas o mayúsculas respectivamente.
  • Sum() y Count(): Dos de las operaciones aritméticas mas usadas, para sumar y contar correspondientemente.
  • Value(): La manera mas práctica de exponer un valor no relacionado a un dato en la base de datos.
  • Subquery(): Una manera de encapsular otras operaciones en la base de datos, fuera de la consulta principal.
  • OuterRef(): Para referenciar el campo a usar en el filtro de una Subquery.
  • Case() y When(): A como su nombre lo indica, son expresiones condicionales o la manera “pythonica” de utilizar if, else, elifdentro del ORM de Django.

Aggregate vs Annotate

aggregate: Genera valores de resultados (resumen) en todo un QuerySet. Podemos decir que opera sobre el conjunto de filas para obtener un solo valor de este mismo conjunto.

Por ejemplo, calcular el precio promedio de los libros:

>>> Book.objects.aggregate(average_price=Avg('price'))
{'average_price': 34.35}

annotate: Genera un resumen independiente para cada objeto en un QuerySet. Podemos decir que itera cada objeto dentro de una operación QuerySet y aplica dicha operación.

Por ejemplo, contar el número de autores que cada libro tiene:

>>> q = Book.objects.annotate(num_authors=Count('authors'))
>>> q[0].num_authors
2
>>> q[1].num_authors
1

Dando una respuesta mas práctica, aggregate ejecuta una operación para toda una QuerySet, mientras que annotate lo hace para cada item dentro del mismo QuerySet.

Ejemplo

Ahora bien, considerando la siguiente consulta SQL:

SELECT
    DISTINCT
    p.bpid AS post_id,
    CASE
        WHEN pr.review1_flag IS NULL AND pr.review2_flag IS NULL
        THEN 'Sin Revisar'
        ELSE 'Sin Revisar'
    END AS upload_status,
    CASE
        WHEN (
            pr.post_rejected = True
            AND pr.post_rejected_at_stage = 1
        ) THEN 'Rechazado'
        WHEN pr.review1_flag = True THEN 'Revisado'
        WHEN pr.review1_flag IS NULL THEN 'Sin Revisar'
        ELSE ''
    END AS review1_flag,
    CASE
        WHEN (
            pr.post_rejected = True
            AND pr.post_rejected_at_stage = 2
        ) THEN 'Rechazado'
        WHEN (
            pr.review1_flag = True AND pr.review2_flag = True
        ) THEN 'Aprobado'
        WHEN (
            pr.review1_flag = True AND pr.review2_flag IS NULL
        ) THEN 'Sin Revisar'
        ELSE ''
    END AS review2_flag,
    CASE
        WHEN pr.post_pushed_date IS NOT NULL THEN (
            to_char(pr.post_pushed_date, 'MM/DD/YYYY HH:MI AM')
        )
        ELSE ''
    END AS pushed_date,
    CASE
        WHEN pr.in_review_userid IS NOT NULL THEN (
            SELECT first_name || ' ' || last_name
            FROM auth_user WHERE username = pr.in_review_userid
        )
    ELSE ''
    END AS in_review_fullname
FROM post AS n
LEFT JOIN post_review AS pr ON n.postid = pr.postid

En esta tenemos una union con una tabla, una subquery y varios case que generan otras columnas, esta consulta se puede escribir de igual manera utilizando el ORM de Django y las funciones descritas anteriormente:

usernames_query = get_user_model().objects.filter(
    username=OuterRef('in_review_userid')
).annotate(
    full_name=Concat(
        F('first_name'),
        Value(' '),
        F('last_name'),
        output_field=CharField(),
    )
)

posts = (
    models.Post.objects.all().annotate(
        post_id=F('bpid'),
        upload_status=Case(
            When(
                postreview__review1_flag__isnull=True,
                postreview__review2_flag__isnull=True,
                then=Value('Sin Revisar')
            ),
            default=Value('Sin Revisar'),
            output_field=CharField(),
        ),
        review1_flag=Case(
            When(
                postreview__post_rejected=True,
                postreview__post_rejected_at_stage=1,
                then=Value('Rechazado')
            ),
            When(
                postreview__review1_flag=True,
                then=Value('Revisado')
            ),
            When(
                postreview__review1_flag=False,
                then=Value('Sin Revisar'),
            ),
            default=Value(''),
            output_field=CharField(),
        ),
        review2_flag=Case(
            When(
                postreview__post_rejected=True,
                postreview__post_rejected_at_stage=2,
                then=Value('Rechazado'),
            ),
            When(
                postreview__review2_flag=True,
                then=Value('Aprobado')
            ),
            When(
                postreview__review2_flag__isnull=True,
                then=Value('Sin Revisar'),
            ),
            default=Value(''),
            output_field=CharField(),
        ),
        pushed_date=ToChar(
            F('postreview__post_pushed_date'),
            'MM/DD/YYYY HH:MI AM',
        ),
        in_review_fullname=Case(
            When(
                postreview__in_review_userid__isnull=False,
                then=Subquery(
                    usernames_query.values('full_name')[:1]),
                ),
            ),
            default=Value(''),
            output_field=CharField(),
        )
    ).prefetch_related(
        'postreview',
    ).order_by(
        'post_id',
    ).distinct(
        'post_id',
    ).values(
        'post_id',
        'review1_flag',
        'review2_flag',
        'pushed_date',
        'in_review_fullname',
    )
)

De la consulta anterior se puede resaltar la creación de una Subquery para obtener el nombre completo del usuario que esta revisando el post, en los Case() se ha agregado como parámetro output_field=CharField() esto debido a que Django necesita saber que tipo de dato va a retornar al momento de ejecutar la operación, de igual forma se utilizó el prefetch_related para precargar la cache de la BD con los registros de revisiones del post y asi evitar generar una consulta N+1, y por ultimo el uso de values para convertir la consulta a un diccionario y limitar los campos retornados por la instrucción SELECT, haciendo de esta forma la consulta mas ligera en el uso de memoria.

A su vez, si se hace un print() mediante print(posts.query) se puede saber la forma en la que Django esta traduciendo o generando la consulta mediante el ORM hacia algo entendible por el gestor de base de datos:

SELECT DISTINCT
   "post"."bpid" AS "post_id",
   CASE
      WHEN
         (
            "post_review"."review1_flag" IS NULL 
            AND "post_review"."review2_flag" IS NULL 
         )
      THEN
         'Sin Revisar' 
      ELSE
         'Sin Revisar' 
   END
   AS "upload_status", 
   CASE
      WHEN
         (
            "post_review"."post_rejected" = true 
            AND "post_review"."post_rejected_at_stage" = 1
         )
      THEN
         'Rechazado' 
      WHEN
         "post_review"."review1_flag" = true
      THEN
         'Revisado'
      WHEN
         "post_review"."review1_flag" = false 
      THEN
         'Sin Revisar' 
      ELSE
         '' 
   END
   AS "review1_flag", 
   CASE
      WHEN
         (
            "post_review"."post_rejected" = true 
            AND "post_review"."post_rejected_at_stage" = 2
         )
      THEN
         'Rechazado' 
      WHEN
         "post_review"."review2_flag" = true
      THEN
         'Aprobado' 
      WHEN
         "post_review"."review2_flag" IS NULL
      THEN
         'Sin Revisar' 
      ELSE
         '' 
   END
   AS "review2_flag",
   TO_CHAR(
      "post_review"."post_pushed_date",
      "MM/DD/YYYY HH:MI AM"
   ) AS "approval_date",
   CASE
      WHEN
         "post_review"."in_review_userid" IS NOT NULL 
      THEN (
         SELECT
            CONCAT(
               U0."first_name", 
               CONCAT(' ', U0."last_name")
            ) AS "full_name" 
         FROM
            "auth_user" U0 
         WHERE
            U0."username" = 
            CASE
               WHEN
                  "post_review"."in_review_userid" IS NULL 
               THEN
                  '' 
               ELSE
                  "post_review"."in_review_userid" 
            END
            LIMIT 1
      ) 
      ELSE
         '' 
   END
   AS "in_review_fullname" 
FROM "post" 
LEFT OUTER JOIN "post_review" ON (
   "post"."bpid" = "post_review"."bpid"
) 
ORDER BY "post"."bpid" ASC;

Se se considera la diferencia no es mucha entre hacerla de manera manual y utilizar el ORM, aunque mas allá del “performance” lo que se gana en usar el ORM es la facilidad de los desarrolladores de entender la consulta (en algunos casos) y la capacidad de iterar sobre esta sin introducir errores de seguridad.

Y de esa manera se ha transformado una consulta SQL a Django, hay que recordar que estas acciones no siempre serán los mas idóneos o se convertirán en una mejoría a largo plazo, es por ello que te dejo algunas recomendaciones a continuación.

Recomendaciones

Cabe aclarar que no siempre una consulta generada por el ORM de Django pueda ser mas rápida que una consulta usando SQL plano, por tal razón cuando tengas que sobre reescribir una consulta de SQL a ORM se debe tener en cuenta los siguientes consejos para validar que los cambios valgan la pena:

  • Lee la documentación de Django primero.
  • Recuerda siempre “El Zen de Python” (Explicit is better than implicit. Readability counts. PEP 20)
  • Los cálculos a nivel de datos es mejor hacerlo en la base de datos en lugar de hacerlos en Python.
  • Se puede usar Django-Debug-Toolbar para monitorear consultas.
  • No esta de más usar el método query para indagar sobre el SQL generado por Django.
>>> q = Book.objects.all()
>>> print(q.query)

SELECT 
    “myapp_book”.id,
    “myapp_book”.”title”
    … 
FROM “myapp_book”; 
  • explain para revisar el plan de ejecución de la consulta, y de esta manera indagar sobre qué índices o uniones se están utilizando.
>>> q = Book.objects.filter(title='Two Scoops of Django')
>>> print(q.explain(verbose=True, analyze=True))

Seq Scan on public.blog  (cost=0.00..35.50 rows=10 width=12) (actual time=0.004..0.004 rows=10 loops=1)
  Output: id, title, price, publish_date
  Filter: (book.title = 'Two Scoops of Django'::bpchar)
Planning time: 0.064 ms
Execution time: 0.058 ms
  • En algunos casos usar consultas fuera del ORM puede resultar en posibles agujeros de seguridad ante ataques de SQL Injection.
  • Cuando se usa una RawQuerySet Django no verifica que la consulta SQL sea correcta, lo que podria generar errores.
  • En una RawQuerySet el indizado y limite no se hacen a nivel de base de datos.
  • Es muy común que al usar el ORM tu consulta pueda resultar en un error del tipo N+1 .
  • Utiliza prefetch_related y select_related según sea necesario.
  • Y finalmente DON’T TAKE ANYTHING FOR GRANTED.

Espero que este post sea de utilidad, mas adelante explicaré como construir nuestras propias Query Expressions y de ser posibles algunas recomendaciones para tener consultas SQL sanas.

Agradecer a @luismejiadev por motivarme a escribir este post y a @nicolefrale for las debidas revisiones y sugerencias.

Hasta la próxima.