php - MySQL 查询基于多个标签检索结果

Question

如果必须处理现有的数据库结构并尝试找到一种有效的方法来根据标签选择文件。一张表包含"file"，一张表包含“标签描述”，第三张表包含与文件相关的所有“标签”。

如何选择所有文件:Language = 'ENG' and Measure = 'METRIC' and Category = 'Type1'？ (所以结果应该是一个文件:ID 100)。

这是相关 3 个表的简化版本:

CREATE TABLE IF NOT EXISTS `files` (
  `file_id` int(11) NOT NULL DEFAULT '0',
  `file_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `files` (`file_id`, `file_name`) VALUES
(100, 'testfile_1'),
(200, 'testfile_2'),
(300, 'testfile_3'),
(400, 'testfile_4');


CREATE TABLE IF NOT EXISTS `tag_parents` (
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `parent_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tag_parents` (`parent_id`, `parent_name`) VALUES
(1, 'Language'),
(2, 'Measure'),
(3, 'Category');


CREATE TABLE IF NOT EXISTS `tags` (
  `tag_id` int(11) NOT NULL DEFAULT '0',
  `file_id` int(11) DEFAULT NULL,
  `tag_parent_id` int(11) DEFAULT NULL,
  `tag_value` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tags` (`tag_id`, `file_id`, `tag_parent_id`, `tag_value`) VALUES
(1, 100, 1, 'ENG'),
(2, 200, 1, 'ENG'),
(3, 300, 1, 'FRA'),
(4, 400, 1, 'DEU'),
(5, 100, 2, 'METRIC'),
(6, 200, 2, 'IMPERIAL'),
(7, 300, 2, 'METRIC'),
(8, 400, 2, 'IMPERIAL'),
(9, 100, 3, 'Type1'),
(10, 200, 3, 'Type3'),
(11, 300, 3, 'Type1'),
(12, 400, 3, 'Type1');

感谢任何帮助。谢谢你! (到目前为止，我的所有试验都失败了，或者它们的速度太慢了(使用子选择))。

Answer 1

这让我想到元模型，其中对象(在本例中为文件)的属性不是列，而是要在标签中查找的值。它总是比将列直接放在表中慢，但您应该能够让它合理地工作。我将 tag_parents 视为 tag_type。以下 ( fiddle here) 应该有效:

select f.* 
from files f
where exists ( -- it should have the "Category"."Type1"
  select parent_id
  from tag_parents categoryT, tags category 
  where categoryT.parent_name="Category"
    and category.tag_parent_id=categoryT.parent_id
    and category.tag_value="Type1"
    and category.file_id=f.file_id
  )
  and exists ( -- as well as "Language"."ENG"
    select parent_id
    from tag_parents languageT, tags language
    where languageT.parent_name="Language"
      and language.tag_parent_id=languageT.parent_id
      and language.tag_value="ENG"
      and language.file_id=f.file_id
    )
  and exists ( -- as well as "Measure"."METRIC"
    select parent_id
    from tag_parents measureT, tags measure
    where measureT.parent_name="Measure"
      and measure.tag_parent_id=measureT.parent_id
      and measure.tag_value="METRIC"
      and measure.file_id=f.file_id
    )

您可以通过定义一些 View 来简化您的生活，例如 Category、Language 和 Measure(以及任何其他 tag_parents你会有)。这将使查询更具可读性。使用类别、度量和语言三个 View ，您可以编写:

select * from files f, category c, measure m, `language` l
where f.file_id=c.file_id and c.value="Type1"
  and f.file_id=l.file_id and l.value="ENG"
  and m.file_id=l.file_id and m.value="METRIC";

或者甚至更好，如果你有固定数量的这些属性，你可以定义一个类似于顶部查询的 View ，但使用外部连接而不是存在，具有可为空的列类别，措施和语言:

create view filesView (file_id, category, measure, `language`) as
  select f.file_id, c.tag_value, m.tag_value, l.tag_value
  from files f
    left outer join (tags c, tag_parents ct) on c.file_id=f.file_id
      and c.tag_parent_id=ct.parent_id
      and ct.parent_name="Category"
    left outer join (tags l, tag_parents lt) on l.file_id=f.file_id
      and l.tag_parent_id=lt.parent_id
      and lt.parent_name="Language"
    left outer join (tags m, tag_parents mt) on m.file_id=f.file_id
      and m.tag_parent_id=mt.parent_id
      and mt.parent_name="Measure";

然后你可以这样写:

select file_id, category, measure, `language` 
from filesView
where category="Type1"
  and `language`="ENG"
  and measure="METRIC";

(抱歉有点跑题了。)