mysql - 如何围绕表循环进行内部连接

Question

我有如下四个表:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `categories_friends` (
  `category_id` int(10) unsigned NOT NULL,
  `friend_id` int(10) unsigned NOT NULL,
  UNIQUE KEY `category_id` (`friend_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `friends` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `friend_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`friend_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `ratings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `rating` tinyint(2) unsigned NOT NULL,
  `public` tinyint(1) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

我正在尝试对这些表执行以下查询:

SELECT *
FROM `favred`.`ratings` AS `Rating`
    INNER JOIN `favred`.`friends` AS `JFriend`
        ON (`JFriend`.`friend_id` = `Rating`.`user_id`)
    INNER JOIN `favred`.`categories_friends` AS `JCategoriesFriend`
        ON (`JCategoriesFriend`.`category_id` = `Rating`.`category_id`
            AND `JCategoriesFriend`.`friend_id` = `JFriend`.`id`)
    INNER JOIN `favred`.`categories` AS `JCategory`
        ON (`JCategory`.`id` = `Rating`.`category_id`
            AND `JCategory`.`id` = `JCategoriesFriend`.`category_id`)
WHERE `JFriend`.`user_id` = 1
    AND `Rating`.`user_id` <> 1
    AND `JCategory`.`id` IN (4, 14)
GROUP BY `Rating`.`id`

上面的查询不起作用，因为它没有返回任何结果(尽管表中有应该返回的数据)，我想要做的是找到所有不是我创作的评级(ID: 1)，但是由我的 friend 创作的，但前提是我选择查看该 friend 的特定类别，结果集按一组给定的特定类别进行过滤。

INNER JOIN 循环遍历 Rating --> Friend --> CategoriesFreind --> Category --> 回到 Rating。

如果我删除 INNER JOIN 的 ON 子句的附加部分，如下所示:

SELECT *
FROM `favred`.`ratings` AS `Rating` 
  INNER JOIN `favred`.`friends` AS `JFriend` 
    ON (`JFriend`.`friend_id` = `Rating`.`user_id`) 
  INNER JOIN `favred`.`categories_friends` AS `JCategoriesFriend` 
    ON (`JCategoriesFriend`.`friend_id` = `JFriend`.`id`) 
  INNER JOIN `favred`.`categories` AS `JCategory` 
    ON (`JCategory`.`id` = `JCategoriesFriend`.`category_id`) 
WHERE `JFriend`.`user_id` = 1 
  AND `Rating`.`user_id` <> 1 
  AND `JCategory`.`id` IN (4, 14) 
GROUP BY `Rating`.`id` 

然后查询将返回结果，但是因为将 CategoriesFriend 连接到 Rating 的 INNER JOIN 没有被 'JCategory'.'id' IN (4, 14) 子句过滤，它返回该 friend 的所有评分，而不是按应有的方式过滤。

关于如何修改我的查询以使其提取过滤结果的任何建议？

我使用的是 CakePHP，因此尽管不是必需的，但适合其独特查询格式的查询将是首选。

Answer 1

首先，为什么要用JFriend.id，是什么意思，还是和user_id一样？

试试这个，同样的逻辑但是是从上到下，我感觉:

SELECT * FROM categories as JCategory
INNER JOIN categories_friends as JCategoriesFriend ON JCategoriesFriend.category_id = JCategory.id
INNER JOIN friends AS JFriend ON JFriend.friend_id = JCategoriesFriend.friend_id
INNER JOIN ratings AS Rating ON Rating.user_id = JFriend.friend_id
WHERE JCategory.id IN (4,14) AND JFriend.user_id = 1 AND Rating.user_id <> 1 GROUP BY Rating.id

我从为测试制作的所有数据中得到了一个结果。如果它也不起作用，请尝试制作一些正确的数据，也许数据不正确...

测试数据如下:

categories: id | name (14| 141414)
categories_friends: category_id| friend_id (14| 2)
friends: id | user_id | friend_id (4| 1| 2)
ratings: id | user_id | category_id | title  (2| 2| 14 | 'haha')