Conditional based where condition in sequelize Node JS

Question

I am trying to execute a query in sequelize, in which the where conditions have to be added based on the request parameters

router.get("/getsearchdata",(req, res) => { var searchquery =[ {model:ProvidersCategories,attributes:["category_id"], include: [ {model:Categories,attributes:["category_id","category_name"]}, ] } ] if(req.query.agency && req.query.agency!=''){ searchquery.where = { $and: [ {agencyName: {$like: '%' + req.query.agency + '%'}}, ] } } Providers.findAll({ include:searchquery }).then(providerData =>{ if(!providerData) { res.status(404).send({reason:'no data found'}); } else { res.status(200).send(providerData); } }); });

I have three tables "providers", "categories" and "provider_Categories". I am trying to join the three tables and get the providers. In the request i.e req, if there is an input req.query.agency != '' then an if condition has to added where agencyName like "% + req.query.agency + %", if input req.query.agency == '' then there should be no if condition. For which I have tried the above code. But the where condition is not getting concatenated to the main query even if there is input req.query.agency

What is the right way to write where conditionally

Konstantyn Kulakov · Accepted Answer · 2019-07-11 12:17:11Z

4

Your variable searchquery is an array and you can't assign a where property to it. What you should do, is just passing a where object to the query options object:

 router.get("/getsearchdata", (req, res) => { var whereClause; if (req.query.agency && req.query.agency != '') { whereClause = {agencyName: {$like: '%' + req.query.agency + '%'}}; } Providers.findAll({ include: [ { model: ProvidersCategories, attributes: ["category_id"], include: [ {model: Categories, attributes: ["category_id", "category_name"]}, ] } ], where: whereClause }).then(providerData => { if (!providerData) { res.status(404).send({reason: 'no data found'}); } else { res.status(200).send(providerData); } }); });

answered Jul 11, 2019 at 12:17

Konstantyn Kulakov

1163 bronze badges

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

4 Comments

rji rji Over a year ago

The solution is working fine but I am trying to add one more if condition if (req.query.insurance && req.query.insurance != '') { whereClause = whereClause+{'ProviderCategories->Category.category_name' : req.query.category}; } and it is giving the error as Unhandled rejection Error: Support for {where: 'raw query'} has been removed.

Konstantyn Kulakov Over a year ago

Since whereClause is an object and not a string, you need to use it's properties and not just appending strings. If I understood you correcly, you need something like: whereClause['ProviderCategories->Category.category_name'] = req.query.category;

Konstantyn Kulakov Over a year ago

But if you want to apply a condition to a Category object, you should define a separate variable, and pass it to the where attribute inside Category: var categoryWhereClause = {category_name: req.query.category}; ... {model: Categories, attributes: ["category_id", "category_name"], where: categoryWhereClause},

inane Over a year ago

And if your query have empty parameter? I need to do one query with and without "where" condition.

Collectives™ on Stack Overflow

Conditional based where condition in sequelize Node JS

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related