php - 修改 MySQL 查询以在菜单中显示在父记录下排序的子记录

Question

我需要构建一个 MySQL 数据库驱动的链接菜单，它只显示用户有权查看的链接。

链接菜单还将有parent 和child 链接。子链接将位于父链接之下(如目录结构)。

我有 2 个数据库表，如下所示。 intranet_links有链接记录，user_link_permissions有权限决定用户是否可以查看链接。

这是我当前的 SQL 查询，它只返回用户有权查看的链接...

这里还有一个可以查看结果的工作演示 http://sqlfiddle.com/#!9/8aa75/2

SELECT intranet_links.id,
       intranet_links.title,
       intranet_links.url,
       intranet_links.description,
       intranet_links.sort_order,
       intranet_links.parent,
       user_link_permissions.user_id
FROM user_link_permissions
JOIN intranet_links ON user_link_permissions.link_id=intranet_links.id AND user_link_permissions.permission=1
WHERE user_link_permissions.permission=1
AND user_link_permissions.user_id=1
ORDER BY title

我需要帮助让我的结果以一种让子链接在父链接下排序的方式排序。

我使用的是 PHP，所以它不必全是 SQL。我想要的最终结果将是这样的菜单...

---

数据库结构和数据...

CREATE TABLE IF NOT EXISTS `intranet_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `description` text NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `active` int(2) NOT NULL DEFAULT '1',
  `sort_order` int(11) DEFAULT NULL,
  `parent` int(10) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `intranet_links`
--
INSERT INTO `intranet_links` (`id`, `title`, `description`, `url`, `active`, `sort_order`, `parent`) VALUES
('1', 'Lnk Parent 1', '', 'google.com', 1, NULL, 0),
('2', 'ink 2', '', 'google.com', 1, NULL, 0),
('3', 'link 3', '', 'google.com', 1, NULL, 0),
('4', 'Lnk Parent 2', '', 'google.com', 1, NULL, 0),
('5', 'ink 5', '', 'google.com', 1, NULL, 2),
('6', 'link 6', '', 'google.com', 1, NULL, 2),
('7', 'ink 7', '', 'google.com', 1, NULL, 2),
('8', 'link 8', '', 'google.com', 1, NULL, 2);



CREATE TABLE IF NOT EXISTS `user_link_permissions` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `user_id` int(30) NOT NULL,
  `link_id` int(30) NOT NULL,
  `permission` int(2) NOT NULL DEFAULT '0',
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `user_link_permissions`
--
INSERT INTO `user_link_permissions` (`id`, `user_id`, `link_id`, `permission`) VALUES
(1, 1, 1, 1),
(2, 1, 2, 1),
(3, 1, 3, 0),
(4, 1, 4, 1),
(5, 1, 5, 1),
(6, 1, 6, 0),
(7, 1, 7, 1),
(8, 1, 8, 1),
(9, 2, 1, 1),
(10, 2, 2, 1),
(11, 2, 3, 1),
(12, 2, 4, 1),
(13, 2, 5, 1),
(14, 2, 6, 1),
(15, 2, 7, 1),
(16, 2, 8, 1);

Answer 1

我认为最好的办法是将其分成两个查询。因此，首先查询用户有权查看的所有 link_id。

SELECT link_id FROM user_link_permissions WHERE user_id=:user AND permission=1

然后根据该查询的结果，执行第二个查询以实际获取链接并根据需要对它们进行排序

SELECT * FROM intranet_links WHERE id IN (123, 456, 789) ORDER BY title

您可以使用子查询同样轻松地做到这一点，但我更喜欢将 SQL 分解成更小的部分并取得了巨大的成功。