El plugin DataTables (VI). Combinar datos de varias tablas.

Facebooktwittergoogle_pluslinkedinmailFacebooktwittergoogle_pluslinkedinmail

datatablesEn el artículo anterior aprendimos a usar el punto más fuerte del plugin DataTables, que es poder renderizar una tabla con los datos que necesitemos obtenidos de una fuente externa. En este artículo vamos a hacer algo muy similar, pero obteniendo los datos a partir de más de una tabla. Este es el escenario más habitual que vamos a encontrar, y es necesario saber cómo hacerlo. Verás que los cambios son mínimos, y se pueden implementar sin ningún esfuerzo en nada de tiempo. Sólo hay que rediseñar la consulta adecuada para el dataset.

Si entendiste bien el artículo anterior, ahora estás a otro nivel. Ahora juegas en las ligas mayores, y tienes que seguir ascendiendo. Déjame mostrarte el camino.

EL ESCENARIO

En esta ocasión contamos con los mismos datos (por lo que al usuario se refiere) que en el artículo anterior. Sin embargo, en la base de datos los tenemos montados según un esquema que es más acorde con la forma en que se trabaja en el mundo real. Como se da la circunstancia de que cada miembro de la plantilla está en una ciudad, pero en cada ciudad hay varios miembros (en total hay 57 miembros pero sólo 7 ciudades), esto nos sugiere dos tablas: la de personal y una tabla de ciudades. En la tabla de ciudades hay dos columnas: el id, autonumérico, que actúa cómo clave primaria y el nombre de la ciudad. En la tabla de personal se ha sustituido el campo que tenía el nombre de la ciudad, por un campo numérico, en el que se ha incluido el id de cada ciudad. De este modo, se puede establecer una relación entre ambas tablas, del tipo n a 1, es decir, un número indeterminado de registros de la tabla principal (la de personal) se relacionan con un registro de la tabla secundaria (ciudades).

Además, hemos hecho lo mismo con los cargos. Aunque en este caso no hay tanta repetición (son 33 cargos para los 57 miembros de la plantilla), hemos establecido también un id para los cargos en una tabla aparte, y la hemos relacionado con la de personal.

El SQL para estas tablas lo tienes en este enlace, para que puedas recrearlas en tu equipo y seguir este artículo.

EL CÓDIGO

Cómo ya es habitual, existen dos scripts en este ejercicio: el scrript primario, que es invocado en el navegador, que renderiza la tabla y que monta el plugin DataTables y el el script secundario, que recupera los datos cada vez que es invocado y se los devuelve al plugin por Ajax.

EL SCRIPT PRIMARIO

El script primario no tiene nada de partícular. Practicamente, es una copia del que vimos en el artículo anterior, en el que lo único que camboa es el nombre del script secundario al que el plugin llama por Ajax. El listado te lo reproduzco de todos modos, para que lo tengas a mano para copiarlo y pegarlo en tu ordenador. Lo he llamado artículo_06.php:

Cómo ves, ninguna novedad aquí.

EL SCRIPT SECUNDARIO

El script secundario es harina de otro costal. Como ahora tenemos más de una tabla, también hay que tener en cuenta las relaciones entre las tablas que hay, la procedencia de cada dato y la estructura final de la consulta que recupera el dataset. Te repoduzco aquí el código, llamado datos_externos_06.php:

Lo primero, cópialo en tu carpeta localhost/datatables y carga en tu navegador localhost/datatables/articulo_06.php para ver que funciona correctamente. A continuación vamos a comentar las diferencias con el script secundario del artículo anterior.

La primera diferencia está en la declaración de los nombres de las tablas. En el script del artículo anterior se usaba sólo una, y aquí tenemos tres tablas en juego:

$tablasDeBBDD = array(
    'personal',
    'ciudades',
    'cargos'
);

Y, lógicamente, también se redefinen los orígenes de las columnas, asi:

$columnasParaRetorno = array(
    $tablasDeBBDD[0].'.nombre',
    $tablasDeBBDD[0].'.apellido',
    $tablasDeBBDD[2].'.cargo',
    $tablasDeBBDD[1].'.ciudad',
    $tablasDeBBDD[0].'.fecha_de_ingreso',
    $tablasDeBBDD[0].'.salario_bruto_anual'
);

Evidentemente, los datos cargo y ciudad no podemos obtenerlos de la tabla personal, ya que esta sólo contiene identificadores numéricos, y no es eso lo que queremos mostrarle al usuario. Por lo tanto, tenemos que obtener estos datos de las tablas que contienen los literales. Esto, en realidad, no es nada nuevo. Es el mismo modo de trabajo que usaríamos para recuperar los datos, aunque no estuviéramos usando el plugin DataTables.

La siguiente diferencia que notamos está en establecer las relaciones entre las tablas, para que los datos de los miembros de la plantilla incluyan los literales de ciudad y cargo. Lo hacemos así:

$reglasDeFiltradoDeRelaciones = '';
$reglasDeFiltradoDeRelaciones .= " (".$tablasDeBBDD[1].".id = ".$tablasDeBBDD[0].".id_ciudad ";
$reglasDeFiltradoDeRelaciones .= "AND ".$tablasDeBBDD[2].".id = ".$tablasDeBBDD[0].".id_cargo) ";

Al componer las reglas de filtrado, hay que tener en cuenta que puede haber claúsulas de búsqueda, o no haberlas y que puede haber (y, de hecho, hay) reglas de relaciones. La regla total de filtrado la componemos así:

$reglasDeFiltrado = array();
if ($reglasDeFiltradoDeUsuario > '') $reglasDeFiltrado[] = $reglasDeFiltradoDeUsuario;
if ($reglasDeFiltradoDeRelaciones > '') $reglasDeFiltrado[] = $reglasDeFiltradoDeRelaciones;
$reglasDeFiltrado = implode(" AND ", $reglasDeFiltrado);

Y ya está. El resto del código no cambia. Como ves, las relaciones entre tablas apenas añaden un poco más de trabajo al script de selección de datos. En el próximo artículo veremos nuevas funcionalidades que podemos añadirle a nuestro plugin para mejorar los resultados y la experiencia del usuario en nuestra página.

Todo el código de este artículo (scripts y base de datos) lo tienes en este enlace.

     

3 comentarios:

  1. Hola, te agradezco enormemente tus tutoriales sobre datatables, son, de lejos, lo mejor en español.
    Pero me ha surgido una duda en este tema.
    A ver, yo soy nuevo en programación web, estoy haciendo mi primera página php ahora.
    Tengo una bbdd que cruza varias tablas y para la mayoría de mis consultas necesito hacer joins, ya que si lo hago como tú indicas con un select de varias tablas, va lentíiiisimo.

    Así que me he apañado como he podido, modificando tu código que recopila los datos por todos los lados, haciendo seguramente chapuzas, pero al final funciona, con joins, va rápido y perfecto, pero claro, no ha quedado el código tan bien como lo tenías tú.

    Claro, con joins, necesito poner alias a las columnas, ya que coincidia que tenia el mismo nombre en una tabla que en otra, y tal y como tienes tu ejemplo, en según qué sitios, los alias destrozaban la consulta.
    Solución: he hecho una variable con alias que uso donde necesito los alias y he copiado-pegado y hecho el array de columnas sin alias para cuando necesitaba que fuera sin alias.

    Luego seguramente podría haber adatpado tu ejemplo en la parte de condiciones, metiendo ahi los join, pero no fui capaz.
    Metí en un “chorizo” todo lo después de que coge la primera tabla, e hice todos los joins con todas las secundarias y los “on” en un array y lo pongo después del select tabla1 …

    Es un poco chapucero pero va.
    La cuestión es: No podrías hacer un ejemplo para dejarlo así de bonito como lo dejas tú para los join? jjeje

    Aunque claro, tendrías que hacer para left, para inner, etc, no? sería un lío, no?
    Como novato… qué me recomiendas?
    Gracias.

  2. En una cosa tienes razón. Efectivamente, cuando se emplean varias tablas con muchos campos, relacionados con claves foráneas, etc, hacer consultas secuencialmente, o consultas que dependan de otras mediante bucles, puede ralentizar enormemente el resultado. Cuando escribí el código, perseguía más la claridad didáctica que un resultado optimo, y es cierto que, en muchos casos, haces las consultas con joins y van como un tiro.
    En la actualidad, tengo en agenda preparar algunos ejemplos usando joins aunque, desafortunadamente, tengo tantas cosas en agenda, que necesitaría días de 48 horas, por lo que no te puedo dar una fecha para publicarlo.
    No obstante, como guía, es muy sencillo preparar el script “secundario” (el que hace las lecturas de la base de datos), si tratas de abstraerte del problema. Me explico. No consideres las consultas como un todo con datatables. Tú haz tus consultas en un script como si las fueras a sacar en un simple volcado de pantalla. Céntrate en obtener el resultado que deseas. Una vez que tengas los resultados en una matriz, sólo tienes que convertirla en un JSON y devovérsela a DT. Eso es, a la postre, lo que hace el script secundario.

    Quizá te puedan ayudar estos artículos: este, este, este otro y, por último, este.

  3. Sí bueno, si ya te digo que al final lo conseguí adaptando tu ejemplo de 1 tabla para mi ejemplo con joins y varias tablas.
    Tuve que modificar muchas cosas pero al final va perfecto.
    Seguramente quedó muy chapuza, pero el caso es que funciona.
    Como dije, soy novato en programación web, estoy aprendiendo, así que que funcione ya es un logro.

    Ya entiendo que estarás ocupado, si un dia cuelgas eso para hacerlo con joins, sin duda lo leeré y lo implementaré, porque seguro que está mejor que lo mio, jeje.

    Un saludo y de nuevo gracias.

Deja un comentario