Creo que todos somos conscientes de que PHPRunner gestiona muy bien las búsquedas y yo personalmente, creo que es uno de sus fuertes, pues genera mucho y buen código para resolver las búsquedas.

No obstante , hay algunos casos que se producen en algunos tipos de aplicativos que no son suficiente y no es fácil ampliar las búsquedas que genera el producto.

Objetivo

Ampliar las búsqueda para que también se resuelva;

• Las búsquedas por descripción de campos lookup múltiples.
• Las búsqueda por información de registros detalles.

DEMO: (versión avanzada) https://fhumanes.com/advanced_search/
DEMO: (versión normal)      https://fhumanes.com/video/videoConsulta/

Solución Técnica

Para explicar el problema y la solución voy a utilizar el modelo de datos del tutorial (Video Club).

Como se muestra en la figura, en verde, las relaciones que resuelve PHPRunner y que no tenemos que hacer nada para que nos funcione bien las búsqueda general.

En rojo tenemos las 2 casuísticas que indico y que falta por completar. En este caso:

• Los múltiples intérpretes de las películas se almacenas todas sus claves, con el separador de “,” en un único campo, por ello no se puede buscar por el nombre o parte del nombre de los intérpretes,
• En el modelo de datos, una película puede tener varios temas y se ha normalizado con una tabla intermedia. Por defecto, no se puede hacer búsquedas de películas por algún tema.

En el tutorial describo que ambos casos tienen ventajas e inconvenientes y que podemos pasar de una estructura a otra, dependiendo de nuestras necesidades.

• Pasar de lookup con varios valores a tabla normalizada:

SELECT 
`p`.`idvide_pelicula` AS `idvide_pelicula`,
`i`.`NombreyApellidos` AS `NombreyApellidos_Interprete` 
FROM (`vide_pelicula` `p` 
JOIN `vide_cata_interprete` `i`) 
WHERE (find_in_set(`i`.`idvide_cata_interprete`,`p`.`vide_cata_interprete_idvide_cata_interprete`) > 0)

Pasar de tablas normalizadas a lookup de campo múltiple:

SELECT
`vide_rel_pelicula_tema`.`vide_pelicula_idvide_pelicula` AS `vide_pelicula_idvide_pelicula`,
cast(group_concat(`vide_rel_pelicula_tema`.`vide_cata_tema_idvide_cata_tema` 
      order by `videoclub`.`vide_rel_pelicula_tema`.`vide_cata_tema_idvide_cata_tema` ASC 
      separator ',') as char charset utf8) AS `vide_cata_tema_idvide_cata_tema` 
FROM `vide_rel_pelicula_tema` 
GROUP BY`vide_rel_pelicula_tema`.`vide_pelicula_idvide_pelicula`

Para resolver el problema de la búsqueda lo que he hecho es añadir 2 nuevas columnas virtuales con la información de todos los temas de la película y otro con los nombres de los intérpretes.

Este es el contenido que quiero obtener:

Para poder obtener esta respuesta el SELECT que necesito es:

SELECT
vide_pelicula.idvide_pelicula,
vide_pelicula.CodigoPelicula,
vide_pelicula.Titulo,
vide_pelicula.Sinopsis,
vide_pelicula.FechaCompra,
vide_pelicula.DuracionMinutos,
vide_pelicula.FechaEstreno,
vide_pelicula.vide_cata_estado_peli_idvide_cata_estado_peli,
vide_pelicula.vide_cata_direccion_peli_idvide_cata_direccion_peli,
vide_pelicula.vide_cata_publico_idvide_cata_publico,
vide_pelicula.vide_cata_interprete_idvide_cata_interprete,
vide_pelicula.Ficheros,
gr_tema.temaPelicula,
gr_interprete.NombreyApellidos
FROM vide_pelicula

LEFT JOIN (
SELECT
vide_rel_pelicula_tema.vide_pelicula_idvide_pelicula,
cast( group_concat(temaPelicula
    order by temaPelicula ASC
    separator ' | ') as char charset utf8) AS temaPelicula
FROM vide_rel_pelicula_tema
join vide_cata_tema ON ( vide_rel_pelicula_tema.vide_cata_tema_idvide_cata_tema = idvide_cata_tema )
GROUP BY vide_rel_pelicula_tema.vide_pelicula_idvide_pelicula
) gr_tema ON (vide_pelicula.idvide_pelicula = gr_tema.vide_pelicula_idvide_pelicula )

LEFT JOIN (
SELECT
vide_pelicula.idvide_pelicula vide_pelicula_idvide_pelicula,
cast( group_concat(NombreyApellidos
    order by NombreyApellidos ASC
    separator ' | ') as char charset utf8) AS NombreyApellidos
FROM vide_pelicula
join (
  select p.idvide_pelicula ,i.NombreyApellidos
  from vide_pelicula p
  join vide_cata_interprete i
  where (find_in_set(i.idvide_cata_interprete,p.vide_cata_interprete_idvide_cata_interprete) > 0)
  ) interprete ON ( interprete.idvide_pelicula = vide_pelicula.idvide_pelicula )
  GROUP BY vide_pelicula.idvide_pelicula
) gr_interprete ON ( gr_interprete.vide_pelicula_idvide_pelicula = vide_pelicula.idvide_pelicula )

Una vez que obtengo el query, es sustituir este query en el aplicativo, indicando que estos nuevos campos se utilicen en la búsqueda general y todo funciona como lo necesitaba.

Para grandes volúmenes de información, seguramente no es eficiente, pero para los volúmenes de datos que normalmente se utilizan (hasta 5.000 registros), se puede utilizar sin problema.

En este caso estoy utilizando MySQL y utilizo funciones específicas de este gestor de datos. Para otros gestores habrá que obtener la misma funcionalidad, pero utilizando otras funciones.

Para cualquier duda o necesidad, podéis contactar conmigo a través de mi email [email protected]

Como siempre, os dejo los fuentes del desarrollos para que lo probéis y ajustéis a vuestras necesidades.