多次使用LEFT JOIN仅带来1行 [英] Multiple use of LEFT JOIN brings only 1 row
问题描述
这是一个智能的基于标签的图像搜索系统.用户在这样的表格中添加带有适当标签的图像:
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 */
用户查看图像并将来自* 这些图像的标签 * 的访问记录在usertagview表中. (请注意，我为此目的使用了INSERT ON DUPLICATE UPDATE查询.)
usertagview (user_id, tag_id, view_count)
现在，请考虑一些带有以下标签的图像:
-
river，day(这是晴天显示的河流的图片) -
river，night(午夜月亮照耀下的那条河) -
tree，day -
tree，night -
flower，day -
flower，night
用户搜索标签river，并显示所有带有标签河流的图像:在这种情况下，第一张图像(由河流天标记)会显示第二个(标记为河流之夜).用户查看第二张图像(用river和night标记)并将其记录在表usertagview中.
然后用户尝试重新搜索标签tree并查看tree night图片.
我希望如果用户搜索flower，则首选flower night，而不是flower day.我的意思是flower night应该先于flower day.换句话说，我想要一个查询，该查询根据用户的先前视图列出用flower标记的图像. (首先 flower night，其次是其他flower ).
我的查询失败:
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_entered)} )
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
问题
是我的查询中的 **LEFT JOIN** 与普通的 INNER JOIN 没有区别.他们都有相同的结果.即使我使用 RIGHT JOIN 也没什么区别.
您的left join与inner join相同的原因是，您在where子句中有针对left join的附加条件.这实际上将您的outer join变成了inner join.
之所以这样，是因为如果在没有匹配记录的情况下usertagview.tag_id是NULL，则WHERE子句中的IN语句将消除具有NULL值的行.
要解决此问题，可以将usertagview.tag_id IN ...检查移到联接的ON子句中.
但是，这只是您问题的一半.您只需要检查用户输入的特定标签的视图，但是如果我了解您的实际要求，则想检查与包含与您的搜索字词匹配的标签的任何图像相关联的所有标签的视图
例如，当用户输入flower时，您要首先查找带有flower标记的任何图像，然后检查该图像集的所有其他标记的视图.
我相信以下查询可以完成此操作，并且此SQL提琴显示了正在使用的查询:
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
It is an intelligent tag base image search system. User adds images with its proper tags in such a table:
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 */
User views images and the visits from *those images' tags* are logged in usertagview table. (Note that I've used an INSERT ON DUPLICATE UPDATE query for that purpose.)
usertagview (user_id, tag_id, view_count)
Now please consider some images with the following tags:
river,day(It's a picture that shows a river in a sunny day)river,night(That river at the light of the midnight moon)tree,daytree,nightflower,dayflower,night
User searches for the tag river and any images that has the tag river is displayed: In this case the first image (tagged by river day) and the second (tagged by river night) are shown. User views the second image (tagged by river and night) and view it is logged in the table usertagview.
Then the user tries a new search for the tag tree and views the tree night image.
I want that if the user searches for flower, the flower night be preferred over the flower day. I mean flower night should come before flower day. In other words I want a query that lists images tagged by flower according to user's previous views. (flower night first, OTHER flowers next).
My query that was failed:
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_entered)} )
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
THE PROBLEM
is that the **LEFT JOIN** in my query has no difference with a normal INNER JOIN. They both have the same result. Even if I use RIGHT JOIN it will have no difference.
The reason that your left join is behaving the same as an inner join is because you have additional criteria for your left join in your where clause. This essentially turns your outer join into an inner join.
The reason for this is because if usertagview.tag_id is NULL in the case where there is no matching record, your IN statement in your WHERE clause eliminates the row with the NULL value.
To correct this, you could move your usertagview.tag_id IN ... check into your join's ON clause.
However, this is only half of your problem. You're only checking the views for the specific tag that was entered by the user, but if I understand your actual requirements, you want to check the views for any tags that are associated with any image that has a tag that matches your search term.
For example, when the user enters flower, you want to first find any image that is tagged with flower, and then check the views for all other tags for that set of images.
I believe the following query accomplishes this, and this SQL Fiddle shows the query in action:
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
这篇关于多次使用LEFT JOIN仅带来1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
