Avoid/bypass query parameters if value is null on Sequelize/Express/Node.js

Question

This is my first question on the site, so bear with me if I commit a mistake, English is not my first language but I'll try to be as detailed as possible.

I am trying to query my database using the findAll.where method from Sequelize and some parameters coming from req.query. What I want to know is: How can I indicate Sequelize not to look for a paramater if it's undefined? (The user didn't set the parameter).

I won't post the controller, route or anything else because everything is working. Even this filtered search works if I set all the parameters. The problem comes when one of them is undefined, hence me setting default values for each variable. Is there any more proper way of doing this? Thank you in advance!

Here's my service

const { minPrice = 0, maxPrice = 1000000000, city = "", region = "", minMeters = 0, maxMeters = 1000000000, minAmbiances = 0, maxAmbiances = 100, minBedrooms = 0, maxBedrooms = 100, minBathrooms = 0, maxBathrooms = 100, minAntiquity = 0, maxAntiquity = 100000, isAvailable = true, propertyType = "casa", businessType = "venta", parking = true, orderColumn, orderDirection, page = 1, size = 5 } = filter;

  const result = await properties.findAll({
        where: {
          price: {
            [Op.between]: [+minPrice, +maxPrice]
          },
          city: {
            [Op.like]: `%${city}%`
          },
          region: {
            [Op.like]: `%${region}%`
          },
          sqMeters: {
            [Op.between]: [+minMeters, +maxMeters]
          },
          ambiances: {
            [Op.between]: [+minAmbiances, +maxAmbiances]
          },
          bedrooms: {
            [Op.between]: [+minBedrooms, +maxBedrooms]
          },
          bathrooms: {
            [Op.between]: [+minBathrooms, +maxBathrooms]
          },
          antiquity: {
            [Op.between]: [+minAntiquity, +maxAntiquity]
          },
          isAvailable,
          propertyType,
          businessType,
          parking
        },
        order: [
          [orderColumn, orderDirection]
        ],
        limit: +size,
        offset: +size * +page
      });

Setting default values to each variable coming from req.query

Answer 1

Yes you can do to handle that undefined param like:

create a function to check the query params:

const isQueryParam = async (param) => {
  if(param && param != undefined && param != '') {
    return true;
  }
  return false;
}

Your sequelize query will look like the following:

const { minPrice = 0, maxPrice = 1000000000, city = "", region = "", minMeters = 0, maxMeters = 1000000000, minAmbiances = 0, maxAmbiances = 100, minBedrooms = 0, maxBedrooms = 100, minBathrooms = 0, maxBathrooms = 100, minAntiquity = 0, maxAntiquity = 100000, isAvailable = true, propertyType = "casa", businessType = "venta", parking = true, orderColumn, orderDirection, page = 1, size = 5 } = filter;

let searchStr = {};
if(await isQueryParam(minPrice) && await isQueryParam(maxPrice)) {
  searchStr.price = {
    [Op.between]: [minPrice, maxPrice]
  }
}
if(await isQueryParam(city)) {
  searchStr.city = {
   [Op.like]: `%${city}%`
  }
}
if(await isQueryParam(region)) {
  searchStr.region = {
   [Op.like]: `%${region}%`
  }
}
if(await isQueryParam(minMeters) && await isQueryParam(maxMeters)) {
  searchStr.sqMeters = {
    [Op.between]: [minMeters, maxMeters]
  }
}
if(await isQueryParam(minAmbiances) && await isQueryParam(maxAmbiances)) {
  searchStr.ambiances = {
    [Op.between]: [minAmbiances, maxAmbiances]
  }
}
...
...
// same for all query params.

const result = await properties.findAll({
   where: searchStr,
   order: [
     [orderColumn, orderDirection]
   ],
   limit: +size,
   offset: +size * +page
});

Try this, I am sure it will handle your issue.