Mejora tus sentencias SQL para búsquedas con PHP

En este artículo vamos a abordar una revisión rápida a la forma en que se realizan las búsquedas de datos en bases de datos.

¿Quién no se ha sentido frustrado al usar el buscador en una página o aplicación y descubrir que el sistema no te entrega la información que esperas? A mi me pasa y más seguido de lo que esperaría. Desde buscadores que no hacen búsqueda separada por cada palabra ingresada, hasta aquellos que arrojan en los primeros lugares aquellos resultados menos relevantes. Esto usualmente se debe a pobres implementaciones de los querys de búsqueda. Un query es una sentencia o instrucción dada al motor de base de datos para que realice una acción, en nuestro caso particular, una búsqueda de datos. Los querys deben seguir una sintaxis predeterminada por un conjunto de reglas denominadas SQL, que es el lenguaje usado tradicionalmente para realizar consultas en muchas bases de datos relacionales como MySQL, PostgreSQL, Microsoft SQL Server, Oracle, etc.

Para ilustrar nuestro caso, usaremos como ejemplo una base de datos con nombres de contactos en donde queremos encontrar las referencias asociadas a “Gabriel García Márquez”. Una búsqueda característica (ignorando por simplicidad mayúsculas y tildes) sería de la forma:

SELECT ID, NOMBRE
FROM contactos 
WHERE NOMBRE = 'gabriel garcia marquez'

Esta búsqueda solamente retornaría resultados si en alguno de los registros contenidos en la tabla “contactos” es exactamente igual al nombre dado, lo que en la práctica puede no ser el caso. Imagina que en lugar de buscar un nombre se buscaran por ejemplo, referencias al premio Nobel y buscara “premio nobel literatura 1982”. Difícilmente un registro tendría esa secuencia exacta cómo valor. ¿Cómo lo corregimos? Una primer aproximación sería buscar esa cadena como contenida en alguno de los registros, lo que nos llevaría a una sentencia como la siguiente:

SELECT ID, NOMBRE
FROM contactos 
WHERE NOMBRE LIKE '%gabriel garcia marquez%'

Aunque es algo mejor, tampoco  tendremos resultado alguno a menos que uno o varios de los registros contengan un valor similar a “xxx gabriel garcia marquez xxx”. Lo que corresponde entonces es realizar una búsqueda de los registros que contengan al menos una de las palabras ingresadas, es decir, que contenga “gabriel”, “garcia” o “marquez”. Lo que nos lleva a una sentencia como esta:

SELECT ID, NOMBRE
FROM contactos 
WHERE NOMBRE LIKE '%gabriel%' OR 
	NOMBRE LIKE '%garcia%' OR 
	NOMBRE LIKE '%marquez%'

Y es posible que ahora sí tengamos algún resultado, una serie de registros como los siguientes (este es un listado de muestra, el resultado dependerá por supuesto del contenido exacto de la tabla “contactos” en nuestra base de datos):

| ID | NOMBRE                      | 
| -- | --------------------------- | 
| 22 | Blaise Márquez              | 
| 37 | Francisco Gabriel Márquez   | 
| 51 | Dantonio Márquez Saavedra   | 
| 77 | Vicente García              | 
| 79 | Gabriela Diaz               | 
| 83 | Gabriel José García Márquez | 
| 85 | Ana Gabriel                 | 

Mucho mejor, ¿verdad?

Ahora bien, muchos quizás se contenten con este resultado, tabulen los datos en pantalla y se den por bien servidos, pero si observan con atención notaran que este resultado está dejando en una de las últimas posiciones la que sería la repuesta deseada, que es “Gabriel José García Márquez”. ¿Quizás si lo ordenamos por el nombre mejoraría? Veamos:

SELECT ID, NOMBRE
FROM contactos 
WHERE NOMBRE LIKE '%gabriel%' OR 
	NOMBRE LIKE '%garcia%' OR 
	NOMBRE LIKE '%marquez%'
ORDER BY NOMBRE

Con el modificador ORDER BY la respuesta obtenida sería algo como:

| ID | NOMBRE                      | 
| -- | --------------------------- | 
| 85 | Ana Gabriel                 | 
| 22 | Blaise Márquez              | 
| 51 | Dantonio Márquez Saavedra   | 
| 37 | Francisco Gabriel Márquez   | 
| 83 | Gabriel José García Márquez | 
| 79 | Gabriela Diaz               | 
| 77 | Vicente García              | 

Un poco mejor pero el resultado que creemos es más relevante todavía se encuentra lejos. 

¿Y porqué insistir en mejorarlo si ya funciona?, se preguntarán algunos. Imagina que en lugar de unos pocos registros como los de este ejemplo tuviéramos cientos o miles, sería todo un martirio si el resultado más relevante estuviera en las últimas posiciones, ¿verdad? Traducido a una aplicación web, tendríamos posiblemente que navegar varias páginas antes de encontrar lo que realmente queremos y como usuarios, posiblemente terminemos abandonando la búsqueda antes que eso pase. Es nuestro deber como programadores el prevenir este escenario y facilitar las cosas al usuario.

Llegados a este punto, lo que necesitamos es asignar a cada resultado un peso apropiado de acuerdo a la cantidad de coincidencias encontradas, de forma que los registros que contengan el mayor número de coincidencias aparezcan de primero. ¿Fácil? Por lo pronto digamos que puede hacerse y para ello, incluiremos columnas adicionales, una por cada palabra, de forma que tome el valor "1" si la palabra se encuentra en los datos del registro y "0" si no existe. Es decir, algo como:

SELECT 
	CASE WHEN (NOMBRE LIKE '%gabriel%') THEN 1 ELSE 0 END AS W1,
	CASE WHEN (NOMBRE LIKE '%garcia%') THEN 1 ELSE 0 END AS W2,
	CASE WHEN (NOMBRE LIKE '%marquez%') THEN 1 ELSE 0 END AS W3,
	ID,
	NOMBRE
FROM 
	contactos
WHERE 
	NOMBRE LIKE '%gabriel%' OR 
	NOMBRE LIKE '%garcia%' OR 
	NOMBRE LIKE '%marquez%'
ORDER BY (W1+W2+W3) DESC, NOMBRE

¡Y Voilá! Tenemos un resultado mucho mejor:

| W1 | W2 | W3 | ID | NOMBRE                      | 
| -- | -- | -- | -- | --------------------------- | 
|  1 |  1 |  1 | 83 | Gabriel José García Márquez | 
|  1 |  0 |  1 | 37 | Francisco Gabriel Márquez   | 
|  1 |  0 |  0 | 85 | Ana Gabriel                 | 
|  0 |  0 |  1 | 22 | Blaise Márquez              | 
|  0 |  0 |  1 | 51 | Dantonio Márquez Saavedra   | 
|  1 |  0 |  0 | 79 | Gabriela Diaz               | 
|  0 |  1 |  0 | 77 | Vicente García              | 

Ahora sí, los registros más relevantes se encuentran entre los primeros de la lista, tal como lo esperaríamos de un sistema de búsqueda decente.

Queda anotar que aunque la condición en el ORDER BY funciona perfectamente para MySQL (o su equivalente Open Source llamado MariaDB), es posible que en otros motores de bases de datos no funcione de la misma forma. Es por esto que sugiero reorganizar el SQL para generar una salida mejor estructurada y con mejor viabilidad de funcionar en otros motores de búsqueda, aprovechando la capacidad que tiene SQL de buscar datos dentro del resultado de otro query, algo como esto:

SELECT
	(W1+W2+W3) AS WT, ID, NOMBRE
FROM (
	SELECT 
		CASE WHEN (NOMBRE LIKE '%gabriel%') THEN 1 ELSE 0 END AS W1,
		CASE WHEN (NOMBRE LIKE '%garcia%') THEN 1 ELSE 0 END AS W2,
		CASE WHEN (NOMBRE LIKE '%marquez%') THEN 1 ELSE 0 END AS W3,
		ID,
		NOMBRE
	FROM 
		contactos
	WHERE 
		NOMBRE LIKE '%gabriel%' OR 
		NOMBRE LIKE '%garcia%' OR 
		NOMBRE LIKE '%marquez%'
	) AS Q
ORDER BY WT DESC, NOMBRE

Y adicionalmente, con esta alternativa obtenemos un resultado donde podemos directamente visualizar cuántas ocurrencias de las palabras buscadas existen dentro de cada registro, tabuladas bajo la columna “WT”:

| WT | ID | NOMBRE                      | 
| -- | -- | --------------------------- | 
|  3 | 83 | Gabriel José García Márquez | 
|  2 | 37 | Francisco Gabriel Márquez   | 
|  1 | 85 | Ana Gabriel                 | 
|  1 | 22 | Blaise Márquez              | 
|  1 | 51 | Dantonio Márquez Saavedra   | 
|  1 | 79 | Gabriela Diaz               | 
|  1 | 77 | Vicente García              | 

Por supuesto, estos son querys que deberán construirse usando algún tipo de código de programación ya que cambian dinámicamente. Dependiendo de cuántas palabras se quieran ingresar al motor de búsqueda, así mismo crecerá el tamaño de la sentencia SQL. Para facilitar este trabajo, queda a consideración el siguiente código escrito en PHP que cumple precisamente con este requerimiento:

function sql_search(string $table, string $column, string $search) {

	// Recupera las palabras a buscar
    $usearch = str_replace(['á', 'é', 'í', 'ó', 'ú'], ['a', 'e', 'i', 'o', 'u'], strtolower($search));
    $words = explode(' ', $usearch);
	// Almacena sentencias LIKE
	$likes = array();
	// Almacena sentencias CASE
	$cases = array();

	foreach ($words as $k => $w) {
		$w = trim($w);
		if ($w != '') {
			$name = 'W' . ($k + 1);
			$likes[$name] = "{$column} LIKE '%{$w}%'";
			$cases[$name] = "\t\tCASE WHEN ({$likes[$name]}) THEN 1 ELSE 0 END AS {$name}";
		}
	}

	// Construye SQL
	$sql = "SELECT
	(" . implode('+', array_keys($cases)) . ") as WT,
	{$column}
FROM (
	SELECT
		" . trim(implode(',' . PHP_EOL, $cases)). ",
	{$column}
	FROM
		{$table}
	WHERE
		" . implode(' OR ', $likes) . "
	) AS Q
ORDER BY WT DESC, {$column}";

	return $sql;
}

Como resultado, esta función genera la sentencia SQL que luego habremos de enviar al motor de base de datos para obtener con ella el resultado deseado. Si quisiéramos obtener el query equivalente al usado en el ejemplo anterior, tenemos que invocar esta función de la siguiente manera:

$query = sql_search('contactos', 'NOMBRE', 'Gabriel García Márquez');

Por supuesto, antes de poder incluir esta función en un sistema en producción es necesario realizarle algunas mejoras. Si bien la instrucción LIKE en la mayoría de los motores de base de datos ignora automáticamente si se trata de mayúsculas y minúsculas o con tildes (por eso, a modo de estandarización sugiero buscar siempre en minúsculas y en lo posible sin tildes) y recupera por igual un registro que contenga “marques” a uno que contenga “Márquez”, existen otras consideraciones a tener en cuenta, tales como:

• ¿Qué pasa si las búsquedas deben realizarse en varias columnas y no solamente en una?
• ¿Qué pasa si una de esas columnas es una referencia numérica a un listado de opciones en otra tabla o (peor aún) a valores asociados a constantes dentro de la aplicación?
• ¿Cómo adicionamos otras columnas a los datos de respuesta?
• ¿Qué pasa si se debe buscar información en columnas con valores no texto (tipos numéricos o fecha) o si alguna de las columnas de búsqueda es de valor null?
• ¿Qué pasa con el manejo de caracteres especiales como “ñ” o diéresis “ü”?
• ¿Pueden asignarse pesos diferentes en caso que efectivamente encuentre la palabra completa o si las encuentra en el orden ingresado, de forma que estos resultados aparezcan en los primeros lugares?

Todas estas son consideraciones a evaluar y como no podía ser de otra forma, te las dejo como tarea querido lector. Te invito a que compartas tus propuestas al respecto en los comentarios para beneficio de todos.

Y no siendo más por el momento, nos vemos próximamente con otro tema de interés.

Imagen de fondo original de Gerd Altmann a través de Pixabay.