El uso múltiple de LEFT JOIN solo trae 1 fila

Es un sistema inteligente de búsqueda de imágenes de tags. El usuario agrega imágenes con sus tags adecuadas en dicha tabla:

image (id, title, ...) tag (id, title) /* It doesn't matter who has created the tag*/ imagetag (image_id, tag_id) /* One image may have multiple tags */ 

Las imágenes de vistas de usuario y las visitas de * esas tags de imágenes * se registran en la tabla usertagview . (Tenga en count que he utilizado una consulta INSERT ON DUPLICATE UPDATE para ese fin).

 usertagview (user_id, tag_id, view_count) 

Ahora considere algunas imágenes con las siguientes tags:

  • river , day (Es una image que muestra un río en un día soleado)
  • river , night (Ese río a la luz de la luna de medianoche)
  • tree , day
  • tree , night
  • flower , day
  • flower , night

El usuario busca la label river y cualquier image que tenga la label river : en este caso, se muestran la primera image (labelda por river day ) y la segunda (labelda por river night ). El usuario ve la segunda image (labelda por el river y la night ) y la ve registrada en la tabla usertagview .

Luego, el usuario intenta una nueva búsqueda del tree tags y ve la image de la tree night del tree night .

Quiero que si el usuario busca flower , prefiera la flower night en lugar del flower day las flower day . Quiero decir que la flower night debe ser anterior al flower day . En otras palabras, quiero una consulta que enumere las imágenes labeldas por flower según las vistas previas del usuario. ( flower night primero, OTRA flower es la siguiente ).

Mi consulta que falló:

 SELECT DISTINCT (image.id) AS image_id, image.title AS image_title, SUM(usertagview.view_count) AS SUM_of_all_tag_views_for_each_image FROM (image) JOIN imagetag ON imagetag.image_id = image.id **LEFT JOIN** usertagview ON usertagview.tag_id = imagetag.tag_id AND usertagview.user_id = {$user_id_from_php} WHERE imagetag.tag_id IN ( {impolde(',', $array_of_id_of_tags_that_the_user_has_entenetworking)} ) AND usertagview.tag_id IN (SELECT tag_id FROM imagetag WHERE userimagetag.image_id = image.id) ORDER BY SUM_of_all_tag_views_for_each_image DESC 

EL PROBLEMA

es que el **LEFT JOIN** en mi consulta no tiene ninguna diferencia con una INNER JOIN normal. Ambos tienen el mismo resultado. Incluso si uso RIGHT JOIN no tendrá ninguna diferencia.

La razón por la que su left join se comporta de la misma manera que una inner join es porque tiene criterios adicionales para su left join en su cláusula where . Esto esencialmente convierte su outer join en una inner join .

La razón de esto es porque si usertagview.tag_id es NULL en el caso en que no haya ningún logging coincidente, su instrucción IN en su cláusula WHERE elimina la fila con el valor NULL .

Para corregir esto, puede mover su usertagview.tag_id IN ... verificar la cláusula ON su join.

Sin embargo, esto es solo la mitad de tu problema. Solo está consultando las vistas de la label específica que ingresó el usuario, pero si entiendo sus requisitos reales, desea verificar las vistas de las tags asociadas a cualquier image que tenga una label que coincida con su término de búsqueda. .

Por ejemplo, cuando el usuario ingresa flower , primero quiere encontrar cualquier image que esté labelda con flower , y luego verificar las vistas para todas las demás tags para ese set de imágenes.

Creo que la siguiente consulta lo logra y este SQL Fiddle muestra la consulta en acción :

 SELECT i.id AS image_id, i.title AS image_title, IFNULL(SUM(utv.view_count), 0) AS associated_view_totals FROM imagetag originalTag JOIN imagetag associatedTags ON associatedTags.image_id = originalTag.image_id JOIN image i ON i.id = associatedTags.image_id LEFT JOIN usertagview utv ON utv.user_id = 1 AND utv.tag_id = associatedTags.tag_id WHERE -- User searches for flower tag (Let's assume 5 == flower)... originalTag.tag_id IN (5) GROUP BY i.id, i.title ORDER BY associated_view_totals DESC 

Este es un problema común. Y afortunadamente, es fácil de resolver.

¿Mira esto?

 LEFT JOIN usertagview ON usertagview.tag_id = imagetag.tag_id -- see this? AND usertagview.user_id = {$user_id_from_php} WHERE imagetag.tag_id IN ( {impolde(',', $array_of_id_of_tags_that_the_user_has_entenetworking)} ) AND 

¿Y esto?

  usertagview.tag_id IN -- and this? (SELECT tag_id FROM imagetag WHERE userimagetag.image_id = image.id) 

Ambas condiciones comparten el mismo campo, es decir, usertagview.tag_id . De modo que usertagview.tag_id IN (SELECT tag_id FROM ...) en su cláusula WHERE básicamente cancela el éxito que usertagview en la unión LEFT JOIN de la label de image.

Por lo tanto, para solucionar su consulta, restaure su INNER JOIN usertagview a LEFT JOIN , luego mueva la condición usertagview a JOIN en su lugar:

 SELECT DISTINCT (image.id) AS image_id, image.title AS image_title, SUM(usertagview.view_count) AS SUM_of_all_tag_views_for_each_image FROM (image) JOIN imagetag ON imagetag.image_id = image.id LEFT JOIN usertagview ON usertagview.tag_id = imagetag.tag_id AND usertagview.user_id = {$user_id_from_php} -- moved the WHERE condition here AND usertagview.tag_id IN (SELECT tag_id FROM imagetag WHERE userimagetag.image_id = image.id) WHERE imagetag.tag_id IN ( {impolde(',', $array_of_id_of_tags_that_the_user_has_entenetworking)} ) ORDER BY SUM_of_all_tag_views_for_each_image DESC 

Eso lo arreglaría. Si no es así (ya que no sé exactamente en sus tablas que son uno-a-muchos el uno para el otro, o el uno para el otro, así que en este caso voy a lanzar lo que generalmente funciona ), intente cambiar la INNER JOIN imagetag de INNER JOIN imagetag a LEFT JOIN . Y dado que la condición de la imagetag en la cláusula WHERE cancelará las filas que imagetag condición de LEFT JOIN , mueva esa condición de imagetag de image de la cláusula WHERE a LEFT JOIN también:

 SELECT DISTINCT (image.id) AS image_id, image.title AS image_title, SUM(usertagview.view_count) AS SUM_of_all_tag_views_for_each_image FROM (image) LEFT JOIN imagetag ON imagetag.image_id = image.id -- WHERE clause condition moved here. -- WHERE conditionXXX basically cancels out whatever rows -- obtained from `LEFT JOIN ON conditionXXX`, in which conditionXXX share -- the same field. IN this case, it is imagetag. AND imagetag.tag_id IN ( {impolde(',', $array_of_id_of_tags_that_the_user_has_entenetworking)} ) LEFT JOIN usertagview ON usertagview.tag_id = imagetag.tag_id AND usertagview.user_id = {$user_id_from_php} -- moved here AND usertagview.tag_id IN (SELECT tag_id FROM imagetag WHERE userimagetag.image_id = image.id) ORDER BY SUM_of_all_tag_views_for_each_image DESC 

Y si la segunda sugerencia aún no entrega los resultados, su consulta actualmente maneja múltiples relaciones de tabla de uno a muchos. SQL no puede determinar su intención si tiene múltiples relaciones de tablas de uno a muchos en las consultas; en este caso, debe aplanar los resultados para get la salida correcta. Aquí hay un buen tutorial sobre cómo aplanar los resultados: http://www.anicehumble.com/2012/05/sql-count-computer-program-does-what.html

Siempre obtienes los resultados de la image de tu tabla + label de image INNER JOIN, sin tener en count a qué te refieres después / después. Si quiere get más resultados, también necesita IZQUIERDA UNIR la tabla de imágenes.

    Intereting Posts