Listar registros con atributos en columnas

[aovalle]

Hola a todos, resulta que tengo unas tablas con los siguientes campos:

- libros
id_libro,titulolibro
1,Odisea
2,Soledad

- atributos
id_atributo,nombreatributo
1,Precio
2,Fecha
3,Volumen
4,Empaste

- atributoslibros
id_libro,id_atributo,valor_atributo
1,1,10000
1,2,1980
1,3,1
1,4,Rustico
2,1,0
2,2,1945
2,3,NULL
2,4,Tapa dura

Los atributos son unos pocos (precio, fecha, volumen, empaste, etc.) cada uno con su id y previamente creados en la tabla atributos.

Lo que deseo obtener en la consulta es una tabla en la que cada libro sea una fila con sus atributos como columnas, ejemplo:

id_libro,titulo,precio,fecha,volumen,empaste
1,Odisea,10000,1980,1,Rustico
2,Soledad,0,1945,NULL,Tapa dura

¿Es esto posible?, he probado con INNER JOIN, LEFT JOIN pero el problema es que me duplica los resultados.

De antemano gracias por sus aportes.

[quimfv]

Tan variables son los atributos de la ficha de un libro para no ponerlos como campos de la tabla libros.... creo que te estas complicando la vida....

Código MySQL:

SELECT Sbc.id_libro,Sbc.titulolibro,
             MAX(Sbc.APrecio) Precio,
             MAX(Sbc.AFecha) Fecha,
             ...
FROM
(SELECT l.id_libro,l.titulolibro,
             if(a.nombreatributo="Precio",al.valor_atributo,0) APrecio,
             if(a.nombreatributo="Fecha",al.valor_atributo,0) AFecha,
             ...
FROM (libros l INNER JOIN atributoslibros al ON l.id_libro=al.id_libro)
                      INNER JOIN atributos a ON a.id_atributo=al.id_atributo) Sbc
GROUP BY Sbc.id_libro,Sbc.titulolibro;
 

Esto creo que te lo dará.... no se si usando GROUP_CONCAT se puede hacer (no me gusta).

Si tienes atributos con valor negativo twendrás que cambiar algo...

Pero en cualquier caso si pretendias hacer un sistema en que la lista de atributos fuera dinamica, en el momento que quieres hacer esto deja de serlo ya que la debes conocer ... con programacion externa si se podria construir un TRANSFORM dinamico..... pero el tema es que el diseño para la ficha de un libro no debe ser este....

[leonardo_josue]

Hola aovalle:

Complementando el comentario de quimfv, el utilizar funciones de agrupación (MAX, MIN, SUM) es una manera "tramposa" de hacerlo, ya que de entrada puedo suponer que el campo valor_atributo es de tipo VARCHAR, ya que almacenas tanto valores como cadenas y valores nulos... de tal forma que el resultado puede ser un tanto impredecible... hay otra forma de obtener el mismo resultado utilizando LEFT JOIN's sobre la tabla de atributoslibros, utilizando alias, de esta manera:

Código MySQL:

mysql> SELECT * FROM libros;
+----------+-------------+
| id_libro | titulolibro |
+----------+-------------+
|        1 | Odisea      |
|        2 | Soledad     |
+----------+-------------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM atributoslibros;
+----------+-------------+----------------+
| id_libro | id_atributo | valor_atributo |
+----------+-------------+----------------+
|        1 |           1 | 10000          |
|        1 |           2 | 1980           |
|        1 |           3 | 1              |
|        1 |           4 | Rustico        |
|        2 |           1 | 0              |
|        2 |           2 | 1945           |
|        2 |           3 | NULL           |
|        2 |           4 | Tapa dura      |
+----------+-------------+----------------+
8 rows in set (0.01 sec)
 
mysql> SELECT
    ->   l.id_libro,
    ->   l.titulolibro,
    ->   alPrecio.valor_atributo precio,
    ->   alFecha.valor_atributo fecha,
    ->   alVolumen.valor_atributo volumen,
    ->   alEmpaste.valor_atributo empaste
    -> FROM libros l
    -> LEFT JOIN atributoslibros alPrecio
    ->    ON l.id_libro = alPrecio.id_libro AND alPrecio.id_atributo = 1
    -> LEFT JOIN atributoslibros alFecha
    ->    ON l.id_libro = alFecha.id_libro AND alFecha.id_atributo = 2
    -> LEFT JOIN atributoslibros alVolumen
    ->    ON l.id_libro = alVolumen.id_libro AND alVolumen.id_atributo = 3
    -> LEFT JOIN atributoslibros alEmpaste
    ->    ON l.id_libro = alEmpaste.id_libro AND alEmpaste.id_atributo = 4;
+----------+-------------+--------+-------+---------+-----------+
| id_libro | titulolibro | precio | fecha | volumen | empaste   |
+----------+-------------+--------+-------+---------+-----------+
|        1 | Odisea      | 10000  | 1980  | 1       | Rustico   |
|        2 | Soledad     | 0      | 1945  | NULL    | Tapa dura |
+----------+-------------+--------+-------+---------+-----------+
2 rows in set (0.00 sec)
 

Tal como te menciona quimfv en su post, el problema es que la asignación de los alias para cada columna lo estoy haciendo de manera arbitraria, es decir sin tomar en cuenta el contenido del catálogo de atributos. Finalmente, puedes simplificar la consulta de quimfv sin necesidad de utilizar subconsultas, asi:

Código MySQL:

mysql> SELECT
    ->   l.id_libro,
    ->   l.titulolibro,
    ->   MAX(IF(a.id_atributo = 1, a.valor_atributo, NULL)) precio,
    ->   MAX(IF(a.id_atributo = 2, a.valor_atributo, NULL)) fecha,
    ->   MAX(IF(a.id_atributo = 3, a.valor_atributo, NULL)) volumen,
    ->   MAX(IF(a.id_atributo = 4, a.valor_atributo, NULL)) empaste
    -> FROM libros l INNER JOIN atributoslibros a ON l.id_libro = a.id_libro
    -> GROUP BY l.id_libro, l.titulolibro;
+----------+-------------+--------+-------+---------+-----------+
| id_libro | titulolibro | precio | fecha | volumen | empaste   |
+----------+-------------+--------+-------+---------+-----------+
|        1 | Odisea      | 10000  | 1980  | 1       | Rustico   |
|        2 | Soledad     | 0      | 1945  | NULL    | Tapa dura |
+----------+-------------+--------+-------+---------+-----------+
2 rows in set (0.00 sec)
 

Repitiendo, que el uso de la función MAX sobre un campo tipo varchar puede traer problemas.

Finalmente, podrías optar por cambiar el modelo de BD, después de todo no creo que los atributos de un libro sean muchos aparte de estos... podrías cambiar a un modelo con sólo dos tablas:

Código MySQL:

mysql> SELECT * FROM libros;
+----------+-------------+
| id_libro | titulolibro |
+----------+-------------+
|        1 | Odisea      |
|        2 | Soledad     |
+----------+-------------+
2 rows in set (0.00 sec)
 
mysql> select * from atributoslibros;
+----------+--------+-------+---------+-----------+
| id_libro | precio | fecha | volumen | empaste   |
+----------+--------+-------+---------+-----------+
|        1 |  10000 |  1980 |       1 | Rustico   |
|        2 |      0 |  1945 |    NULL | Tapa dura |
+----------+--------+-------+---------+-----------+
2 rows in set (0.07 sec)
 

Podrías agregar una columna de comentarios para agregar atributos adicionales... podrías definir tus campos precio, fecha y volumen de tipo numérico, lo que te permitiría obtener consultas por ejemplo de rangos de precios o de años en específicos (cosa que con tu modelo no lo podrías hacer).

Saludos
Leo.

[aovalle]

Hola,

Me ha servido de mucho la ayuda de sus respuestas, he logrado sacar la consulta sin ningun dificultad.

Realmente el planteamiento de la base creo que está bien, lo que pasa es que el sistema (es un sistema bibliotecario) tiene unos atributos definidos, y la posibilidad de crear atributos adicionales es una opción cuando la biblioteca tiene mas información para registrar que no está cubierta por los atributos principales.

Efectivamente los valores de los atributos se almacenan en un campo varchar, por lo que me queda la inquietud de que tipo de problemas podrían surgir al usar la función MAX sobre dicho campo.

Muchas gracias de nuevo!

[quimfv]

Pues no habia caido pero tiene razon leonardo_josue, intenta ordenar estos valores guardados en un varchar

"1"
"1000000"
"2"

El maximo si es varchar seria 2!!!!

Luego la solución con join es la correcta.