我有下面的表格，我试图找到如何
得到的结果是商品ID数组或商品行，
同时匹配ArticleTag0和ArticleTag1表中的标签'hiphop'，'rock'，'single'。
现在我使用下面的代码，
获取以不同标记类型标记的每个商品ID数组，
例如，如果在文章x Tag0中标记的文章ID行为[0，2，4，6]，而文章x Tag1为[0，4，9]，
然后比较每个数组获取上面两个数组中的数字get，[0，4]。
我想知道有没有更好的查询一次选择它们？考虑表现
表
CREATE TABLE IF NOT EXISTS "Article"(
"ArticleId" SERIAL NOT NULL,
"PublishDate" timestamp without time zone,
"Active" bit NOT NULL,
PRIMARY KEY ("ArticleId")
);
CREATE TABLE IF NOT EXISTS "Tag0"(
"TagId" SERIAL NOT NULL,
"Name" varchar,
"Active" bit NOT NULL,
PRIMARY KEY ("TagId")
);
CREATE TABLE IF NOT EXISTS "Tag1"(
"TagId" SERIAL NOT NULL,
"Name" varchar,
"Active" bit NOT NULL,
PRIMARY KEY ("TagId")
);
CREATE TABLE IF NOT EXISTS "ArticleTag0"(
"ArticleTagId" SERIAL NOT NULL,
"ArticleId" integer NOT NULL,
"TagId" integer NOT NULL,
FOREIGN KEY ("ArticleId") REFERENCES "Article" ("ArticleId") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("TagId") REFERENCES "Tag0" ("TagId") ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY ("ArticleTagId")
);
CREATE TABLE IF NOT EXISTS "ArticleTag1"(
"ArticleTagId" SERIAL NOT NULL,
"ArticleId" integer NOT NULL,
"TagId" integer NOT NULL,
FOREIGN KEY ("ArticleId") REFERENCES "Article" ("ArticleId") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("TagId") REFERENCES "Tag1" ("TagId") ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY ("ArticleTagId")
);
码
用户输入参数
inputGenres（Tag0）-['hiphop'，'rock']
inputReleaseType（Tag1）-['single']
...
// inputGenres
var inputGenresArticleIdList = [];
if (inputGenres[0] == 'all') {
var query = 'SELECT * FROM "Article"';
var params = [];
var selectArticle = yield crudDatabase(db,query,params);
if (typeof selectArticle.error !== 'undefined') {
response.meta.code = '500';
} else {
for (var i = 0; i < selectArticle.result.rows.length; i++) {
inputGenresArticleIdList.push(selectArticle.result.rows[i].ArticleId);
}
}
} else {
var query = 'SELECT DISTINCT ON ("ArticleId") * FROM "ArticleTag0" LEFT OUTER JOIN "Tag0" ON ("ArticleTag0"."TagId" = "Tag0"."TagId") WHERE "Name" IN (';
for (var i = 0; i < inputGenres.length; i++) {
if (i > 0) {
query += ',';
}
query += '$' + (i + 1);
}
query += ')';
var params = inputGenres;
var selectArticleTag0 = yield crudDatabase(db,query,params);
if (typeof selectArticleTag0.error !== 'undefined') {
response.meta.code = '500';
} else {
for (var i = 0; i < selectArticleTag0.result.rows.length; i++) {
inputGenresArticleIdList.push(selectArticleTag0.result.rows[i].ArticleId);
}
}
}
console.log(inputGenresArticleIdList);
// end: inputGenres
// inputReleaseType
var inputReleaseTypeArticleIdList = [];
if (inputReleaseType[0] == 'all') {
var query = 'SELECT * FROM "Article"';
var params = [];
var selectArticle = yield crudDatabase(db,query,params);
if (typeof selectArticle.error !== 'undefined') {
response.meta.code = '500';
} else {
for (var i = 0; i < selectArticle.result.rows.length; i++) {
inputReleaseTypeArticleIdList.push(selectArticle.result.rows[i].ArticleId);
}
}
} else {
var query = 'SELECT DISTINCT ON ("ArticleId") * FROM "ArticleTag4" LEFT OUTER JOIN "Tag4" ON ("ArticleTag4"."TagId" = "Tag4"."TagId") WHERE "Name" IN (';
for (var i = 0; i < inputReleaseType.length; i++) {
if (i > 0) {
query += ',';
}
query += '$' + (i + 1);
}
query += ')';
var params = inputReleaseType;
var selectArticleTag4 = yield crudDatabase(db,query,params);
if (typeof selectArticleTag4.error !== 'undefined') {
response.meta.code = '500';
} else {
for (var i = 0; i < selectArticleTag4.result.rows.length; i++) {
inputReleaseTypeArticleIdList.push(selectArticleTag4.result.rows[i].ArticleId);
}
}
}
console.log(inputReleaseTypeArticleIdList);
// end: inputReleaseType
... then loop each array and compare
最佳答案
通常，最好使用一条SQL语句来完成这项工作：它不太复杂，节省了往返服务器的往返时间，并利用了数据库的算法和优化功能。
您可以使用以下语句获得所需的结果：
select *
from "Article" a
where exists (
select 1
from "ArticleTag0" at0,
"Tag0" t0
where at0."ArticleId" = a."ArticleId"
and t0."TagId" = at0."TagId"
and t0."Name" in ('hiphop','rock')
)
and exists (
select 1
from "ArticleTag1" at1,
"Tag1" t1
where at1."ArticleId" = a."ArticleId"
and t1."TagId" = at1."TagId"
and t1."Name" in ('single')
);
当然，您仍然需要更改文字（例如
'hiphop'）以绑定变量（$i）。标记的“全部”选项可以通过用exists(...)替换适当的true块来完成。但我建议您对架构进行一些重新设计。如何用数组表示文章的标签？
create table article(
articleId serial primary key,
publishDate timestamp without time zone,
active boolean, -- clearer that 'bit'
genres text[], -- array of genre tags
releases text[] -- array of release tags
);
优点：
文章和标签之间不需要中间表（例如
ArticleTag0）。更简单的查询。
可以在支持索引的情况下使用数组的重叠操作。
让我们插入一些值：
tags=# insert into article(publishDate,active,genres,releases) values ('2015-09-01',true,'{"hiphop"}','{"single"}');
INSERT 0 1
tags=# insert into article(publishDate,active,genres,releases) values ('2015-10-01',true,'{"rock","blues"}','{"album"}');
INSERT 0 1
tags=# insert into article(publishDate,active,genres,releases) values ('2015-11-01',true,'{"pop"}','{"ep"}');
INSERT 0 1
tags=# select * from article;
articleid | publishdate | active | genres | releases
-----------+---------------------+--------+--------------+----------
1 | 2015-09-01 00:00:00 | t | {hiphop} | {single}
2 | 2015-10-01 00:00:00 | t | {rock,blues} | {album}
3 | 2015-11-01 00:00:00 | t | {pop} | {ep}
(3 rows)
现在查询尽可能简单明了（
&&运算符表示“重叠”）：tags=# select * from article where genres && '{"hiphop","rock"}' and releases && '{"single"}';
articleid | publishdate | active | genres | releases
-----------+---------------------+--------+----------+----------
1 | 2015-09-01 00:00:00 | t | {hiphop} | {single}
(1 row)
它还简化了查询文本的构造：使用
select * from article where genres && $1 and releases && $2;并为$1和$2生成适当的字符串。为了加快查询速度，您可以创建两个GIN索引，它们支持数组的
&&运算符：create index on article using gin(genres);
create index on article using gin(releases);