Consulta para ranking

[rubiomike]

Hola a todos, llevo ya unos meses programando una web de cine en mi tiempo libre y tengo un problemilla con una consulta. Hace unos meses un usuario de este foro me ayudó con el siguiente codigo:

SELECT peliculas.titulo, peliculas.year, votos.id_pelicula, AVG( votos.nota ) AS promedio
FROM votos
INNER JOIN peliculas ON peliculas.id = votos.id_pelicula
GROUP BY id_pelicula
ORDER BY promedio DESC

En esencia el código funciona perfectamente, con el lo que consigo es que se me muestren los registros de la tabla películas y votos ordenados según la media de tal forma que si solo se han votado, por ejemplo, 10 películas, la consulta devuelve 10 registros.

El problema me surge porque quiero que la consulta muestre tanto los registros que tienen votos como que no. Me explico, lo que busco es que si tengo los registros de 1000 películas, pero en la tabla votos solo hay votos para 10 de ellas, quiero que la consulta me muestre las 1000 películas ordenadas primero por la media, y las que no tengan votos, aparezcan con puntuación 0 y ordenadas en orden alfabético.

¿Es posible realizar algo así?

Gracias a todos y perdón por la parrafada

[lucianomdq]

Hola!
Si, es posible.

Yo hace un tiempo resolvi un problema parecido usando los condicionales.
O sea, basicamente lo que deberias lograr es que cuando la consulta pase por votos.nota, si se encuentra con un numero haga algo y sino haga alguna otra cosa, de manera que siempre te muestre algo, se entiende?

La sentencia seria algo asi:

SELECT IF('aca pones la condicion', AVG( votos.nota ), 0) AS promedio

Traducido seria: si la condicion se cumple hace el promedio, sino le asigna cero.

Tendrias que ver cual es la condicion. A lo mejor podes hacer que sea IF('NULL',0,AVG( votos.nota )) que seria: si el campo esta vacio ponele cero y sino (si tiene un numero) que haga el promedio.

Con eso deberia andar. Ojo! investigalo un poco y trata de adaptarlo a lo que necesitas.

Despues solo deberias modificar el order by para que sea primero por promedio y luego por nombre de pelicula. eso lo podes hacer simplemente poniendo:

ORDER BY promedio DESC, peliculas.titulo ASC

Espero te sirva. Saludos...

Lucianomdq

[jurena]

Prueba esto. Observa que necesitas LEFT JOIN. Asimismo, el problema de 0 lo resuelves con IFNULL.

Código MySQL:

SELECT
   peliculas.titulo, 
   peliculas.year, 
   votos.id_pelicula, 
   IFNULL(AVG( votos.nota ),0) AS promedio
FROM peliculas
   LEFT JOIN votos
     ON peliculas.id = votos.id_pelicula
   GROUP BY peliculas.id 
  ORDER BY promedio DESC
 

[rubiomike]

Muchas gracias a los dos por tomaros la molestia de responder, la verdad es que era exactamente lo que estaba buscando. El código de jurena funciona correctamente, lo único es que tarda mucho tiempo en realizar la consulta, unos 3 segundos. Actualmente tengo unos 15.000 registros en la tabla películas. ¿Es normal que tarde ese tiempo? ¿Alguna otra forma para que sea más rápido?

Muchas gracias de nuevo a los dos.

[jurena]

Doy por sentado que tienes indexado votos.id_pelicula, porque eso hará más rápido el proceso. De todas formas, creo que ahora te encuentras con un problema de paginación. Intenta probar sacando solamente los 10 primeros con un LIMIT a ver si ganas algo

Código MySQL:

SELECT
   peliculas.titulo, 
   peliculas.year, 
   votos.id_pelicula, 
   IFNULL(AVG( votos.nota ),0) AS promedio
FROM peliculas
   LEFT JOIN votos
     ON peliculas.id = votos.id_pelicula
   GROUP BY peliculas.id 
  ORDER BY promedio DESC LIMIT 10
 

[rubiomike]

La verdad es que se me había pasado indexar votos.id_pelicula, estaba tan centrado en como hacer la consulta que no me di cuenta, jejeje.

Ahora va todo correctamente 15.000 resultados en 0.0003 segundos.

Gracias.

PD: bonita ciudad la de Cáceres!!

[jurena]

Cierto, muy cierto.
Me alegro de que solucionaras el problema.

saludos