如何使用 SequelizeJS 实现搜索功能? [英] How to implement search feature using SequelizeJS?
问题描述
我有一个 Ticket 模型和一个 Comment 模型.Ticket 与 Comment 模型有 hasMany 关系.我想通过关键字搜索门票.关键字将与工单模型的主题属性和评论模型的正文属性进行匹配.
I have a Ticket model and a Comment model. The Ticket has a hasMany relationship to Comment model. I want to search tickets by a keyword. The keyword will be matched againts the subject attribute of the ticket model and the body attribute of the comment model.
下面的代码不起作用:
var options = {
where: {
$or: [
{
subject: {
like: '%' + query + '%'
},
},
{
'Comment.body': {
like: '%' + query + '%'
},
}
]
},
include: [
{ model: Comment },
]
};
Ticket.findAll(options);
这是错误:可能未处理的 SequelizeDatabaseError:列 Ticket.Comment.body 不存在"
This is the error: "Possibly unhandled SequelizeDatabaseError: column Ticket.Comment.body does not exist"
我也尝试了下面的代码,但它也不起作用:
I also tried the code below but it also doesn't work:
var options = {
where: {
CompanyId: req.company.id,
$or: [
{
subject: {
like: '%' + query + '%'
},
},
sequelize.cast(sequelize.col('comment.body'), 'TEXT', 'LIKE', '%' + query + '%')
]
},
include: [
{ model: Comment, as: 'comment', where: {} },
]
};
Ticket.findAll(options);
错误是:可能未处理的错误:评论(comment)与票证无关!"
The error is: "Possibly unhandled Error: Comment (comment) is not associated to Ticket!"
还有这个:
var options = {
where: {
CompanyId: req.company.id,
$or: [
{
subject: {
like: '%' + query + '%'
},
},
sequelize.where(sequelize.col('Comments.body'), 'LIKE', '%' + query + '%')
]
},
include: [
{ model: Comment},
]
};
Ticket.findAll(options);
错误:可能未处理的 SequelizeDatabaseError:缺少表评论"的 FROM 子句条目"
Error: "Possibly unhandled SequelizeDatabaseError: missing FROM-clause entry for table "Comments""
我使用的是 SequelizeJS 2.0.4 版
I'm using SequelizeJS version 2.0.4
我在 Github 上的 Sequelizejs 存储库中看到了这些相关问题:
I saw these related issues on the Sequelizejs repository on Github:
- https://github.com/sequelize/sequelize/issues/3261
- https://github.com/sequelize/sequelize/issues/3095
- https://github.com/sequelize/sequelize/issues/3527
有人知道解决办法吗?提前致谢!
Anyone knows a solution? Thanks in advance!
推荐答案
对你来说可能有点太晚了,但对其他人来说;#3095 更新了位一个解决方案:
Probably a bit too late for you, but for anyone else; #3095 was updated with a bit of a solution:
var options = {
where: {
$or: [
{ 'subject': { like: '%' + query + '%' } },
{ '$Comment.body$': { like: '%' + query + '%' } }
]
},
include: [{ model: Comment }]
};
诀窍在于那些美元符号,尽管仍然存在问题 在设置 limit
或使用 findAndCountAll()
查询时使用此解决方案.
The trick is in those dollar signs, though there are still problems with this solution when limit
is set or when querying with findAndCountAll()
.
这篇关于如何使用 SequelizeJS 实现搜索功能?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!