Consulta con resultas limitados

[PibeJava]

Hola, que tal?

Tengo un pequeño problema que estuve buscando por todos lados y no encuentro una solucion como la que yo quiero pero acudo a ustedes que tienen mucho mas conociemiento en el tema y por ahi conocen alguna forma.

Tengo dos tablas, albumes y fotos, con las siguientes columnas (pongo menos para no hacerlo muy largo)

albumes:
id_album
titulo
descripcion

fotos:
id_foto
id_album
titulo
descripcion

Lo que quiero hacer es traer los los albumes y solo una cantidad de fotos por cada uno, ej. 10.
Entonces tendria que traer solamente 10 fotos por cada album.

Mi solucion que no era la que queria fue traer los albumes y por cada album hacer una consulta y traer las 10 fotos pero no me gusta mucho la idea de hacer tantas llamas si bien solo traigo 5 albumes por pagina para acortar las consultas.

Hay alguna forma de poder hacer esto en una sola consulta?

Espero se haya entendido mi duda y desde ya muchas gracias.

[gnzsoloyo]

Hay alguna forma de poder hacer esto en una sola consulta?

Tal y como lo has planteado, no. Deberás hacerlo de esa forma.

[PibeJava]

Me imaginaba gnzsoloyo, queria sacarme la duda con alguien que sepa bien del tema.

Te agradezco la respuesta.

[leonardo_josue]

Hola PibeJava:

Hace tiempo discutía con algunos compañeros acerca de un problema similar, les propuse una solución que igual te puede servir. supongamos que tenemos la siguiente información:

Código MySQL:

mysql> SELECT * FROM albumes;
+----------+------------+-------------+
| id_album | titulo     | descripcion |
+----------+------------+-------------+
|        1 | Album Uno  | Desc Uno    |
|        2 | Album Dos  | Desc Dos    |
|        3 | Album Tres | Desc Tres   |
+----------+------------+-------------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM fotos;
+---------+----------+---------+----------------+
| id_foto | id_album | titulo  | descripcion    |
+---------+----------+---------+----------------+
|       1 |        1 | Foto 1  | Foto 1-Album 1 |
|       2 |        1 | Foto 2  | Foto 2-Album 1 |
|       3 |        1 | Foto 3  | Foto 3-Album 1 |
|       4 |        2 | Foto 4  | Foto 1-Album 2 |
|       5 |        2 | Foto 5  | Foto 2-Album 2 |
|       6 |        2 | Foto 6  | Foto 3-Album 2 |
|       7 |        3 | Foto 7  | Foto 1-Album 3 |
|       8 |        3 | Foto 8  | Foto 2-Album 3 |
|       9 |        3 | Foto 9  | Foto 3-Album 3 |
|      10 |        3 | Foto 10 | Foto 4-Album 3 |
+---------+----------+---------+----------------+
10 rows in set (0.01 sec)
 

Supongamos ahora que queremos mostrar sólo las 2 primeras fotos para cada album. La idea es simular la función ROW_NUMBER de Oracle o SQL Server para numerar cada registro... hay muchas formas de hacerlo, pero para este ejemplo lo hacemos con un COUNT (más información en la liga):

http://www.artfulsoftware.com/infotree/queries.php#1098

de esta forma quedaría una consulta así:

Código MySQL:

mysql> SELECT F1.id_foto, F1.id_album, COUNT(*) AS row_number
    ->    FROM fotos F1
    ->    INNER JOIN
    ->      fotos F2 ON F1.id_album = F2.id_album AND
    ->      F1.id_foto >= F2.id_foto
    ->    GROUP BY F1.id_album, F1.id_foto;
+---------+----------+------------+
| id_foto | id_album | row_number |
+---------+----------+------------+
|       1 |        1 |          1 |
|       2 |        1 |          2 |
|       3 |        1 |          3 |
|       4 |        2 |          1 |
|       5 |        2 |          2 |
|       6 |        2 |          3 |
|       7 |        3 |          1 |
|       8 |        3 |          2 |
|       9 |        3 |          3 |
|      10 |        3 |          4 |
+---------+----------+------------+
10 rows in set (0.03 sec)
 

Con esta consulta, simplemente filtramos los registros que tengan un row_number > 2... al utilizar una función COUNT(*) lo podemos hacer con una sentencia HAVING COUNT(*) <= 2. El resto sería simplemente hacer los JOIN's, incluyendo esta nueva tabla para filtrar la información, es decir, algo como esto:

Código MySQL:

mysql> SELECT
    -> A.id_album, A.titulo, A.descripcion,
    -> F.id_foto, F.titulo, F.descripcion
    -> FROM albumes A
    -> INNER JOIN fotos F ON A.id_album = F.id_album
    -> INNER JOIN
    ->   (SELECT F1.id_foto, F1.id_album, COUNT(*) AS row_number
    ->    FROM fotos F1
    ->    INNER JOIN
    ->      fotos F2 ON F1.id_album = F2.id_album AND
    ->      F1.id_foto >= F2.id_foto
    ->    GROUP BY F1.id_album, F1.id_foto
    ->    HAVING COUNT(*) <= 2) T
    ->  ON T.id_foto = F.id_foto AND T.id_album = F.id_album;
+----------+------------+-------------+---------+--------+----------------+
| id_album | titulo     | descripcion | id_foto | titulo | descripcion    |
+----------+------------+-------------+---------+--------+----------------+
|        1 | Album Uno  | Desc Uno    |       1 | Foto 1 | Foto 1-Album 1 |
|        1 | Album Uno  | Desc Uno    |       2 | Foto 2 | Foto 2-Album 1 |
|        2 | Album Dos  | Desc Dos    |       4 | Foto 4 | Foto 1-Album 2 |
|        2 | Album Dos  | Desc Dos    |       5 | Foto 5 | Foto 2-Album 2 |
|        3 | Album Tres | Desc Tres   |       7 | Foto 7 | Foto 1-Album 3 |
|        3 | Album Tres | Desc Tres   |       8 | Foto 8 | Foto 2-Album 3 |
+----------+------------+-------------+---------+--------+----------------+
6 rows in set (0.06 sec)
 

Observa que sólo te trae dos fotos por cada album, que creo que es lo que pretendes...

Sale un vistazo, creo que te puede servir. Si tienes problemas para entender alguna parte de la consulta me la comentas y lo vemos.

Saludos
Leo.

[gnzsoloyo]

El problema es que habría que iterar los identificadores de los álbumes para poder obtener los de las fotos, y como eso implicaría crear dinamicamente un conjunto de subconsultas unidas con UNION, la cosa se volvería una locura... si es que funciona.
Existe una posibilidad, pero es bastante complicada: Usar Stored procedures, con tablas de tipo TEMPORARY y cursores sobre los álbumes.
Podría funcionar, pero es una codificación algo rebuscada para quien no tenga práctica en SP.
(Je, je... este Leo, anda siempre un pasito cerca mío :D)

[leonardo_josue]

Parecemos caballos en la recta final, con Foto Finish... lo bueno es que corremos hacia el mismo lado. Malo sería que fuéramos como dos trenes sobre la misma vía, pero yendo uno en contra del otro.

Saludos
Leo.

[PibeJava]

leonardo_josue! La verdad que no tengo palabras para agradecerte la ayuda. Lo probe y anda perfecto. Tendria que enviarte un buen vino o alfajores como agradecimiento por el tiempo que te tomaste en mostrarme como es. Aparte me hiciste dar cuenta cuenta lo lejos que estoy de ese tipo de cosas, ni siquiera entiendo el 2º INNER JOIN. Pero ya lo estoy estudiando.

Otra vez gracias a vos y a gnzsoloyo por la atencion.

[leonardo_josue]

La verdad es que no me caería nada mal una buena botella de vino o unos deliciosos Alfajores, pero como estoy en mi México Lindo y Querido te acompañarte con unos buenos tequilas y unos dulces de leche...

La consulta en realidad no es tan compleja, es solo cuestión de tomar algo de la experiencia con Oracle y SQL Server y aplicarla a MySQL. Como te comenté, el truco esta en simular la función ROW_NUMBER para aplicarla a MySQL... hay muchas maneras de hacerlo... la liga que te puse muestra dos de ellas que desde mi punto de vista son de las más sencillas (la página tiene muchísimos ejemplos de estas y otras consultas avanzadas que estoy seguro que te podrían servir en un futuro, deberías tomarte un tiempo largo en estudiar su contenido).

Para entender un poco mejor la consulta, lo que estás haciendo sería una especie de "producto cartesiano" entre la misma tabla... utilizas un INNER JOIN sobre la misma tabla, por naturaleza, el INNER JOIN sirve para asociar uno a uno dos registros, sin embargo observa que en la cláusula ON se utiliza una condición >=, por lo que en realidad estarías uniendo de uno a muchos... si ejecutas esta consulta, sin el COUNT(*) ni el GROUP BY te darás cuenta de cómo se hace esta unión:

Código MySQL:

mysql> SELECT
    -> F1.id_album id_album_f1, F1.id_foto id_foto_f1,
    -> F2.id_album id_album_f2, F2.id_foto id_foto_f2
    -> FROM fotos F1
    -> INNER JOIN
    ->   fotos F2 ON F1.id_album = F2.id_album AND
    ->   F1.id_foto >= F2.id_foto
    -> ORDER BY F1.id_album, F1.id_foto;
+-------------+------------+-------------+------------+
| id_album_f1 | id_foto_f1 | id_album_f2 | id_foto_f2 |
+-------------+------------+-------------+------------+
|           1 |          1 |           1 |          1 |
|           1 |          2 |           1 |          1 |
|           1 |          2 |           1 |          2 |
|           1 |          3 |           1 |          1 |
|           1 |          3 |           1 |          2 |
|           1 |          3 |           1 |          3 |
|           2 |          4 |           2 |          4 |
|           2 |          5 |           2 |          4 |
|           2 |          5 |           2 |          5 |
|           2 |          6 |           2 |          6 |
|           2 |          6 |           2 |          4 |
|           2 |          6 |           2 |          5 |
|           3 |          7 |           3 |          7 |
|           3 |          8 |           3 |          7 |
|           3 |          8 |           3 |          8 |
|           3 |          9 |           3 |          7 |
|           3 |          9 |           3 |          8 |
|           3 |          9 |           3 |          9 |
|           3 |         10 |           3 |          7 |
|           3 |         10 |           3 |          8 |
|           3 |         10 |           3 |          9 |
|           3 |         10 |           3 |         10 |
+-------------+------------+-------------+------------+
22 rows in set (0.14 sec)
 

Observa que el número de registros de la tabla F2 con que se relaciona cada registro de la tabla F1 es justamente la posición que le corresponde, utilizando entonces la función COUNT(*) y el GROUP BY obtienes el resultado deseado. Espero que esto te ayude a entender un poco mejor la consulta.

Saludos y Salud!
Leo.

[PibeJava]

Podemos intercambiar cultura gastronomica leonardo_josue porque de bases de datos tengo que sentarme a leer y aprender mucho todavia para poder intercambiar algo con ustedes. Ahora me quedo mas claro el INNER JOIN, de esa forma no lo habia implementado nunca.

Gracias por el tiempo.