如何使用 Sequelize 执行带有条件 where 参数的搜索 [英] How to perform a search with conditional where parameters using Sequelize
问题描述
通常每当我为 SQL 编写搜索查询时,我都会做类似的事情:
Usually whenever I write a search query for SQL, I do something similar to this:
SELECT * FROM users u
WHERE (@username IS NULL OR u.username like '%' + @username + '%')
AND (@id IS NULL OR u.id = @id)
这基本上模拟了一个条件 WHERE 子句.如果提供了@searchParam,我们只想将@searchParam 与列进行比较.
Basically this simulates a conditional WHERE clause. We only want to compare @searchParam to the column if @searchParam was provided.
有没有办法使用 Sequelize 复制它?
Is there a way to replicate this using Sequelize?
这是我失败的最佳尝试:
Here is my best attempt which fails:
models.user.findAll({
where: {
username: searchParams.username || models.sequelize.col('user.username'),
id: searchParams.id || models.sequelize.col('user.id')
}
})
更新:我找到了一种方法,但感觉像是一种解决方法.我敢肯定必须有一种更优雅的方式.这可行,但很难看:
UPDATE: I found a way to do it, but it feels like a workaround. I'm certain there has to be a more elegant way. This works, but is ugly:
models.user.findAll({
where: [
'(? IS NULL OR "user"."username" LIKE ?) AND (? IS NULL OR "user"."id" = ?)',
searchParams.username,
`%${searchParams.username}%`,
searchParams.id,
searchParams.id
]
})
推荐答案
你可以只准备需要条件的对象.简单易懂
You can just prepare object with needed conditions. Simple and easy to understand
var whereStatement = {};
if(searchParams.id)
whereStatement.id = searchParams.id;
if(searchParams.username)
whereStatement.username = {$like: '%' + searchParams.username + '%'};
models.user.findAll({
where: whereStatement
});
这篇关于如何使用 Sequelize 执行带有条件 where 参数的搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!