How do I implement a search function using SequelizeJS?
I have a Ticket model and a Comment model. The ticket hasMany has to do with the comment model. I want to search for tickets by keyword. The keyword will be matched against the ticket model object attribute and the comment model body attribute.
The code below doesn't work:
var options = {
where: {
$or: [
{
subject: {
like: '%' + query + '%'
},
},
{
'Comment.body': {
like: '%' + query + '%'
},
}
]
},
include: [
{ model: Comment },
]
};
Ticket.findAll(options);
This is an error: "Possibly unhandled SequelizeDatabaseError: Ticket.Comment.body column does not exist"
I've also tried the code below, but it doesn't work either:
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);
Error: "Possibly unhandled error: comment (comment) not associated with ticket!"
And this one:
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);
Error: "Possibly unhandled SequelizeDatabaseError: Missing FROM-clause entry for Comments table"
I am using SequelizeJS version 2.0.4
I've seen these related issues in the Sequelizejs Github repository:
- https://github.com/sequelize/sequelize/issues/3261
- https://github.com/sequelize/sequelize/issues/3095
- https://github.com/sequelize/sequelize/issues/3527
Does anyone know a solution? Thanks in advance!
source to share
It may be too late for you, but for someone else; # 3095 has been updated with a few solutions:
var options = {
where: {
$or: [
{ 'subject': { like: '%' + query + '%' } },
{ '$Comment.body$': { like: '%' + query + '%' } }
]
},
include: [{ model: Comment }]
};
The focus is on these dollar signs, although there are still issues with this solution when installing limit
or when prompted with findAndCountAll()
.
source to share