带有多个关键字的 WHERE LIKE [英] WHERE LIKE with multiple keywords

问题描述

我有相当复杂的查询，它在 4 个表中搜索一个或多个关键字:

I have fairly complicated query which searches across 4 tables for one or more keywords:

select distinct textures.id from textures
left join `category_texture` on `category_texture`.`texture_id` = `textures`.`id`
left join `categories` on `categories`.`id` = `category_texture`.`category_id`
left join `tag_texture` on `tag_texture`.`texture_id` = `textures`.`id`
left join `tags` on `tags`.`id` = `tag_texture`.`tag_id`
left join `size_texture` on `size_texture`.`texture_id` = `textures`.`id`
left join `sizes` on `sizes`.`id` = `size_texture`.`size_id` 
WHERE ( (textures.name LIKE '%artwork%' 
OR categories.name LIKE '%artwork%' 
OR tags.name LIKE '%artwork%'   
OR sizes.name LIKE '%artwork%')  AND  (textures.name LIKE '%super%' 
OR categories.name LIKE '%super%'   
OR tags.name LIKE '%super%' 
OR sizes.name LIKE '%super%')  AND  (textures.name LIKE '%master%' 
OR categories.name LIKE '%master%'  
OR tags.name LIKE '%master%'    
OR sizes.name LIKE '%master%') ) AND `textures`.`is_published` = 1  group by `tags`.`name`, `categories`.`name`, `sizes`.`name`, `textures`.`id`

在这个例子中，artwork 是一个类别，master 和 super 是标签.问题是，如果我重复任何表，都找不到结果:

In this example, artwork is a category, master and super are tags. The problem is that if I repeat any of the tables, no results are found:

1. 艺术品(类别)+ 大师(标签)作品
2. 艺术作品
3. artwork + master + super 不起作用 - 它应该打印所有具有艺术品作为类别和 2 个标签(master 和 super)的纹理，但它没有.

提前致谢.

为清晰起见进行

我的目标是能够在这些表中搜索数据，这样我就可以组合同一个表的多个实例.例如，如果我搜索artwork super master"，它应该返回我所有的纹理，类别为artwork"(因为它是唯一找到这个词的地方)和标签super"和master"(它们都是).

My goal is to be able to search for data within those tables, in a way that I can combine multiple instances of the same table. For instance, if I search for "artwork super master", it should return all my textures with category "artwork" (because it's the only place where the word is found) and the tags "super" and "master" (both of them).

目前使用此查询，我可以从这些表中的任何一个中进行搜索，但前提是我的搜索未在同一个表中找到 2 个以上的内容.因此，搜索类别 + 纹理名称 + 标签 + 大小是可行的，但搜索发现为 2 个标签的内容却失败了.

Currently with this query I'm able to search from within any of those tables, but only if my search doesn't find 2+ things in the same table. So, searching for a category + a texture name + a tag + a size works, but searching for something which is found as 2 tags fails.

推荐答案

我重新思考了一下，也许这种方法更好:

I had a rethink and perhaps this approach is better:

SELECT T1.id
FROM 
(  
select textures.id 
from textures
WHERE textures.name LIKE '%texture1%' 
UNION 
select textures.id 
from textures
join category_texture on category_texture.texture_id = textures.id
join categories on categories.id = category_texture.category_id
WHERE categories.name LIKE '%texture1%'
UNION
select textures.id 
from textures
join tag_texture on tag_texture.texture_id = textures.id
join tags on tags.id = tag_texture.tag_id
WHERE tags.name LIKE '%texture1%'
UNION 
select textures.id 
from textures
join size_texture on size_texture.texture_id = textures.id
join sizes on sizes.id = size_texture.size_id 
WHERE sizes.name LIKE '%texture1%'
) AS T1
JOIN 
(  
select textures.id 
from textures
WHERE textures.name LIKE '%category2%' 
UNION 
select textures.id 
from textures
join category_texture on category_texture.texture_id = textures.id
join categories on categories.id = category_texture.category_id
WHERE categories.name LIKE '%category2%'
UNION
select textures.id 
from textures
join tag_texture on tag_texture.texture_id = textures.id
join tags on tags.id = tag_texture.tag_id
WHERE tags.name LIKE '%category2%'
UNION 
select textures.id 
from textures
join size_texture on size_texture.texture_id = textures.id
join sizes on sizes.id = size_texture.size_id 
WHERE sizes.name LIKE '%category2%'
) AS T2
ON T1.id = T2.id
JOIN
(  
select textures.id 
from textures
WHERE textures.name LIKE '%tag3%' 
UNION 
select textures.id 
from textures
join category_texture on category_texture.texture_id = textures.id
join categories on categories.id = category_texture.category_id
WHERE categories.name LIKE '%tag3%'
UNION
select textures.id 
from textures
join tag_texture on tag_texture.texture_id = textures.id
join tags on tags.id = tag_texture.tag_id
WHERE tags.name LIKE '%tag3%'
UNION 
select textures.id 
from textures
join size_texture on size_texture.texture_id = textures.id
join sizes on sizes.id = size_texture.size_id 
WHERE sizes.name LIKE '%tag3%'
) AS T3
ON T1.id = T3.id

只需添加更多/更少 JOIN...ON T1.id = Tn.id 部分以匹配您的参数.

Just add more/less JOIN...ON T1.id = Tn.id sections to match your parameters.

这是一个显示它正在执行的小提琴:http://sqlfiddle.com/#!9/9abe6/1

Here is a fiddle to show it executing: http://sqlfiddle.com/#!9/9abe6/1

这篇关于带有多个关键字的 WHERE LIKE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！