基于SQL索引的搜索

Question

我有一个名为Index的表，它有id和value列，其中id是自动增量的bigint，value是带英文单词的varchar。

我有一个名为Search的表，它与表Index有关系。对于每个搜索，您可以在一个名为Article的表中定义应该搜索哪些索引。

表Article还与表Index有关系。

定义这些关系的表是：

Searches_Indexes，包含id_search和id_index列。

Articles_Indexes，包含id_article和id_index列。

我想找到所有包含相同搜索索引的文章。

例如:我有一个Search，其中包含索引laptop和dell，我想检索包含这两个索引的所有Article，而不仅仅是一个。

到目前为止我有这样的想法：

SELECT ai.id_article 
FROM articles_indexes AS ai 

INNER JOIN searches_indexes AS si 
ON si.id_index = ai.id_index 

WHERE si.id_search = 1

如何使SQL只返回Articles和Search的所有Indexes

编辑：

样本数据：

条款：

id | name          | description           | ...
1  | 'Dell Laptop' | 'New Dell Laptop...'  | ...
2  | 'HP Laptop'   | 'Unused HP Laptop...' | ...
...

搜索：

id | name                 | id_user | ...
1  | 'Dell Laptop Search' | 5       | ...

指数：

id | value
1  | 'dell'
2  | 'laptop'
3  | 'hp'
4  | 'new'
5  | 'unused'
...

Articles_Indexes:

Article with id 1(戴尔笔记本电脑)有Indexes‘戴尔’，‘笔记本电脑’，‘新’。

Article with id 2(惠普笔记本电脑)有Indexes‘膝上型电脑’，' hp '，‘未使用’。

id_article | id_index
1          | 1
1          | 2
1          | 4
...
2          | 2
2          | 3
2          | 5
...

Searches_Indexes:

Search与id 1只包含两个Indexes，‘戴尔’和‘笔记本电脑’：

id_search | id_index
1         | 1
1         | 2

必需的输出：

id_article
1

Answer 1

如果我正确理解，您需要聚合和一个HAVING子句。假设索引表中没有重复条目：

SELECT ai.id_article 
FROM articles_indexes ai INNER JOIN
     searches_indexes si 
     ON si.id_index = ai.id_index 
WHERE si.id_search = 1
GROUP BY ai.id_article
HAVING COUNT(*) = (SELECT COUNT(*) FROM searches_indexes si2 WHERE si2.id_search = 1);

这将计算匹配的数量，并确保它与您要查找的数字相匹配。

我应该加上这个。如果您想同时查找所有搜索，我倾向于这样写：

SELECT si.id_search, ai.id_article 
FROM articles_indexes ai INNER JOIN
     (SELECT si.*, COUNT(*) OVER (PARTITION BY si.id_index) as cnt
      FROM searches_indexes si 
     ) si
     ON si.id_index = ai.id_index 
GROUP BY si.id_search, ai.id_article, si.cnt
HAVING COUNT(*) = si.cnt;

Answer 2

你可以比较数组。下面是一些例子：

create table article_index(id_article int, id_index int);
create table search_index(id_search int, id_index int);

insert into article_index
select generate_series(1,2), generate_series(1,10);
insert into search_index
select generate_series(1,2), generate_series(1,4);

select 
    id_article
from article_index
group by id_article
having array_agg(id_index) @> (select array_agg(id_index) from search_index where id_search = 2);

了解更多关于postgres中的数组。