CODEWITHSUNDEEP
javascriptmysqlnode.jssequelize.jswhere-clause
amaster
amaster

Reputation: 2163

Dynamically Building Where Clauses with Sequelize and Multiple OR Operators

The desired MySQL query:

SELECT
  *
FROM
  myTable
WHERE 
  (
    JSON_CONTAINS(access,'"a"','$.owner') OR
    JSON_CONTAINS(access,'"b"','$.owner')
  ) AND
  (
    JSON_CONTAINS(access,'"c"','$.moderator') OR
    JSON_CONTAINS(access,'"d"','$.moderator')
  )

A snippet of some of the logic that dynamically builds the filters:

const args = {x: ["a", "b"], y: ["c", "d"]}
let where = {}
// more code
if (args.x && Array.isArray(args.x)) {
  const orWhere = args.x.map(x => sequelize.fn('JSON_CONTAINS', sequelize.col('access'), `"${x}"`,'$.owner'))
  where = {
    ...where,
    [Op.or]: orWhere
  }
}
if (args.y && Array.isArray(args.y)) {
  const orWhere = args.y.map(y => sequelize.fn('JSON_CONTAINS', sequelize.col('access'), `"${y}"`,'$.moderator'))
  where = {
    ...where,
    [Op.or]: orWhere
  }
}
// more code
db.contacts.findAll({where})

The problem with doing this is that when args.x and args.y are both provided then the [Op.or]: orWhere from the second statement overrides what is already in the where object.

I have read through the Sequelize WHERE docs, but have yet to find a solution. It would be simple if I was not doing complex JSON query functions.

NOTE: Yes, I am preventing against injection but that part is left out intentionally to shorten the code

EDIT: as @Anatoly suggested, use sequelize.fn instead of literal, but left with the same condition of overwriting where[Op.or] in second if condition

Upvotes: 1

Views: 2109

Answers (1)

Anatoly
Anatoly

Reputation: 22758

You can use [Op.and] with a array of conditions just like you did with [Op.or]:

if ((args.x && Array.isArray(args.x)) ||
    (args.y && Array.isArray(args.y))) {
  where[Op.and] = []
  if (args.x && Array.isArray(args.x)) {
    const orWhere = args.x.map(x => sequelize.fn('JSON_CONTAINS',   sequelize.col('access'), `"${x}"`,'$.owner'))
    where[Op.and].push({
      [Op.or]: orWhere
    })
  }
  if (args.y && Array.isArray(args.y)) {
    const orWhere = args.y.map(y => sequelize.fn('JSON_CONTAINS', sequelize.col('access'), `"${y}"`,'$.moderator'))
    where[Op.and].push({
      [Op.or]: orWhere
    })
  }
}

Upvotes: 3

Related Questions