En 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>Uso de DataTables</title> <!-- El CSS de DataTables --> <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jszip-2.5.0/pdfmake-0.1.18/dt-1.10.12/af-2.1.2/b-1.2.2/b-colvis-1.2.2/b-flash-1.2.2/b-html5-1.2.2/b-print-1.2.2/cr-1.3.2/fc-3.2.2/fh-3.1.2/kt-2.1.3/r-2.1.0/rr-1.1.2/sc-1.4.2/se-1.2.0/datatables.min.css"/> <!-- El CSS de Bootstrap --> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <!--<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css">--> </head> <body> <div class="container"> <div class="row"> <div class="col-sm-12"> <table id="tabla_de_personal" class="table display table-striped table-bordered"> <thead> <tr> <th>NOMBRE</th> <th>APELLIDO</th> <th>CARGO</th> <th>CIUDAD</th> <th>INGRESO</th> <th>SALARIO</th> </tr> </thead> <tbody> </tbody> </table> </div> </div> </div> <!-- jQuery --> <script language="javascript" src="https://code.jquery.com/jquery-3.1.1.min.js"></script> <!-- El JavaScript de DataTables --> <script language="javascript" type="text/javascript" src="https://cdn.datatables.net/v/dt/jszip-2.5.0/pdfmake-0.1.18/dt-1.10.12/af-2.1.2/b-1.2.2/b-colvis-1.2.2/b-flash-1.2.2/b-html5-1.2.2/b-print-1.2.2/cr-1.3.2/fc-3.2.2/fh-3.1.2/kt-2.1.3/r-2.1.0/rr-1.1.2/sc-1.4.2/se-1.2.0/datatables.min.js"></script> <!-- El JavaScript de BootStrap --> <script language="javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <!-- El código JavaScript --> <script language="javascript"> var objetoDataTables_personal = $('#tabla_de_personal').DataTable({ "language": { "emptyTable": "No hay datos disponibles en la tabla.", "info": "Del _START_ al _END_ de _TOTAL_ ", "infoEmpty": "Mostrando 0 registros de un total de 0.", "infoFiltered": "(filtrados de un total de _MAX_ registros)", "infoPostFix": "(actualizados)", "lengthMenu": "Mostrar _MENU_ registros", "loadingRecords": "Cargando...", "processing": "Procesando...", "search": "Buscar:", "searchPlaceholder": "Dato para buscar", "zeroRecords": "No se han encontrado coincidencias.", "paginate": { "first": "Primera", "last": "Última", "next": "Siguiente", "previous": "Anterior" }, "aria": { "sortAscending": "Ordenación ascendente", "sortDescending": "Ordenación descendente" } }, "lengthMenu": [[5, 10, 20, 25, 50, -1], [5, 10, 20, 25, 50, "Todos"]], "iDisplayLength": 10, "bServerSide": true, "sAjaxSource": "datos_externos_06.php", "columns" : [ {"data": 0}, {"data": 1}, {"data": 2}, {"data": 3}, {"data": 4}, {"data": 5} ], }); $('label').addClass('form-inline'); $('select, input[type="search"]').addClass('form-control input-sm'); </script> </body> </html> |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 |
<?php // Establecemos la codificacion para las llamadas y respuestas HTTP mb_internal_encoding ('UTF-8'); /* CREAMOS LA CONEXION A LA BASE DE DATOS, O BIEN LA IMPORTAMOS DESDE UN ARCHIVO EXTERNO DE CONFIGURACION. */ $conexion = new PDO('mysql:host=localhost;dbname=datatables;charset=UTF8', 'root', ''); $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); /* RECUPERAMOS TODOS LOS PARAMETROS DE $_GET. LOS QUE NO APAREZCAN EN LA CONSULTA RECIBIRAN UN VALOR PREDETERMINADO. ESTOS DATOS SON LOS QUE SE RECIBEN CADA VEZ QUE EL PLUGIN DATATABLES LLAMA A ESTE SCRIPT. */ $datosDeLlamada = $_GET; /* SE INDICA(N) LA(S) TABLA(S) QUE SE VA(N) A USAR EN LA CONSULTA. */ $tablasDeBBDD = array( 'personal', 'ciudades', 'cargos' ); /* SE DEFINE LA LISTA DE COLUMNAS QUE SE DEVOLVERON PARA SER MOSTRADAS EN LA TABLA HTML. LOS NOMBRES DE ESTAS COLUMNAS DEBEN COINCIDIR CON LOS DE LAS COLUMNAS DE LA(S) TABLA(S) AFECTADA(S) POR LA CONSULTA. */ $columnasParaRetorno = array( $tablasDeBBDD[0].'.nombre', $tablasDeBBDD[0].'.apellido', $tablasDeBBDD[2].'.cargo', $tablasDeBBDD[1].'.ciudad', $tablasDeBBDD[0].'.fecha_de_ingreso', $tablasDeBBDD[0].'.salario_bruto_anual' ); $numeroDeColumnas = count($columnasParaRetorno); //////////////////////////////////////////////// REGLAS DE FILTRADO //////////////////////////// /* PREPARAMOS EL FILTRADO POR COLUMNAS PARA LA CAJA DE BUSQUEDA */ $reglasDeFiltradoDeUsuario = array (); if (isset($datosDeLlamada['sSearch']) && $datosDeLlamada['sSearch'] !== "") { for($i = 0; $i < $numeroDeColumnas; $i++) { if (isset ($datosDeLlamada['bSearchable_'.$i]) && $datosDeLlamada['bSearchable_'.$i] == 'true') { $reglasDeFiltradoDeUsuario[] = $columnasParaRetorno[$i]." LIKE '%".addslashes($datosDeLlamada['sSearch'])."%'"; } } } if (!empty($reglasDeFiltradoDeUsuario)){ $reglasDeFiltradoDeUsuario = ' ('.implode(" OR ", $reglasDeFiltradoDeUsuario).') '; } else { $reglasDeFiltradoDeUsuario = ''; } /* PREPARAMOS LAS REGLAS DE FILTRADO DE RELACIONES ENTRE TABLAS. ESTAS SE PROGRAMAN AQUI A MANO, PORQUE PUEDEN EXISTIR O NO, DEPENDIENDO DE QUE SE USE UNA TABLA O MAS DE UNA. */ $reglasDeFiltradoDeRelaciones = ''; $reglasDeFiltradoDeRelaciones .= " (".$tablasDeBBDD[1].".id = ".$tablasDeBBDD[0].".id_ciudad "; $reglasDeFiltradoDeRelaciones .= "AND ".$tablasDeBBDD[2].".id = ".$tablasDeBBDD[0].".id_cargo) "; /* SE COMPONE TODA LA REGLA DE FILTRADO. EN ESTE CASO INCLUYE LAS CLAÚSULAS DE BÚSQUEDA, Y LAS RELACIONES ENTRE TABLAS. SIGUE SIENDO UN EJEMPLO SIMPLE, PERO MÁS ELABORADO QUE EL ANTERIOR. MÁS ADELANTE VEREMOS OTROS USOS. LO IMPORTANTE AQUI ES QUE, ADEMÁS DE LAS CLAUSULAS DE BÚSQUEDA (VARIABLE $reglasDeFiltradoDeUsuario, QUE PUEDEN EXISTIR O NO) TAMBIÉN HAY UNA CLAÚSULA DE RELACIONES ENTRE LAS TABLAS. SI HAY MÁS DE UNA TABLA SIEMPRE HABRÁ UNA CLAÚSULA DE RELACIONES ($reglasDeFiltradoDeRelaciones). LAS IMPLEMENTAMOS COMO UNA MATRIZ PARA PODER COMPROBAR LAS QUE EXISTEN Y LAS QUE NO, Y LUEGO LAS UNIMOS CON EL OPERADOR AND, SI HAY MÁS DE UNA CLAÚSULA DE FILTRADO. */ $reglasDeFiltrado = array(); if ($reglasDeFiltradoDeUsuario > '') $reglasDeFiltrado[] = $reglasDeFiltradoDeUsuario; if ($reglasDeFiltradoDeRelaciones > '') $reglasDeFiltrado[] = $reglasDeFiltradoDeRelaciones; $reglasDeFiltrado = implode(" AND ", $reglasDeFiltrado); //////////////////////////////////////////// FIN DE REGLAS DE FILTRADO /////////////////////////// /////////////////////////// REGLAS DE ORDENACION //////////////////////// /* SE COMPONE LA REGLA DE ORDENACION. */ $reglasDeOrdenacion = array (); if (isset($datosDeLlamada['iSortCol_0'] )) { $columnasDeOrdenacion = intval($datosDeLlamada['iSortingCols']); for($i = 0; $i < $columnasDeOrdenacion; $i ++) { if ($datosDeLlamada['bSortable_'.intval($datosDeLlamada['iSortCol_'.$i])] == 'true') { $reglasDeOrdenacion [] = $columnasParaRetorno[intval($datosDeLlamada['iSortCol_'.$i])].($datosDeLlamada['sSortDir_'.$i] === 'asc'?' asc':' desc'); } } } if (!empty($reglasDeOrdenacion)) { $reglasDeOrdenacion = " ORDER BY ".implode(", ", $reglasDeOrdenacion); } else { $reglasDeOrdenacion = ""; } /* SE COMPONE LA REGLA DE LIMITACION DE REGISTROS. */ $reglaDeLimitacion = ($datosDeLlamada['iDisplayLength'] > 0)?' LIMIT '.$datosDeLlamada['iDisplayStart'].', '.$datosDeLlamada['iDisplayLength'].';':';'; /////////////////////////////////////// FIN DE REGLAS DE ORDENACION //////////////////// /* SE PREPARA LA CONSULTA DE RECUPERACION DEL DATASET SOLICITADO. */ $consulta = "SELECT ".implode(', ', $columnasParaRetorno)." "; $consulta .= "FROM ".implode(', ', $tablasDeBBDD)." "; $consulta .= "WHERE 1 "; if ($reglasDeFiltrado > "") $consulta .= "AND (".$reglasDeFiltrado.") "; $consulta .= $reglasDeOrdenacion; $consulta .= $reglaDeLimitacion; $hacerConsulta = $conexion->prepare($consulta); $hacerConsulta->execute(); $DataSet = $hacerConsulta->fetchAll(PDO::FETCH_ASSOC); $hacerConsulta->closeCursor(); /* SI ES NECESARIO ADAPTAR ALGUN DATO PARA PRESENTACION, SE ADAPTA AQUI. SI ES NECESARIOS AGREGAR ENLACES, REFERENCIAS A IMAGENES, O CUALQUIER OTRA COSA, SE INCLUYE EN ESTA SECCION. EN ESTE CASO, LO ÚNICO QUE VAMOS A HACER ES DARLE FORMATO AL SALARIO ANUAL. */ foreach ($DataSet as $keyDL=>$DL){ $DataSet[$keyDL]['fecha_de_ingreso'] = date("d-m-Y", strtotime($DL['fecha_de_ingreso'])); $DataSet[$keyDL]['salario_bruto_anual'] = number_format($DL['salario_bruto_anual'], 2, ",", ".").' €'; } /* CALCULO DE DATOS INFORMATIVOS DE REGISTROS. */ $numeroDeRegistrosDelDataSet = count($DataSet); /* CALCULO DEL TOTAL DE REGISTROS QUE CUMPLEN LAS REGLAS DE FILTRADO SIN ORDENACION NI LIMITACION. */ $consulta = "SELECT COUNT(".$columnasParaRetorno[0].") "; $consulta .= "FROM ".implode(', ', $tablasDeBBDD)." "; $consulta .= "WHERE 1 "; if ($reglasDeFiltrado > "") $consulta .= "AND (".$reglasDeFiltrado.") "; $hacerConsulta = $conexion->prepare($consulta); $hacerConsulta->execute(); $totalDeRegistrosConFiltrado = $hacerConsulta->fetch(PDO::FETCH_NUM)[0]; $hacerConsulta->closeCursor(); /* TOTAL DE REGISTROS DE LA TABLA PRIMARIA (O UNICA, SI SOLO HAY UNA). */ $consulta = "SELECT COUNT(".$columnasParaRetorno[0].") "; $consulta .= "FROM ".$tablasDeBBDD[0].";"; $hacerConsulta = $conexion->prepare($consulta); $hacerConsulta->execute(); $totalDeRegistrosEnBruto = $hacerConsulta->fetch(PDO::FETCH_NUM)[0]; $hacerConsulta->closeCursor(); // SE PREPARA UNA MATRIZ CON TODOS LOS DATOS NECESARIOS PARA LA SALIDA. $matrizDeSalida = array( "sEcho" => intval($datosDeLlamada['sEcho']), "iTotalRecords" => strval($totalDeRegistrosEnBruto), "iTotalDisplayRecords" => strval($totalDeRegistrosConFiltrado), "aaData" => array () ); foreach ($DataSet as $DL){ $registro = array(); foreach ($DL as $dato) $registro[] = $dato; $matrizDeSalida['aaData'][] = $registro; unset($registro); } $salidaDeDataSet = json_encode ($matrizDeSalida, JSON_HEX_QUOT); /* SE DEVUELVE LA SALIDA */ echo $salidaDeDataSet; ?> |
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.
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.
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.
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.