它是一个智能的标签库图像搜索系统。用户在这样的表中添加带有适当标记的图像:
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的任何图像:在这种情况下,将显示第一个图像(按river day标记)和第二个图像(按river night标记)。用户查看第二个图像(标记为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
相同的原因是您在left join
子句中有其他where
条件。这基本上将您的outer join
变成inner join
。
原因是,如果在没有匹配记录的情况下usertagview.tag_id
是NULL
,则IN
子句中的WHERE
语句将删除值为NULL
的行。
要更正此问题,可以将usertagview.tag_id IN ...
签入join的ON
子句。
然而,这只是你问题的一半。您只检查用户输入的特定标记的视图,但如果我了解您的实际需求,则需要检查视图中是否有与任何具有与搜索词匹配的标记的图像关联的任何标记。
例如,当用户输入flower
时,您希望首先找到用flower
标记的任何图像,然后检查视图中该图像集的所有其他标记。
我相信下面的查询完成了这一点,并且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