Sequelize Where if not null

Question

Lets say I want to do a select command with

WHERE ID=2134

But if the user does not provide the id then it should just not bother with the WHERE ID (since it is null)

How can I handle this with Sequelize?

Answer 1

let where = {}

if (req.query.id) {
  where = {
    id: req.query.id
  }
}

Model.findAll({
  where: where
});

Answer 2

I read the title and it says, where if not null. However, I show IS NOT NULL and IS NULL

I attachment a example.

    const cars = await Car.findAll({
    where: {
        userId: {
            [Op.in]: myUserIds, // array like userId IN [2, 3, 4]
        },
        action: 'start', // like: action = 'start'
        sellDate: {
            [Op.not]: null, // Like: sellDate IS NOT NULL
        },
        status: {
            [Op.is]: null, // Like: status IS NULL
        }
    },
    order: [
        ['id', 'DESC'] // Like: ORDER BY id DESC
    ],
    limit: 5, 
    offset: 1
});

For details I attachment the file node_modules/sequelize/types/lib/operators.d.ts

I hope help you.

/**
 * object that holds all operator symbols
 */
declare const Op: {
  /**
   * Operator -|- (PG range is adjacent to operator)
   *
   * ```js
   * [Op.adjacent]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * -|- [1, 2)
   * ```
   */
  readonly adjacent: unique symbol;
  /**
   * Operator ALL
   *
   * ```js
   * [Op.gt]: {
   *  [Op.all]: literal('SELECT 1')
   * }
   * ```
   * In SQL
   * ```sql
   * > ALL (SELECT 1)
   * ```
   */
  readonly all: unique symbol;
  /**
   * Operator AND
   *
   * ```js
   * [Op.and]: {a: 5}
   * ```
   * In SQL
   * ```sql
   * AND (a = 5)
   * ```
   */
  readonly and: unique symbol;
  /**
   * Operator ANY ARRAY (PG only)
   *
   * ```js
   * [Op.any]: [2,3]
   * ```
   * In SQL
   * ```sql
   * ANY ARRAY[2, 3]::INTEGER
   * ```
   *
   * Operator LIKE ANY ARRAY (also works for iLike and notLike)
   *
   * ```js
   * [Op.like]: { [Op.any]: ['cat', 'hat']}
   * ```
   * In SQL
   * ```sql
   * LIKE ANY ARRAY['cat', 'hat']
   * ```
   */
  readonly any: unique symbol;
  /**
   * Operator BETWEEN
   *
   * ```js
   * [Op.between]: [6, 10]
   * ```
   * In SQL
   * ```sql
   * BETWEEN 6 AND 10
   * ```
   */
  readonly between: unique symbol;
  /**
   * With dialect specific column identifiers (PG in this example)
   *
   * ```js
   * [Op.col]: 'user.organization_id'
   * ```
   * In SQL
   * ```sql
   * = "user"."organization_id"
   * ```
   */
  readonly col: unique symbol;
  /**
   * Operator <@ (PG array contained by operator)
   *
   * ```js
   * [Op.contained]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * <@ [1, 2)
   * ```
   */
  readonly contained: unique symbol;
  /**
   * Operator @> (PG array contains operator)
   *
   * ```js
   * [Op.contains]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * @> [1, 2)
   * ```
   */
  readonly contains: unique symbol;
  /**
   * Operator LIKE
   *
   * ```js
   * [Op.endsWith]: 'hat'
   * ```
   * In SQL
   * ```sql
   * LIKE '%hat'
   * ```
   */
  readonly endsWith: unique symbol;
  /**
   * Operator =
   *
   * ```js
   * [Op.eq]: 3
   * ```
   * In SQL
   * ```sql
   * = 3
   * ```
   */
  readonly eq: unique symbol;
  /**
   * Operator >
   *
   * ```js
   * [Op.gt]: 6
   * ```
   * In SQL
   * ```sql
   * > 6
   * ```
   */
  readonly gt: unique symbol;
  /**
   * Operator >=
   *
   * ```js
   * [Op.gte]: 6
   * ```
   * In SQL
   * ```sql
   * >= 6
   * ```
   */
  readonly gte: unique symbol;

  /**
   * Operator ILIKE (case insensitive) (PG only)
   *
   * ```js
   * [Op.iLike]: '%hat'
   * ```
   * In SQL
   * ```sql
   * ILIKE '%hat'
   * ```
   */
  readonly iLike: unique symbol;
  /**
   * Operator IN
   *
   * ```js
   * [Op.in]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * IN [1, 2]
   * ```
   */
  readonly in: unique symbol;
  /**
   * Operator ~* (PG only)
   *
   * ```js
   * [Op.iRegexp]: '^[h|a|t]'
   * ```
   * In SQL
   * ```sql
   * ~* '^[h|a|t]'
   * ```
   */
  readonly iRegexp: unique symbol;
  /**
   * Operator IS
   *
   * ```js
   * [Op.is]: null
   * ```
   * In SQL
   * ```sql
   * IS null
   * ```
   */
  readonly is: unique symbol;
  /**
   * Operator LIKE
   *
   * ```js
   * [Op.like]: '%hat'
   * ```
   * In SQL
   * ```sql
   * LIKE '%hat'
   * ```
   */
  readonly like: unique symbol;
  /**
   * Operator <
   *
   * ```js
   * [Op.lt]: 10
   * ```
   * In SQL
   * ```sql
   * < 10
   * ```
   */
  readonly lt: unique symbol;
  /**
   * Operator <=
   *
   * ```js
   * [Op.lte]: 10
   * ```
   * In SQL
   * ```sql
   * <= 10
   * ```
   */
  readonly lte: unique symbol;
  /**
   * Operator !=
   *
   * ```js
   * [Op.ne]: 20
   * ```
   * In SQL
   * ```sql
   * != 20
   * ```
   */
  readonly ne: unique symbol;
  /**
   * Operator &> (PG range does not extend to the left of operator)
   *
   * ```js
   * [Op.noExtendLeft]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * &> [1, 2)
   * ```
   */
  readonly noExtendLeft: unique symbol;
  /**
   * Operator &< (PG range does not extend to the right of operator)
   *
   * ```js
   * [Op.noExtendRight]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * &< [1, 2)
   * ```
   */
  readonly noExtendRight: unique symbol;
  /**
   * Operator NOT
   *
   * ```js
   * [Op.not]: true
   * ```
   * In SQL
   * ```sql
   * IS NOT TRUE
   * ```
   */
  readonly not: unique symbol;
  /**
   * Operator NOT BETWEEN
   *
   * ```js
   * [Op.notBetween]: [11, 15]
   * ```
   * In SQL
   * ```sql
   * NOT BETWEEN 11 AND 15
   * ```
   */
  readonly notBetween: unique symbol;
  /**
   * Operator NOT ILIKE (case insensitive) (PG only)
   *
   * ```js
   * [Op.notILike]: '%hat'
   * ```
   * In SQL
   * ```sql
   * NOT ILIKE '%hat'
   * ```
   */
  readonly notILike: unique symbol;
  /**
   * Operator NOT IN
   *
   * ```js
   * [Op.notIn]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * NOT IN [1, 2]
   * ```
   */
  readonly notIn: unique symbol;
  /**
   * Operator !~* (PG only)
   *
   * ```js
   * [Op.notIRegexp]: '^[h|a|t]'
   * ```
   * In SQL
   * ```sql
   * !~* '^[h|a|t]'
   * ```
   */
  readonly notIRegexp: unique symbol;
  /**
   * Operator NOT LIKE
   *
   * ```js
   * [Op.notLike]: '%hat'
   * ```
   * In SQL
   * ```sql
   * NOT LIKE '%hat'
   * ```
   */
  readonly notLike: unique symbol;
  /**
   * Operator NOT REGEXP (MySQL/PG only)
   *
   * ```js
   * [Op.notRegexp]: '^[h|a|t]'
   * ```
   * In SQL
   * ```sql
   * NOT REGEXP/!~ '^[h|a|t]'
   * ```
   */
  readonly notRegexp: unique symbol;
  /**
   * Operator OR
   *
   * ```js
   * [Op.or]: [{a: 5}, {a: 6}]
   * ```
   * In SQL
   * ```sql
   * (a = 5 OR a = 6)
   * ```
   */
  readonly or: unique symbol;
  /**
   * Operator && (PG array overlap operator)
   *
   * ```js
   * [Op.overlap]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * && [1, 2)
   * ```
   */
  readonly overlap: unique symbol;
  /**
   * Internal placeholder 
   *
   * ```js
   * [Op.placeholder]: true
   * ```
   */
  readonly placeholder: unique symbol;
  /**
   * Operator REGEXP (MySQL/PG only)
   *
   * ```js
   * [Op.regexp]: '^[h|a|t]'
   * ```
   * In SQL
   * ```sql
   * REGEXP/~ '^[h|a|t]'
   * ```
   */
  readonly regexp: unique symbol;
  /**
   * Operator LIKE
   *
   * ```js
   * [Op.startsWith]: 'hat'
   * ```
   * In SQL
   * ```sql
   * LIKE 'hat%'
   * ```
   */
  readonly startsWith: unique symbol;
  /**
   * Operator << (PG range strictly left of operator)
   *
   * ```js
   * [Op.strictLeft]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * << [1, 2)
   * ```
   */
  readonly strictLeft: unique symbol;
  /**
   * Operator >> (PG range strictly right of operator)
   *
   * ```js
   * [Op.strictRight]: [1, 2]
   * ```
   * In SQL
   * ```sql
   * >> [1, 2)
   * ```
   */
  readonly strictRight: unique symbol;
  /**
   * Operator LIKE
   *
   * ```js
   * [Op.substring]: 'hat'
   * ```
   * In SQL
   * ```sql
   * LIKE '%hat%'
   * ```
   */
  readonly substring: unique symbol;
  /**
   * Operator VALUES
   * 
   * ```js
   * [Op.values]: [4, 5, 6]
   * ```
   * In SQL
   * ```sql
   * VALUES (4), (5), (6)
   * ```
   */
  readonly values: unique symbol;
};

export = Op;

Answer 3

Ok, I think I got this question wrong. So if there is an id param provided, when we return result filtered by this id, if it's not the return all results. something like this

function getData(dataId) {
  let conditions = {};

  if (dataId) {
    conditions.where = { id: dataId };
  }

  return Model.findAll(conditions);
}

// The record with id = 1234
getData(1234).then(function(record) {
  // record readed here
});

// All records
getData().then(function(records) {
  // record readed here
});

Answer 4

Basically your query should have

WHERE fk_id=1234 OR fk_id is null

so for sequilize it would

const Op = Sequelize.Op;

Model.findAll({
   where: {
      fk_id: {
        [Op.or]: [1234, null]
      }
   }
});

Here's the docs: http://docs.sequelizejs.com/manual/tutorial/querying.html#operators

Answer 5

Post.update({
  updatedAt: null,
}, {
  where: {
    deletedAt: {
      [Op.ne]: null
    }
  }
});