我使用了一些样板代码(如下),该代码创建了我希望全文搜索_search
的所有列(在中指定)的标准化tsvector 列searchObjects
。
在大多数情况下,这很好。我将其与Sequelize结合使用,因此查询如下:
const articles = await Article.findAndCountAll({
where: {
[Sequelize.Op.and]: Sequelize.fn(
'article._search @@ plainto_tsquery',
'english',
Sequelize.literal(':query')
),
[Sequelize.Op.and]: { status: STATUS_TYPE_ACTIVE }
},
replacements: { query: q }
});
搜索索引设置:
const vectorName = '_search';
const searchObjects = {
articles: ['headline', 'cleaned_body', 'summary'],
brands: ['name', 'cleaned_about'],
products: ['name', 'cleaned_description']
};
module.exports = {
up: async queryInterface =>
await queryInterface.sequelize.transaction(t =>
Promise.all(
Object.keys(searchObjects).map(table =>
queryInterface.sequelize
.query(
`
ALTER TABLE ${table} ADD COLUMN ${vectorName} TSVECTOR;
`,
{ transaction: t }
)
.then(() =>
queryInterface.sequelize.query(
`
UPDATE ${table} SET ${vectorName} = to_tsvector('english', ${searchObjects[
table
].join(" || ' ' || ")});
`,
{ transaction: t }
)
)
.then(() =>
queryInterface.sequelize.query(
`
CREATE INDEX ${table}_search ON ${table} USING gin(${vectorName});
`,
{ transaction: t }
)
)
.then(() =>
queryInterface.sequelize.query(
`
CREATE TRIGGER ${table}_vector_update
BEFORE INSERT OR UPDATE ON ${table}
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(${vectorName}, 'pg_catalog.english', ${searchObjects[
table
].join(', ')});
`,
{ transaction: t }
)
)
.error(console.log)
)
)
),
down: async queryInterface =>
await queryInterface.sequelize.transaction(t =>
Promise.all(
Object.keys(searchObjects).map(table =>
queryInterface.sequelize
.query(
`
DROP TRIGGER ${table}_vector_update ON ${table};
`,
{ transaction: t }
)
.then(() =>
queryInterface.sequelize.query(
`
DROP INDEX ${table}_search;
`,
{ transaction: t }
)
)
.then(() =>
queryInterface.sequelize.query(
`
ALTER TABLE ${table} DROP COLUMN ${vectorName};
`,
{ transaction: t }
)
)
)
)
)
};
问题在于,因为代码在的每个数组中合并两个列searchObjects
,所以要存储的是每个数组中所有列的组合索引。
例如在articles
桌子上:'headline', 'cleaned_body', 'summary'
都是单个生成_search
向量的一部分。
因此,我无法真正按ONLY headline
或ONLY cleaned_body
等进行搜索。我希望能够分别搜索每个列,也可以一起搜索。
用例是在我的搜索搜索栏中,我只想在标题上搜索。但是,我想在搜索结果页面上搜索中指定的所有列searchObjects
。
有人可以给我提示我需要更改的内容吗?我应该为每列创建一个新的tsvector吗?
如果有人好奇,可以按照以下方法为每一列创建tsvector:
try {
for (const table in searchObjects) {
for (const col of searchObjects[table]) {
await queryInterface.sequelize.query(
`ALTER TABLE ${table} ADD COLUMN ${col + vectorName} TSVECTOR;`,
{ transaction }
);
await queryInterface.sequelize.query(
`UPDATE ${table} SET ${col + vectorName} = to_tsvector('english', ${col});`,
{ transaction }
);
await queryInterface.sequelize.query(
`CREATE INDEX ${table}_${col}_search ON ${table} USING gin(${col +
vectorName});`,
{ transaction }
);
await queryInterface.sequelize.query(
`CREATE TRIGGER ${table}_${col}_vector_update
BEFORE INSERT OR UPDATE ON ${table}
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(${col +
vectorName}, 'pg_catalog.english', ${col});`,
{ transaction }
);
}
}
await transaction.commit();
} catch (err) {
await transaction.rollback();
throw err;
}