按標簽搜索物體并在MySQL中回傳所有其他標簽

在我的例子中，一個物體是一個食譜，一個標簽是一個成分。我想按成分搜索并回傳包含該成分的所有食譜并回傳所有其他成分。在我的情況下，成分可以有同義詞。

首先，這是我的表：

CREATE TABLE `ingredient` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `synonym_group` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `recipe` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `type` varchar(45) DEFAULT NULL,
  `comment` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `recipe_ingredient` (
  `recipe_id` int NOT NULL,
  `ingredient_id` int NOT NULL,
  `alternative_ingredient_id` int DEFAULT NULL,
  `is_optional` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`recipe_id`,`ingredient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

這是我提出的查詢：

SELECT 
  r.name, 
  i_all.name

FROM
  recipe r
  
JOIN (
    SELECT DISTINCT 
        recipe_id 
    FROM 
        ingredient i 
        JOIN recipe_ingredient ri ON i.id = ri.ingredient_id 
        LEFT JOIN ingredient i_syn ON i.synonym_group = i_syn.synonym_group 
    WHERE i.name = 'Asd' OR i_syn.name = 'Asd'
) i ON i.recipe_id = r.id 

JOIN recipe_ingredient ri ON i.recipe_id = ri.recipe_id 
JOIN ingredient i_all ON ri.ingredient_id = i_all.id

我覺得這個查詢對于我想要做的事情來說非常復雜，并且有一種更簡單的方法。主要感覺很奇怪，我必須在查詢中兩次指定搜索詞。現在，如果我只過濾i.name = 'Asd'我不會找到任何同義詞，如果我只過濾i_syn.name = 'Asd'我不會在搜索詞沒有同義詞的情況下找到任何東西。洗掉distinct似乎沒有改變任何東西。

沒有更好的方法還是我想得太復雜了？

成分的示例資料：

uj5u.com熱心網友回復：

我會在組中而不是在組中建立所有成分，通過將組設定為成分 id 然后加入配方以獲得不同的配方，然后可以簡單地加入表格。這樣，代碼就不會關心您搜索的是哪種成分或組成員。

drop table if exists ingredient,recipe,recipe_ingredient;
CREATE TABLE `ingredient` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `synonym_group` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ;
insert into ingredient (id,name ,synonym_group) values
(1,'egg',null),(2,'bell pepper',10),(3,'capsicum',10),(4,'sugar',null),(5,'chilli pepper',null);

CREATE TABLE `recipe` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `type` varchar(45) DEFAULT NULL,
  `comment` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `recipe_ingredient` (
  `recipe_id` int NOT NULL,
  `ingredient_id` int NOT NULL,
  `alternative_ingredient_id` int DEFAULT NULL,
  `is_optional` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`recipe_id`,`ingredient_id`)
) ;

insert into recipe_ingredient values
(1,1,null,null),(2,2,null,null),(2,5,null,null),(2,4,null,null),(3,3,null,null);

insert into recipe (id, name) values
(1,'stew'),(2,'nei'),(3,'three');

select r.id,r.name,ri.recipe_id,ri.ingredient_id,i.name
from recipe_ingredient ri
join recipe r on r.id = ri.recipe_id
join ingredient i on i.id = ri.ingredient_id
join
(
select distinct ri.recipe_id rid
from recipe_ingredient ri
join
    (
    select i.id ,sg
    from ingredient i 
    join
    (
    Select case 
        when i.synonym_group is not null then i.synonym_group 
        else i.id
        end as sg
    from ingredient i where i.name = 'capsicum' 
    ) t on 
    t.sg = i.synonym_group or 
    t.sg = i.id
) u on u.id = ri.ingredient_id
) v on v.rid = ri.recipe_id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=879979bde1118b6e8fb1acf6d090204c

uj5u.com熱心網友回復：

你說——

洗掉distinct似乎沒有改變任何東西。

這表明您的測驗資料不足。下面說明了DISTINCT在您當前查詢中的重要性。鑒于上面列出的成分和下面的食譜-

你內心的疑問——

SELECT DISTINCT 
    recipe_id 
FROM 
    ingredient i 
    JOIN recipe_ingredient ri ON i.id = ri.ingredient_id 
    LEFT JOIN ingredient i_syn ON i.synonym_group = i_syn.synonym_group
WHERE i.name = 'Bell Pepper' OR i_syn.name = 'Bell Pepper';

回傳 -

但如果你放下DISTINCT它，它會回傳 -

如果您洗掉DISTINCT但添加了一個額外的連接謂詞來停止它 LEFT JOINing 到它自己，你會得到想要的結果 -

SELECT
    recipe_id 
FROM 
    ingredient i 
    JOIN recipe_ingredient ri ON i.id = ri.ingredient_id 
    LEFT JOIN ingredient i_syn ON i.synonym_group = i_syn.synonym_group AND i.id <> i_syn.id
WHERE i.name = 'Bell Pepper' OR i_syn.name = 'Bell Pepper';

您當前的查詢似乎是最佳的，并且可以輕松修改以允許基于多種成分進行搜索，只需修改內部查詢即可獲得最佳匹配型別排名 -

SELECT 
    recipe_id, COUNT(ingredient_id) AS ingredients_matched
FROM 
    ingredient i 
    JOIN recipe_ingredient ri ON i.id = ri.ingredient_id 
    LEFT JOIN ingredient i_syn ON i.synonym_group = i_syn.synonym_group AND i.id <> i_syn.id
WHERE i.name IN ('Bell Pepper', 'Sugar', 'Egg') OR i_syn.name IN ('Bell Pepper', 'Sugar', 'Egg')
GROUP BY recipe_id

標籤：mysql query-optimization

上一篇：查詢僅在WITH...AS陳述句中不起作用

下一篇：如何在MYSQL視圖中使用遞回