CODEWITHSUNDEEP
sequelize.js
James111
James111

Reputation: 15903

Sequelize optional where clause parameters?

This is one thing that really annoys me! I have to write 2 different functions for almost the same query!

Say I've got an API that returns posts that are associated to a particular typeId and cityId. To get ALL posts that are associated to typeId 1 OR 2, OR 3 and cityId 1 I would parse the following to my sequelize findAll query:

$or: [{typeId: 1}, {typeId: 2}, {typeId: 3}]
cityId: 1

But say I want to get all post where cityId = 1 andOr typeId = 1,2,3,4,5,6,7,8,9,10,etc... I cannot do something like:

var types = [{typeId: 1}, {typeId: 2}, {typeId: 3}]
Post.findAll({
     where: {
          if (types != []) $or: types,
          cityId: 1
      }

So instead I have to make a new query that won't include the $or: types where clause...Because if I parse an empty types array I get a weird sql output:

WHERE 0 = 1 AND `post`.`cityId` = '1'

Notice how it's outputting 0 = 1?! No idea why

Upvotes: 10

Views: 16221

Answers (4)

Manoj Mulakala
Manoj Mulakala

Reputation: 16

I discovered a handy trick a while back that I'd like to share. This trick is particularly useful when you have multiple parameters (like name, age, gender, phone number, account number, date of birth, etc.) that are all optional. That is, the user can provide either all parameters or just one. I found using 'if-else' statements to handle this scenario was not scalable. So, here's an alternative approach you can consider:

let name = req.body.name ? { name: req.body.name } : undefined;
let gender = req.body.gender ? { gender: req.body.gender } : undefined;
let phoneNumber = req.body.phoneNumber ? { phoneNumber: req.body.phoneNumber } : undefined;

let users = await User.findAll({
   where: {
      [Op.and]: [
         name,
         gender,
         phoneNumber
      ]
   }
})

this will work only with Op operator such as and , I am not sure about other operators. Please let me know if you have questions on this.

Upvotes: 0

user5383152
user5383152

Reputation:

You could build the where object beforehand. Here's a simple example

// Get typeIds from whatever source you have

// Here's an example
var typeIds = [1, 2, 3];

// Or you could try this to build a query without typeIds
// var typeIds = [];

var whereCondition = {};

if (typeIds.length > 0) {
    whereCondition['$or'] = typeIds.map(function(id) {
        return {
            typeId: id
        };
    })
};

whereCondition['cityId'] = 1;

console.log(whereCondition);

Post.findAll(whereCondition).then(function(posts) {
    // The rest of your logic
});

Upvotes: 10

Marko Rochevski
Marko Rochevski

Reputation: 1275

You can do this:

Post.findAll({
     where: {
          cityId: 1,
          ...(types && types.length && {
              types
          })
      }

types attr will only be evaluated in the expression if the array has elements.

Upvotes: 5

mahmoud miz
mahmoud miz

Reputation: 299

i had some similar situation , and i used template literals to define empty string as default if the field was undefined.

User.findOne({
where: {
  [Op.or]: [
    { email: `${req.body.email || ""}` },
    { username: `${req.body.username || ""}` },
  ],
},

})

Upvotes: 0

Related Questions