mysql - 无法在搜索过滤器查询中找到所需的结果

Question

我从过去两天开始尝试在查询中获得所需的结果，但无法做到这一点。基本上我正在查询一个餐厅网站“基于标签”的搜索过滤器来搜索最喜欢的菜肴。

有一个单独的标签表，其中根据meal_id、restaurant_id保存了如此多的标签。餐馆->标签和餐点->标签的多对多关系..

用户输入的标签越多，出现的结果就越少。

请查看查询

SELECT
   ep_tag.tag_en as tag_meal,
   ep_tag2.tag_en as tag_restaurant,
   `ep_meal`.id as id_meal,
   `ep_meal`.name as name_plat,
   `ep_meal`.`id_budget`,
   `ep_meal_favorite`.date,
   `ep_restaurant`.id as `id_restaurant`,
   `ep_restaurant`.* 
from
   `ep_meal` 
   inner join
      `ep_meal_favorite` 
      on `ep_meal_favorite`.`id_meal` = `ep_meal`.id 
   inner join
      `ep_restaurant` 
      on `ep_meal_favorite`.`id_restaurant` = `ep_restaurant`.`id` 
   inner JOIN
      ep_tag 
      ON ep_meal_favorite.id_meal = ep_tag.id_meal 
   inner JOIN
      ep_tag as ep_tag2 
      ON ep_meal_favorite.id_restaurant = ep_tag2.id_restaurant 
where
   ep_meal_favorite.id_epicurean = 82 
   and `ep_restaurant`.id = 14 
   and `ep_meal`.id = 92 
   AND 
   (
      ep_tag.tag_en LIKE '%tuna%' 
      OR ep_tag2.tag_en LIKE'%tuna%'
   )
   AND 
   (
      ep_tag.tag_en LIKE '%karaoke%' 
      OR ep_tag2.tag_en LIKE'%karaoke%'
   )

上张图片是当前结果，问题是查询返回记录，其中tag_meal是“tuna”，tag_restaurant是“karaoke”，但匹配时返回空但另一个则不然。

我希望查询在满足一个条件时返回结果假设我为“meal”编写了2个标签，并且没有为“restaurant”编写了标签，反之亦然，同时我希望查询使用 AND，因为我想减少结果数量。

请在这方面帮助我。谢谢。

Answer 1

正如我所评论的，获取与一个或另一个记录匹配的所有行的方法是对它们进行或操作。

SELECT
   ep_tag.tag_en as tag_meal,
   ep_tag2.tag_en as tag_restaurant,
   `ep_meal`.id as id_meal,
   `ep_meal`.name as name_plat,
   `ep_meal`.`id_budget`,
   `ep_meal_favorite`.date,
   `ep_restaurant`.id as `id_restaurant`,
   `ep_restaurant`.* 
from
   `ep_meal` 
   inner join
      `ep_meal_favorite` 
      on `ep_meal_favorite`.`id_meal` = `ep_meal`.id 
   inner join
      `ep_restaurant` 
      on `ep_meal_favorite`.`id_restaurant` = `ep_restaurant`.`id` 
   inner JOIN
      ep_tag 
      ON ep_meal_favorite.id_meal = ep_tag.id_meal 
   inner JOIN
      ep_tag as ep_tag2 
      ON ep_meal_favorite.id_restaurant = ep_tag2.id_restaurant 
where
   ep_meal_favorite.id_epicurean = 82 
   and `ep_restaurant`.id = 14 
   and `ep_meal`.id = 92 
   AND 
   (
      ep_tag.tag_en LIKE '%tuna%' 
      OR ep_tag2.tag_en LIKE'%tuna%'
      OR ep_tag.tag_en LIKE '%karaoke%' 
      OR ep_tag2.tag_en LIKE'%karaoke%'
   )

这并不是那么有效。您可以构建正则表达式来优化。