时间戳

首页 > 解决方案 > 如果项目或其子项与标签匹配，则选择项目

mysql - 如果项目或其子项与标签匹配，则选择项目

问题描述

我正在尝试选择已创建列表项的用户列表。

可以有父列表项和列表项的子项。

仅应显示与某个标签匹配的结果，或者如果他们的孩子与该标签匹配。

我做了一个简化的可重现示例，由于only_full_group_by.

这是我目前试图显示与 ID 3 (tag-c) 的标签匹配的结果的查询。

此查询无法显示匹配项及其已发布子项的所有标签。我也摆弄了这么多，以至于 ATM 将子项目显示为结果，这是错误的。

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `branchid` int(11) NOT NULL,
  `title` text NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL,
  `vid` int(11) NOT NULL,  
  PRIMARY KEY (`id`)
);

INSERT IGNORE INTO `listings` VALUES 
(1, 0, 'Listing',1,1,1),
(2, 0, 'Listing',1,1,1),
(3, 0, 'Listing',1,2,2),
(4, 2, 'Listing B - Child',1,1,1),
(5, 2, 'Listing B - Child',1,1,1),
(6, 3, 'Listing C - Child',1,2,2),
(9, 3, 'Listing C - Child',0,2,2),
(7, 0, 'Listing',1,3,3),
(8, 7, 'Listing D - Child',1,3,3);


CREATE TABLE IF NOT EXISTS `listing_map` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `listing_id` int(11) NOT NULL,
  `tagid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT IGNORE INTO `listing_map` VALUES 
(600,1,1),
(610,1,2),
(632,2,1),
(650,2,3),
(652,2,4),
(653,3,6),
(654,3,2),
(655,5,1),
(605,5,6),
(590,5,4),
(638,6,2),
(601,6,3),
(611,8,5),
(612,9,1);

CREATE TABLE IF NOT EXISTS `listing_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   `title` text NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT IGNORE INTO `listing_tags` VALUES 
(1, 'tag-a'),
(2, 'tag-b'),
(3, 'tag-c'),
(4, 'tag-d'),
(5, 'tag-e'),
(6, 'tag-f');

CREATE TABLE IF NOT EXISTS `vendors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
   `uid` int(11) NOT NULL,
   `title` text NOT NULL,
   `default` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

INSERT IGNORE INTO `vendors` VALUES 
(1, 1, 'vendor A', 1),
(2, 2, 'vendor B', 0),
(3, 3, 'vendor C', 1),
(4, 4, 'vendor D', 1);

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT IGNORE INTO `users` VALUES 
(1, 'user A'),
(2, 'user B'),
(3, 'user C'),
(4, 'user D');

https://www.db-fiddle.com/f/pMJvDPNqVRhcEhD4U4r3f7/19

如果 branchid 等于 0，则它是父项。如果 branchid 是数字，则它是 branchid 父项的子项。

有几个重要条件：

  1. 一次只搜索一个标签。所以不需要匹配多个标签（虽然会很好）。
  2. 结果应该只显示父项，即使它们不匹配任何标签，但它们的子项匹配。
  3. 结果应显示父项及其子项（如果存在）包含的所有标签。我一直在尝试通过 GROUP_CONCAT。
  4. 应考虑父母和孩子的已发表状态
  5. 一位用户被分配给一位供应商
  6. 未分配列表的供应商应获得包含其用户标题而不是供应商标题的默认结果。
  7. 如果搜索标签，默认供应商将不再获得结果。因此，如果没有搜索任何内容，“供应商 B”就会得到结果。

只是一个解释：

Listing A
- user 1, vendor 1
- no childs 
- tags: tag-a, tag-b

Listing B
- user 1, vendor 2
- two childs 
- tags: tag-a, tag-c, tag-d, tag-f

Listing C
- user 2, vendor 2
- one child
- tags: tag-f, tag-b, tag-c

Listing D
- user 3, vendor 3
- one child
- tags: tag-e

显示匹配 tag-f 的项目：

Listing B (tags: tag-a, tag-c, tag-d, tag-f)
Listing C (tag-f, tag-b, tag-c)

显示匹配标签-e的项目：

Listing D (tag-e)

显示所有项目：

Listing A (tag-a, tag-b)
Listing B (tags: tag-a, tag-c, tag-d, tag-f)
Listing C (tag-f, tag-b, tag-c)
Listing D (tag-e)
Vendor D  (no tags, default entry)

谢谢

标签： mysqlmariadb

解决方案

回顾一下，这是一个返回所有孤儿及其关联标签的查询（假设层次结构只有一层深）......

请注意，我用 NULL 而不是 '0' 表示孤儿，因为这是我更习惯的，但如果您愿意，可以将其更改回 0。

SELECT DISTINCT parent.id
              , parent.title listing
              , parent.uid
              , parent.vid
              , t.title tag
           FROM listings parent 
           LEFT
           JOIN listings child 
             ON child.branchid = parent.id
            AND child.published = 1
           JOIN listing_map lt
             ON lt.listing_id IN(parent.id,child.id)
           JOIN listing_tags t
             ON t.id = lt.tagid
          WHERE parent.branchid IS NULL
          ORDER 
             BY parent.id
              , tag;
+----+-----------+-----+-----+-------+
| id | listing   | uid | vid | tag   |
+----+-----------+-----+-----+-------+
|  1 | Listing A |   1 |   1 | tag-a |
|  1 | Listing A |   1 |   1 | tag-b |
|  2 | Listing B |   1 |   1 | tag-a |
|  2 | Listing B |   1 |   1 | tag-c |
|  2 | Listing B |   1 |   1 | tag-d |
|  2 | Listing B |   1 |   1 | tag-f |
|  3 | Listing C |   2 |   2 | tag-b |
|  3 | Listing C |   2 |   2 | tag-c |
|  3 | Listing C |   2 |   2 | tag-f |
|  7 | Listing D |   3 |   3 | tag-e |
+----+-----------+-----+-----+-------+

推荐阅读