Consulta LEFT OUTER JOIN con 4 tablas

publicado por: Anonymous

Tengo 4 tablas y Wallpapers, Downloads, Favorites, Votes que están definidas así. De wallpapers solo necesito el id.

Tabla Downloads

+----+---------+---------+
| id | wall_id | user_id |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |      35 |    NULL |
|  3 |      35 |    NULL |
+----+---------+---------+

Tablas Favorites

+----+---------+---------+
| id | user_id | wall_id |
+----+---------+---------+
|  1 |      12 |      10 |
|  2 |      12 |       2 |
+----+---------+---------+

Tabla Votes

+----+---------+---------+---------+
| id | user_id | wall_id | type    |
+----+---------+---------+---------+
|  1 |      12 |       1 | dislike |
|  2 |      12 |      39 | like    |
|  3 |       1 |       2 | like    |
|  4 |       2 |       2 | like    |
|  5 |       3 |       2 | like    |
|  6 |       5 |       2 | dislike |
|  7 |      12 |      10 | like    |
|  8 |      12 |       2 | like    |
+----+---------+---------+---------+

Básicamente lo que necesito es una consulta que me devuelva la cantidad de Descargas, Favoritos, Likes y Dislikes de un wallpaper. Trate anidando con varios LEFT OUTER JOIN asi:

SELECT w.id,COUNT(d.id) AS Downloads,
COUNT(f.id) AS Favorites,
SUM(IF(v.type = 'like',1,0)) AS Likes,
SUM(IF(v.type = 'dislike',1,0)) AS Dislikes 
FROM wallpapers AS w 
LEFT OUTER JOIN downloads AS d ON w.id = d.wall_id 
LEFT OUTER JOIN favorites AS f ON w.id = f.wall_id 
LEFT OUTER JOIN votes AS v ON w.id = v.wall_id 
WHERE w.id = 2
GROUP BY w.id;

Que me devuelve el resultado de la forma que necesito pero con los cálculos erróneos es decir se duplican valores y pasan otras cosas extrañas.

Tabla Resultados

+----+-----------+-----------+-------+----------+
| id | Downloads | Favorites | Likes | Dislikes |
+----+-----------+-----------+-------+----------+
|  2 |        10 |        10 |     8 |        2 |
+----+-----------+-----------+-------+----------+

Tengo entendido que es por que los LEFT OUTER JOIN no se pueden anidar así ya que las 3 tablas deben tener registros que correspondan con el id de la primera tabla.

¿Alguna Solucion?

solución

El problema es que algunos de los valores se están contando múltiples veces. Esto se debe a que se está haciendo el COUNT de los id’s sin comprobar que esos id’s no se hayan contado antes. El mismo error ocurrirá con los SUM, por lo que podría darse el caso de que se sumen los mismos votos varias veces.

Al hacer un LEFT OUTER JOIN, se mantienen todos los registros de las tablas de la izquierda y se combinan con los registros de las tabla de la derecha (o con NULL si no hay ninguno). El problema está en que al mantener los valores de la izquierda, se están duplicando (o multiplicando) algunos de ellos porque se “vuelven a añadir” por cada registro de la derecha.

Para ver esto mejor vamos a quitar los COUNT, SUM y GROUP BY de tu SELECT, lo que nos deja la siguiente sentencia:

SELECT w.id,
       d.id AS Downloads,
       f.id AS Favorites,
       v.type AS Likes,
       v.type AS Dislikes 
FROM   wallpapers AS w 
       LEFT OUTER JOIN downloads AS d ON w.id = d.wall_id 
       LEFT OUTER JOIN favorites AS f ON w.id = f.wall_id 
       LEFT OUTER JOIN votes AS v ON w.id = v.wall_id 
WHERE  w.id = 2

que al ejecutarse con los datos proporcionados en la pregunta nos devuelve lo siguiente:

id | Downloads | Favorites | Likes    | Dislikes
------------------------------------------------
2  | NULL      | 2         | like     | like
2  | NULL      | 2         | like     | like
2  | NULL      | 2         | like     | like
2  | NULL      | 2         | dislike  | dislike
2  | NULL      | 2         | like     | like

Como ves, primero se selecciona w.id que es 2 como se indica en el WHERE, no hay ninguna descarga por lo que Downloads es NULL, sí se encuentra un favorito con el id 2… y ahora es cuando empieza el problema: se encuentran 5 votos, por los que cada fila tendrá la misma parte izquierda combinada con cada uno de los votos. Eso no sería un problema sino fuera porque ahora nos encontramos con que el id 2 se ha seleccionado 5 veces (una por cada voto).

Una solución rápida sería añadir un DISTINCT a los COUNT para evitar ese problema:

SELECT w.id,
       COUNT(DISTINCT d.id) AS Downloads,
       COUNT(DISTINCT f.id) AS Favorites,
       SUM(IF(v.type = 'like',1,0)) AS Likes,
       SUM(IF(v.type = 'dislike',1,0)) AS Dislikes 
FROM   wallpapers AS w 
       LEFT OUTER JOIN downloads AS d ON w.id = d.wall_id 
       LEFT OUTER JOIN favorites AS f ON w.id = f.wall_id 
       LEFT OUTER JOIN votes AS v ON w.id = v.wall_id 
WHERE  w.id = 2
GROUP BY w.id;

Eso parece funcionar a simple vista, pero te sigue dejando un problema con los SUM (que no se puede ver de primeras con los datos puestos en la pregunta). Imagina que en lugar de haber un único favorito para el Wallpaper con id 2 (como hay ahora), hubiese dos. En ese caso, todos los votos se combinarán con esos dos produciendo 10 filas. Y los SUM estarían sumando el mismo voto dos veces.

Para solucionar esto, mi recomendación sería que en lugar de usar SUM utilizaras COUNT con DISTINCT como para los otros valores (es lo que estás haciendo de todos modos, porque realmente estás simulando un COUNT con un SUM). Y que separaras los “Likes” y “Dislikes” con JOINs diferentes.

Con los cambios que digo, la sentencia SQL sería así:

SELECT w.id, 
       COUNT(DISTINCT d.id) AS Downloads,
       COUNT(DISTINCT f.id) AS Favorites,
       COUNT(DISTINCT v1.id) AS Likes,
       COUNT(DISTINCT v2.id) AS Dislikes 
FROM   wallpapers AS w 
       LEFT OUTER JOIN downloads AS d ON w.id = d.wall_id 
       LEFT OUTER JOIN favorites AS f ON w.id = f.wall_id 
       LEFT OUTER JOIN votes AS v1 ON w.id = v1.wall_id AND v1.type = 'like'
       LEFT OUTER JOIN votes AS v2 ON w.id = v2.wall_id AND v2.type = 'dislike'
WHERE  w.id = 2
GROUP BY w.id

Que ya devuelve los valores correctos siempre porque aunque haya id’s duplicados, sólo se están contando los id’s distintos.

Respondido por: Anonymous

Leave a Reply

Your email address will not be published.