CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
palmeiira
palmeiira

Reputation: 45

Optional parameters on sequelize query

Good morning.

I'm quite new to NodeJS / sequelize world and I'm currently facing a problem while trying to display a dashboard on screen.

This dashboard has three filters: two dates (period), client name, and employee name. The user can select none, one, two, or all the filters and my database needs to work accordingly.

That being said, my problem is with Sequelize because I don't know how to treat this problem of parameters not being "always" there.

I've seen this question:

Sequelize optional where clause parameters?

but this answer doesn't work anymore. I also tried another way of building the where clause, but I failed on it as well (mainly due to sequelize operators). The last thing I tried was to make a single query with all parameters included but try to find some value (or flag) that would make sequelize ignore the parameter, for the case when the parameter was no there*, but it looks like Sequelize doesn't have anything like that.

* I've read a question here that has an answer saying that {} would do the trick but I tried that as well but didn't work.

In summary: I need to make a query that can "change" over time, for example:

Foo.findAll({
  where: { 
  id : 1,
  }
});

Foo.findAll({
  where: { 
  id {
   [Op.in] : [1,2,3,4,5]
  },
  name: "palmeiira",
  }
});

Do you know a way of doing it without the need of using a lot if / switch statements?

I'm currently using Sequelize v. 5.5.1.

Update

I tried doing as suggested by @Anatoly and created a function to build the parameters. It was something like that. (I tried a "smaller" version just to test)

async function test() {
  const where = {};
  where[Op.and] = [];
  where[Op.eq].push({
    id: {
    [Op.in]: [1,2,3] 
    }
  });

  return where;
}

I setted the return value to a const:

const query = await test()

And tried console.log(query) The result was: { [Symbol(and)]: [ { id: [Object] } ] }, which made me believe that the problem was parsing the Op part so i tried using 'Op.and' and 'Op.in' to avoid that and it solved this problem, but led to another on sequelize that said Invalid value

Do you have any idea where is my error ?

P.S.: @Anatoly very nice idea you gave me on original answer. Thank you very much.

Upvotes: 3

Views: 4357

Answers (1)

Anatoly
Anatoly

Reputation: 22758

If these three conditions should work together then you can use Op.and with an array of conditions:

const where = {}

if (datesFilter || clientNameFilter || employeenameFilter) {
  where[Op.and] = []
  if (datesFilter) {
    where[Op.and].push({
      dateField: {
        [Op.between]: [datesFilter.start, datesFilter.finish]
      }
    })
  }
  if (clientNameFilter) {
    where[Op.and].push({
      name: {
        [Op.iLike]: `%${clientNameFilter.value}%`
      }
    })
  }
  if (employeenameFilter) {
    where[Op.and].push({
      employeeName: {
        [Op.iLike]: `%${employeenameFilter.value}%`
      }
    })
  }
}

const dashboardItems = await DashboardItem.findAll({ where }, {
// some options here
})

If the conditions should work as alternatives then just replace Op.and with Op.or

Upvotes: 5

Related Questions