Manejando relaciones n:m con mysql y oracle.

Vamos a ver un escenario con el que seguro que todos vosotros os habéis encontrado. Pongamos por ejemplo, que se tiene una base de datos donde hay una tabla que se relaciona con otra de modo que de la segunda hay n y solo contiene dos columnas, la primaria y la de datos. Llamémosle a la primera producto y a la segunda etiquetas. Evidentemente, la relación recíproca también existe ya que es una n:m así que tendremos en medio una tabla que llamaremos etiquetas_productos que modela la n:m.

Vale, ahora hay que mostrar esa información en un formulario. La aproximación clásica y más lenta es la de poner un campo de lista de selección múltiple en el formulario con tooodos los valores posibles de etiquetas. Así lograremos un look ochentero la mar de mono, pero tendremos poca eficiencia.

Vale, una solución mejor es aprovechar la potencia del motor de base de datos en nuestro beneficio, con un toque de jQuery para hacerlos más "mono".

Primero la base de datos.
El escenario ideal, es que en vez de hacer 3 consultas (muy posiblemente pesadas y repetidas) una sobre productos, otra sobre etiquetas y otra sobre etiquetas_producto, sería hacer una única consulta que nos devolviera en un campo nuevo, la relación 1:n entre productos y etiquetas. Hay un modo! la función GROUP_CONCAT() de mysql.

Esta función, lo que hace es pivotarnos un campo que se nos queda fuera de un GROUP BY, separando los distintos valores por el separador que elijamos. Tened en cuenta que siempre es sobre un GROUP BY, no intentéis hacerlo sobre una consulta "normal".

Así, teniendo como consultas origen para el producto xxx las siguientes

SELECT * FROM productos p;
SELECT e.nombre FROM 
    etiquetas_productos ep JOIN 
    etiquetas e USING(idetiqueta) 
WHERE ep.idproducto = xxx;

Usando, GROUP_BY nos quedaría como

SELECT 
    p.*, 
    GROUP_CONCAT(e.nombre) etiquetas 
FROM
    productos p JOIN
    etiquetas_productos ep USING(p.idproducto) JOIN
    etiquetas e USING(ep.idetiqueta) 
GROUP BY
    p.*
WHERE
    p.idproducto = xxx;

Con esto conseguieremos una salida que nos dará todas las columnas de los productos más una nueva columna con las etiquetas del producto xxx separadas por comas (que es el separador por defecto).

Si se quiere cambiar el separador, por ejemplo a '-', habría que hacer lo siguiente:

SELECT 
    GROUP_CONCAT( e.nombre SEPARATOR ‘-’ )

Si se quiere cambiar el orden en vez de ascendente a descendente, hay que poner:

SELECT 
    GROUP_CONCAT( e.nombre ORDER BY e.nombre DESC )

Importante: GROUP_BY ignora los valores NULL.

Oracle no tiene esta funcionalidad de forma nativa, pero siempre hay alguien que se encarga de portar estas funcionalidades. Un ejemplo es la solución dada desde Halis way, que da una solución muy sencilla comparada con otras, que os copio aquí.

with data
 as
 (
  select job,
    ename,
    row_number() over (partition by job order by ename) rn,
    count(*) over (partition by job) cnt
  from emp
 )
 select job, ltrim(sys_connect_by_path(ename,','),',') scbp
  from data
  where rn = cnt
  start with rn = 1
  connect by prior job = job and prior rn = rn-1
  order by job
/

Y el resultado de esta consulta es

JOB       SCBP
--------- ----------------------------------------
ANALYST   FORD,SCOTT
CLERK     ADAMS,JAMES,MILLER,SMITH
MANAGER   BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN  ALLEN,MARTIN,TURNER,WARD

Segundo. La parte jQuery.
Ya tenemos el resultado de la consulta tal y como queríamos, ahora hay que mostrarlo en pantalla. Una muy buena solución y muy sencilla es éste plugin de jQuery que se puede encontrar en la página de XOXCO, y que nos permite convertir un input de tipo texto con texto una lista de valores separados por un separador, en un campo de etiquetas que podremos añadir y borrar a golpe de ratón. Hasta soporta autocompletado mediante AJAX.

El primer paso (aparte de descargarse el paquete) es añadir el plugin a la página

<script src="jquery.tagsinput.js"></script>
<link rel="stylesheet" type="text/css" href="jquery.tagsinput.css" />

Después, localizar el input que contenga las etiquetas

<input id="etiquetas" name="etiquetas" value="foo,bar,baz" />

Se hace la llamada que hace la magia

$('#etiquetas').tagsInput();

Ya está, con ésto convertimos nuestro aburrido input en algo como ésto.

Ahora, si queremos más virguerías solo hay que añadir opciones a la llamada
- Autocompletado con AJAX

$('#etiquetas').tagsInput({autocomplete_url:'http://myserver.com/api/autocomplete'});

- Autocompletado con opciones

$('#etiquetas').tagsInput({
autocomplete_url: 'http://myserver.com/api/autocomplete', 
autocomplete: {selectFirst:true,width:'100px',autoFill:true}
});

- Añadir y eliminar etiquetas programáticamente

$('#etiquetas').addTag('foo');
$('#etiquetas').removeTag('bar');

- Éste es un listado de las opciones que admite el plugin

$(selector).tagsInput({
   'autocomplete_url': url_to_autocomplete_api,
   'autocomplete': { option: value, option: value},
   'height':'100px',
   'width':'300px',
   'unique':true,
   'defaultText':'add a tag'
});

A disfrutar!.