Using the union methods in database queries/es

Joomla! 

≥ 3.3

Esta página cubre Joomla! 3.3 o posterior.

El uso de UNION en una consulta de base de datos es una forma útil para combinar los resultados de dos o más consultas SELECT a la base de datos, que no están necesariamente vinculadas por una relación de la base de datos. También puede ser útil en la optimización del rendimiento. El uso de un UNION para combinar los resultados de diferentes consultas a veces puede ser mucho más rápido que en una sola consulta con una cláusula WHERE sobre todo cuando la consulta implica unir otras tablas grandes.

Para aquellos poco familiarizados con la teoría de un UNION, hace exactamente lo que se espera; fusiona el conjunto de resultados de una consulta con el conjunto de los resultados de otra consulta para producir un conjunto de resultados, que es el conjunto de la unión de los dos conjuntos de resultados individuales. Si desea que el conjunto se ordene entonces tendrás que poner especial atención a la forma en que se realiza, como se explica más adelante.

Los fundamentos

Con el fin de utilizar un UNION, necesitas estar consciente de los requisitos básicos establecidos por el servidor SQL que estás utilizando. Estas no son impuestas por Joomla!, pero si no cumples con ellas obtendrás errores de la base de datos. En particular, cada consulta SELECT debe devolver el mismo número de campos en el mismo orden y con los tipos de datos compatibles para que el UNION sea exitoso.

Un ejemplo sencillo

Supongamos que deseas enviar una circular a un grupo de personas, pero la base de datos es tal que los nombres y direcciones de correo electrónico que desea enviar a no todos están en la misma tablaa. Por inventar una ejemplo arbitrario, supongamos que deseas enviar el correo a todos los clientes y todos los proveedores y que los nombres y direcciones de correo electrónico se almacenan en tablas llamadas, como era de esperar, customers y suppliers, respectivamente.

Esta consulta va a recuperar toda la información del cliente que se necesitan para la circular:

$query
    ->select('name, email')
    ->from('customers')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

mientras que esta consulta hace lo mismo para todos los proveedores:

$query
    ->select('name, email')
    ->from('suppliers')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Entonces puedes combinar los resultados en una sola consulta como esta:

$query
    ->select('name, email')
    ->from('customers')
    ->union($q2->select('name , email')->from('suppliers'))
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

El conjunto de resultados obtenidos a partir de la consulta de unión, realmente será un poco diferente de la gestión de las consultas individuales por separado porque la consulta de unión, automáticamente elimina los duplicados. Si no te importa que el conjunto de resultados pueda contener duplicados (que matemáticamente significa que no es un conjunto), utilizando unionAll en lugar de unión mejorará el rendimiento.

Muchas maneras para uso de union

Los métodos union (y unionAll), son bastantes flexibles en lo acepta como argumentos. Se puede pasar de un "viejo estilo" de cadena de consulta, a un objeto JDatabaseQuery, o un array de objetos JDatabaseQuery. Por ejemplo, supón que tienes tres tablas, de forma similar al ejemplo anterior:

$q1->select('name, email')->from('customers');
$q2->select('name, email')->from('suppliers');
$q3->select('name, email')->from('shareholders');

Entonces todas estas consultas producen el mismo resultado:

// The union method can be chained.
$q1->union($q2)->union($q3);

// The union method will accept string queries.
$q1->union($q2)->union('SELECT name, email FROM shareholders');

// The union method will accept an array of JDatabaseQuery objects.
$q1->union(array($q2, $q3));

// It doesn't matter which query object is at the "root" of the query.  In this case the actual query that is produced will be different but the result set will be the same.
$q2->union(array($q1, $q3));

union, unionAll y unionDistinct

En realidad, hay tres métodos union disponibles.

• union produce un verdadero conjunto de la unión de los conjuntos de resultados individuales; es decir, los duplicados se eliminan. El proceso de eliminación de duplicados puede o no puede incurrir en un impacto en el rendimiento, dependiendo de los conjuntos de datos y las estructuras de la base de datos involucradas.

• unionAll produce una unión de los conjuntos de resultados individuales, pero no se quitan los duplicados.

• unionDistinct es idéntico en el comportamiento de la unión y es simplemente un alias para el método unión.

Usar UNION en lugar de OR

Existen algunos casos en donde a través del uso de union puede darse un impulso significativo en el rendimiento en lugar de la alternativa más comúnmente utilizada de un OR o IN en la cláusula where.

Por ejemplo, supón que tienes una tabla de productos y deseas extraer sólo aquellos productos que pertenecen a dos categorías particulares. Normalmente este sería el código para algo como esto:

$query
    ->select('*')
    ->from('products')
    ->where('category = ' . $db->q('catA'), 'or')
    ->where('category = ' . $db->q('catB'))
    ;
$products = $db->setQuery($query)->loadObjectList();

Sin embargo, es probable que vas a ver útil aumentar el rendimiento mediante el uso de union en su lugar:

$query
    ->select('*')
    ->from('products')
    ->where('category = ' . $db->q('catA'))
    ;
$q2
    ->select('*')
    ->from('products')
    ->where('category = ' . $db->q('catB'))
    ;
$query->union($q2);
$products = $db->setQuery($query)->loadObjectList();

Por supuesto, si deseas seleccionar los productos de más de dos categorías, se puede combinar más consultas individuales juntas.

Debes ver un aumento de rendimiento similar a la hora de sustituir una cláusula where que contiene una declaración IN.

Ordenar los resultados

Si deseas que tus resultados se ordenen entonces necesitas estar consciente que ofrece la base de datos con las lcausulas ORDER BY. Los siguientes comentarios se aplican a MySQL pero, probablemente, se aplican a otras bases de datos.

Supongamos que deseas la salida de los nombres y direcciones de correo electrónico en orden alfabético en la circular del ejemplo anterior. Entonces sólo tienes que hacer esto:

$q2
    ->select('name , email')
    ->from('suppliers')
    ;
$query
    ->select('name, email')
    ->from('customers')
    ->union($q2)
    ->order('name')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Pero supongamos que, por alguna razón, querías la salida de los nombres y direcciones de correo electrónico en orden alfabético con todos los clientes en primer lugar, a continuación, todos los proveedores. Esta consulta no va a dar el resultado esperado:

$q2
    ->select('name , email')
    ->from('suppliers')
    ->order('name')
    ;
$query
    ->select('name, email')
    ->from('customers')
    ->order('name')
    ->union($q2)
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Esto es debido a que un cláusula ORDER BY en las declaraciones SELECT individuales no implica nada sobre el orden en que aparecen las filas en el resultado final. Un UNION produce un conjunto desordenado de filas. La consulta anterior es sintácticamente correcta, pero el optimizador de MySQL simplemente ignora la cláusula ORDER BY del SELECT proveedores y la cláusula ORDER BY del SELECT de clientes y la declaración ORDER BY se aplica al conjunto final de resultados en lugar de cada conjunto de resultados.

La forma de evitar esto es agregar una columna adicional al conjunto de resultados y ordenarla, en tal manera que los resultados se ordenen de la forma deseada. Aquí hay una manera de hacerlo:

$q2
    ->select('name , email, 1 as sort_col')
    ->from('suppliers')
    ;
$query
    ->select('name, email, 2 as sort_col')
    ->from('customers')
    ->union($q2)
    ->order('sort_col, name')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Ordenamiento Avanzado

Sin embargo, puede haber ocasiones en las que es importante tener una cláusula ORDER BY en las consultas individuales que no se pueden quitar de la consulta por el optimizador de conversiones. Supón que deseas enviar una oferta especial para tu clientes top 10 y proveedores top 5. Por lo tanto, será conveniente aplicar una cláusula LIMIT en combinación con la cláusula ORDER BY y en este caso que el optimizador de la consulta no ignore el ordenamiento. Esta es la forma en que podrías hacerlo:

$q2
    ->select('name , email, 1 as sort_col')
    ->from('suppliers')
    ->order('turnover DESC')
    ->setLimit(5)
    ;
$q1
    ->select('name, email, 2 as sort_col')
    ->from('customers')
    ->order('turnover DESC')
    ->setLimit(10)
    ;
$query
    ->select('name, email, 0 as sort_col')
    ->from('customers')
    ->where('1 = 0')
    ->union($q1)
    ->union($q2)
    ->order('sort_col, name')
    ;
$mailshot = $db->setQuery($query)->loadObjectList();

Es necesario el uso de una consulta ficticia, en este caso, porque de lo contrario los métodos order y setLimit serán aplicados al conjunto de resultados finales, en lugar de los individuales.

Si estás experimentando los errores que reproducen este ejemplo, chequea el tema y parche relacionado: 4127