Dynamically Building Where Clauses with Sequelize and Multiple OR Operators

Question

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

Didn't you try something like sequelize.fn('JSON_CONTAINS', sequelize.col('access'), '"a"','$.owner')? — Anatoly
– Anatoly, Commented May 18, 2020 at 22:36
@Anatoly, that would replace the literal(...) but still not solve the problem. I will update it with your recomendation. — amaster
– amaster, Commented May 18, 2020 at 22:39

Anatoly · Accepted Answer · 2020-05-18 22:48:28Z

3

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
    })
  }
}

answered May 18, 2020 at 22:48

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

amaster Over a year ago

That should do it... I will test it here soon. I don't know why it was so hard for me to get to that point

Collectives™ on Stack Overflow

Dynamically Building Where Clauses with Sequelize and Multiple OR Operators

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related