Generador de Consultas (Query Builder)

Si bien podemos acceder a nuestra base de datos a través de la capa básica proporcionada por Yii (DAO), en ocasiones puede ser algo tedioso y por ende susceptible de errores al escribir nuestra consulta directamente. Una alternativa es utilizar el Generador de Consultas.

Un ejemplo sería:

$query = (new \yii\db\Query())
    ->select('id, nombre')
    ->from('usuario')
    ->limit(10);
// Crea un commando
$command = $query->createCommand();
// Ejecuta el commando
$rows = $command->queryAll();

Métodos de Consulta

Como se notará, yii\db\Query es la parte principal que necesitamos. Query es en realidad sólo responsable de representar diversa información de consulta. La lógica real de construcción de consultas se realiza mediante yii\db\QueryBuilder cuando se llama al método createCommand(), y la ejecución de la consulta se la realiza por medio de yii\db\Command.

Para mayor comodidad, yii\db\Query proporciona un conjunto de métodos de consulta de uso común. Por ejemplo:

• all(): construye la consulta, lo ejecuta y devuelve todos los resultados como una matriz.
• one(): devuelve la primera fila del resultado.
• column(): devuelve la primera columna del resultado.
• scalar(): devuelve la primera columna de la primera fila del resultado.
• exists(): devuelve un valor que indica si la consulta devuelve resultados.
• count(): devuelve el resultado de una consulta tipo COUNT. Otros métodos similares sum($q), average($q), max($q), min($q) donde $q es un parámetro obligatorio para estos métodos y puede ser el nombre de la columna o expresión.

Constructor de Consultas (Building Query)

A continuación se verá como construir diferentes cláusulas de una sentencia SQL. Por sencillez, utilizaremos $query para representar el objeto yii\db\Query.

SELECT

Debemos especificar las columnas que queremos seleccionar y la tabla de la cual obtener los datos.

$query->select('id, nombre')
    ->from('usuario');

También lo podemos hacer mediante un arreglo, lo cual es útil cuando se trata de una selección dinámica.

$query->select(['id', 'nombre'])
    ->from('usuario');

Consejo: Es bueno acostumbrarse a utilizar arreglos. Esto se debe a que, expresiones como CONCAT(nombre, apellido) AS nombre_completo pueden contener comas, y el resultado puede ser una cadena separada en partes por comas, y obviamente ese no es el resultado que desearíamos.

Por otro lado, podemos utilizar prefijos de tablas o alias. Una manera sería, por ejemplo

usuario.id AS usuario_id

Y si utilizamos arreglos los podemos hacer algo similares a lo siguiente:

['usuario_id' => 'usuario.id', 'usuario_nombre' => 'usuario.nombre']

A partir de la versión 2.0.1 también se pueden especificar sub-consultas como columnas. Por ejemplo:

$subQuery = (new Query)->select('COUNT(*)')->from('user');
$query = (new Query)->select(['id', 'count' => $subQuery])->from('post');
// $query representa la siguiente consulta SQL:
// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`

Para seleccionar filas distintas, podemos utilizar distinct como en el ejemplo siguiente:

$query->select('user_id')->distinct()->from('post');

FROM

Para especificar de cuál(es) tabla(s) obtener los datos, utilizamos from():

$query->select('*')->from('usuario');

Podemos especificar varias tablas mediante una cadena separada por comas o una matriz. Los nombres de tabla pueden contener prefijos de esquema (por ejemplo, 'public.usuario' ) y/o alias de tabla (por ejemplo, 'usuario u' ). A continuación un ejemplo:

$query->select('u.*, p.*')->from(['usuario u', 'post p']);

Cuando las tablas se especifican como una matriz, también se puede utilizar las claves de matriz como los alias de tabla.

$query->select('u.*, p.*')->from(['u' => 'usuario', 'p' => 'post']);

También es posible especificar una sub-consulta como un objeto Query.

$subQuery = (new Query())->select('id')->from('usuario')->where('estado=1');
$query->select('*')->from(['u' => $subQuery]);

WHERE

Por lo general, los datos se seleccionan cumpliendo ciertos criterios. El Generador de consultas tiene algunos métodos útiles para dichas tareas. El más poderoso de los cuales es WHERE.

La manera más sencilla de utilizarlo es con una cadena.

$query->where('estado=:estado', [':estado' => $estado]);

Cuando se utiliza cadenas, hay que tener presente que se está realizando una consulta con parámetros, no una consulta por concatenación. La manera anterior es la correcta de utilizarlo, la siguiente no lo es:

$query->where("estado=$estado"); // Incorrecto!

También se lo puede hacer a través de params o addParams:

$query->where('estado=:estado');$query->addParams([':estado' => $estado]);

Condiciones múltiples también pueden utilizarse de la siguiente manera:

$query->where([
    'estado' => 10,
    'tipo' => 2,
    'id' => [4, 8, 15, 16, 23, 42],
]);

Éste código es equivalente a

WHERE (`estado` = 10) AND (`tipo` = 2) AND (`id` IN (4, 8, 15, 16, 23, 42))

Cuando necesitamos comparar un campo con el valor NULL lo hacemos de la siguiente manera:

$query->where(['estado' => null]);

con lo que obtenemos

WHERE (`estado` IS NULL)

En cambio, si necesitamos una condición del tipo NOT NULL:

$query->where(['not', ['columna' => null]]);

También es posible utilizar sub-consultas:

$userQuery = (new Query)->select('id')->from('usuario');
$query->where(['id' => $userQuery]);

lo que nos genera el código

WHERE `id` IN (SELECT `id` FROM `usuario`)

Tenemos la posibilidad de utilizar operadores. El formato de uso es [operador, término_1, término_2, ...]. Los operadores que podemos especificar son:

• and
• or
• between
• not between
• in
• not in
• like
• or like
• not like
• or not like
• exists
• not exists
• operador matemáticos de comparación (>, <, >=, <=)

A continuación un ejemplo de cómo sería su uso.

$query->select('id')
    ->from('user')
    ->where(['>=', 'id', 10]);

Lo que equivale a

SELECT id FROM user WHERE id >= 10;

Para construir una condición dinámicamente es conveniente utilizar andWhere() y orWhere():

$stado = 10;$termino_busqueda = 'yii';
$query->where(['estado' => $estado]);
if (!empty($termino_busqueda)) {
    $query->andWhere(['like', 'titulo', $termino_busqueda]);
}

En este caso, termino_busqueda no es vacío, por lo que la consulta generada sería:

WHERE (`estado` = 10) AND (`titulo` LIKE '%yii%')

Constructor de Condiciones Filtro

Cuando construimos condiciones para filtrar nuestros datos, basados en lo que el usuario ingresa, queremos manejar adecuadamente los campos vacíos. Por ejemplo, un usuario puede llenar un formulario en el que no todos los campos son obligatorios, por lo que los campos vacíos no queremos que los tome en cuenta. Para lograr este objetivo podemos utilizar filterWhere().

// $username y $email son ingresados por un usuario
$query->filterWhere([
    'username' => $username,
    'email' => $email,
]);

El método filterWhere() es similar a Where(). La diferencia principal es que filterWhere() remueve los valores vacíos. Se considera campos vacíos a aquellos que tienen el valor null, una cadena vacía, una cadena de espacios en blanco, o un arreglo vacío.

Se puede utilizar andFilterWhere() y orFilterWhere() para añadir más condiciones.

ORDER BY

Para ordenar los datos se puede utilizar orderBy o addOrderBy:

$query->orderBy([
    'id' => SORT_ASC,
    'nombre' => SORT_DESC,
]);

GROUP BY y HAVING

Para añadir una cláusula GROUP BY a nuestra sentencia:

$query->groupBy('id, estado');

Si queremos añadir otro campo para ser agrupado:

$query->addGroupBy(['creado_el', 'actualizado_el']);

Para añadir una condición HAVING:

$query->having(['estado' => $estado]);

LIMIT y OFFSET

Para limitar el resultado a 10 filas:

$query->limit(10);

Para saltar 100 filas:

$query->offset(100);

JOIN

Para la cláusula JOIN tenemos los siguiente métodos:

• innerJoin()
• leftJoin()
• rightJoin()

Por ejemplo:

$query->select(['usuario.nombre AS autor', 'post.titulo as titulo'])
    ->from('usuario')
    ->leftJoin('post', 'post.usuario_id = usuario.id');

Si nuestra base de datos no soporta alguno de los tipos de JOIN, podemos utilizar el método genérico join:

$query->join('FULL OUTER JOIN', 'post', 'post.usuario_id = usuario.id');

De la misma manera que FROM, podemos utilizar sub-consultas. Por ejemplo:

$query->leftJoin(['u' => $subQuery], 'u.id=autor_id');

UNION

En Yii primero debemos construir la primera consulta, luego construir la segunda, y por último unir los resultados. Por ejemplo:

$query = new Query();$query->select("id, categoria_id as tipo, nombre")->from('post')->limit(10);
$otroQuery = new Query();
$otroQuery->select('id, tipo, nombre')->from('usuario')->limit(10);
$query->union($otroQuery);

Consulta por lotes 

Cuando se trabaja con grandes cantidades de datos, métodos como yii\db\Query::all() no son adecuados, ya que requieren la carga de todos los datos en la memoria. Para mantener el requisito de memoria baja, Yii ofrece apoyo a la consulta por lotes. Una consulta por lotes hace uso de cursores de datos y recupera los datos en lotes.

Un ejemplo es el siguiente:

use yii\db\Query;
$query = (new Query())
    ->from('usuario')
    ->orderBy('id');

foreach ($query->batch() as $usuarios) {
    // $usuarios es un arreglo de 100 o menos filas de la tabla usuario
}
// o si se desea iterar fila por fila
foreach ($query->each() as $usuario) {
    // $usuario representa una fila de datos de la tabla usuario
}

El método yii\db\Query::batch() y yii\db\Query::each() retornan un objeto yii\db\BatchQueryResult que puede ser implementado por la interfaz Iterator y por lo tanto se puede utilizar con foreach. Durante la primera iteración, se realiza una consulta SQL a la base de datos. Los datos se captan por lotes en las iteraciones restantes. Por defecto, el tamaño del lote es de 100, lo que significa que 100 filas de datos están siendo extraídas en cada lote. Se puede cambiar el tamaño de lote pasándolo como primer parámetro a los métodos batch() o each().

En comparación con  yii\db\Query::all(), la consulta por lotes sólo carga 100 filas de datos a la vez en la memoria. Si procesamos los datos y luego los descartamos de inmediato, la consulta por lotes puede ayudar a reducir el uso de memoria.



También te puede interesar:
Trabajando con bases de datos
DAO - Database Access Objects (Objetos de acceso a base de datos)
Active Record (Registro Activo)