Trabajar con una base de datos en Drupal 7 - Lección 4 - Consultas dinámicas (SELECT)

Probablemente hemos llegado a la parte más interesante del Database API de Drupal: las consultas dinámicas. Se llaman dinámicas porque Drupal completa dinámicamente la cadena de la consulta. Todas las consultas de inserción, actualización, eliminación o fusión pueden ser dinámicas. Las consultas de selección pueden ser tanto dinámicas como estáticas. No obstante, se recomienda usar consultas dinámicas incluso para las consultas de selección.

Todas las consultas dinámicas se crean como un objeto de consulta, que se invoca cuando sea necesario. Al igual que en las consultas estáticas, la mayoría de las consultas utilizan un envoltorio procedimental (procedural wrapper). Todos los parámetros adicionales pueden añadirse al objeto de consulta.

Consultas de selección con db_select()

Las consultas dinámicas de selección comienzan con la función db_select(), por ejemplo:

<?php
$query = db_select('node', 'n', $options);
?>

En este ejemplo, "node" es la tabla a la que accedemos, lo cual en SQL corresponde a la cláusula FROM. Observa que el nombre de la tabla no se encierra entre llaves como antes. El constructor de consultas lo hará automáticamente. El segundo parámetro es el alias de la tabla. Si no se proporciona, se usará el nombre de la tabla. El array $options es opcional y es idéntico al de las consultas estáticas.

Las consultas dinámicas pueden ser tanto simples como complejas. Estudiaremos los principios básicos de su funcionamiento, aunque podrían explorarse durante mucho tiempo.

Otro ejemplo de db_select()

A continuación, un ejemplo relativamente simple de consulta sobre la tabla de usuarios. Más adelante analizaremos características adicionales de las consultas de selección y ejemplos más complejos con joins (uniones).

<?php
$query = db_select('users', 'u');

$query
  ->condition('u.uid', 0, '<>')
  ->fields('u', array('uid', 'name', 'status', 'created', 'access'))
  ->range(0, 50);

$result = $query->execute();
?>

Este ejemplo es equivalente a:

$result = db_query("SELECT uid, name, status, created, access FROM {users} u WHERE uid <> 0 LIMIT 50 OFFSET 0");

Esta forma simplificada se usa en la página de administración de usuarios y puede ser útil para su estudio posterior.

Joins (Uniones, join)

Para unir con otra tabla se usan los métodos join(), innerJoin(), leftJoin() o rightJoin(), como:

<?php
$table_alias = $query->join('user', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));
?>

En este ejemplo se añade un INNER JOIN (por defecto) con la tabla "user", usando el alias "u". La cláusula ON es "n.uid = u.uid AND u.uid = :uid", donde :uid vale 5. Observa que se usan placeholders para agregar variables de forma segura. Nunca incluyas variables directamente en la cadena de consulta, igual que con las consultas estáticas (riesgo de inyecciones SQL). Los métodos innerJoin(), leftJoin() y rightJoin() funcionan igual para sus respectivos tipos.

El valor devuelto por join es el alias asignado a la tabla. Si ese alias ya estaba en uso (raro), Drupal asignará uno diferente.

Nota: en el primer argumento se usa el nombre de la tabla como "user" para todos los joins.

<?php
$myselect = db_select('mytable')
  ->fields('mytable')
  ->condition('myfield', 'myvalue');
$alias = $query->join($myselect, 'myalias', 'n.nid = myalias.nid');
?>

Campos (Fields, addField)

Para añadir un campo a una consulta usa addField():

<?php
$title_field = $query->addField('n', 'title', 'my_title');
?>

Este código selecciona el campo "title" de la tabla con alias "n" y lo renombra como "my_title". Si no se especifica un alias, se generará automáticamente, normalmente con el nombre del campo. Si ese alias ya existe, se usará el nombre de la tabla y el campo (por ejemplo "n_title"). Si también existe, se añadirá un número como "n_title_2".

Si al crear una consulta no defines un alias y el predeterminado no está disponible, es un error. En hook_query_alter(), si no conoces el alias exacto, usa el predeterminado.

Para seleccionar múltiples campos, llama a addField() varias veces. El orden no suele importar, salvo que afecte la lógica del módulo.

También puedes usar fields() para agregar varios campos a la vez.

<?php
$query->fields('n', array('nid', 'title', 'created', 'uid'));
?>

Este método es equivalente a usar addField() cuatro veces. No permite establecer alias. Retorna el objeto de consulta y se generan los alias automáticamente. Si necesitas alias específicos, usa addField().

Llamar a fields() sin campos es como "SELECT *".

<?php
$query->fields('n');
?>

Esto incluye "n.*" en el SELECT. Nota: no se define alias. Si hay campos duplicados en tablas distintas, puede haber colisiones. Por eso, evitar SELECT * es buena práctica.

DISTINCT

Algunas consultas pueden devolver registros duplicados. Para filtrarlos, se usa "DISTINCT" en SQL. En consultas dinámicas, se emplea el método distinct().

<?php
// Elimina duplicados
$query->distinct()
?>

Recuerda que DISTINCT puede ser costoso, no lo uses innecesariamente.

Expresiones (addExpression)

El constructor de consultas permite usar expresiones como "edad x2", "COUNT de nombres" o substrings. Algunas funciones SQL pueden no ser estándar, depende del soporte del módulo para compatibilidad.

Usa addExpression() para agregar expresiones.

<?php
$count_alias = $query->addExpression('COUNT(uid)', 'uid_count');
$count_alias = $query->addExpression('created - :offset', 'uid_count', array(':offset' => 3600));
?>

La primera línea genera "COUNT(uid) AS uid_count". Si el alias ya existe, se generará uno nuevo. Si no se define, será "expression", "expression_2", etc.

El tercer parámetro es un array asociativo de placeholders usados en la expresión. Algunas expresiones no funcionarán si se usa GROUP BY.

ORDER BY (orderBy)

Para añadir orden a la consulta, usa orderBy():

<?php
$query->orderBy('title', 'DESC');
?>

Esto ordena por el campo title en orden descendente. Puede usarse ASC para ascendente. Por defecto es ASC. Usa alias definidos por addField() o addExpression(). Puedes llamar a orderBy() varias veces para múltiples campos.

Orden aleatorio (orderRandom)

El orden aleatorio varía según la BD, por eso conviene hacerlo dinámicamente.

<?php
$query->orderRandom();
?>

Se puede combinar con orderBy(), como:

<?php
$query->orderBy('term')->orderRandom()->execute();
?>

Primero se ordena por "term", luego aleatoriamente.

GROUP BY (groupBy)

Para agrupar por campo usa groupBy():

<?php
$query->groupBy('uid');
?>

Asegúrate de que el campo esté definido previamente con addField() o addExpression(). Llama varias veces a groupBy() para varios campos.

Rangos y Límites (range)

Para limitar resultados, usa range(), que en MySQL se traduce como LIMIT.

<?php
$query->range(5, 10);
?>

Esto devuelve 10 registros desde el sexto (índice 5). Llamar de nuevo a range() sobrescribe los valores anteriores. Llamarlo sin argumentos no tiene efecto.

Ordenar tabla (extend)

Para ordenar por columnas en una tabla HTML, usa la extensión TableSort:

<?php
$query = $query
  ->extend('TableSort')
  ->orderByHeader($header);
?>

Condiciones (condition)

Las condiciones son un tema complejo que se verá después de SELECT, UPDATE y DELETE. Se explicarán por separado. Las condiciones en SELECT pueden usar WHERE o HAVING. Usa condition() o where() para WHERE, y havingCondition() o having() para HAVING.

Ejecutar la consulta (execute)

Para ejecutar la consulta, usa execute().

<?php
$result = $query->execute();
?>

Devuelve un conjunto de resultados como db_query(), que puede procesarse así:

<?php
$result = $query->execute();
foreach ($result as $record) {
  // Procesar cada $record
}
?>

• fetchField()
• fetchAllKeyed()
• fetchCol()

Estos métodos requieren índices en los alias. El constructor de consultas no siempre lo garantiza.

Contar resultados

Cualquier consulta puede contar filas. Usa countQuery():

<?php
$count_query = $query->countQuery();
?>

$count_query es una nueva consulta sin orden, que al ejecutarse devuelve solo el número de filas:

<?php
$num_rows = $query->countQuery()->execute()->fetchField();
?>

Depuración

Para ver la consulta SQL generada, usa __toString():

<?php
  print_r($query->__toString());
?>