Sequelize Where如果非空

Question

假设我想使用 WHERE ID=2134 进行选择操作。但是，如果用户没有提供 ID，则不应该使用 WHERE ID（因为它为空）。 我如何在 Sequelize 中处理这种情况？

- Asfgasdf

---

你需要更清楚一些。你想要发生什么？你想要选择表中的所有内容吗？还是你想要完全拒绝请求并强制用户提供一个ID？ - alnorth29

检查用户输入是否存在并仅在存在时添加查询选项，这样不是更实际吗？您可以为queryOptions创建一个变量，根据需要进行条件调整，并将其直接传递到Sequelize查询中，而不是将参数本身传递到查询语句中。 - Cole Simchick

@Asfgasdf 你找到任何解决方案了吗？ - Jatin Kaklotar

Answer 1

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

- Chetan Jain

---

使用[Op.ne]和[Op.not]有什么区别吗？ - Catfish

不确定，但我认为 [Op.ne] 在 SQL 中是 !，而 [Op.not] 是 NOT。 - Sankar

Answer 2

我看到标题是“如果不为空则在哪里”，但实际上代码中使用了“IS NOT NULL”和“IS NULL”。这里有一个示例供参考。

    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
});

请查看附件 node_modules/sequelize/types/lib/operators.d.ts 以获取详细信息。

希望能对您有所帮助。

/**
 * 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;

- gabrielrincon

Answer 3

基本上，您的查询应该有：

WHERE fk_id=1234 OR fk_id is null

所以对于Sequelize来说，它应该是这样的。

const Op = Sequelize.Op;

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

这里是文档： http://docs.sequelizejs.com/manual/tutorial/querying.html#operators

- Yurgen

---

我不明白这有什么帮助。我的意思是，谁的表中会有空的 ID？ - alnorth29

我来这里是因为有一个稍微不同的情况。如果我想在where子句中检查提供的参数值怎么办？例如： WHERE variableName is null OR fk_id is variableName - Adeel Shekhani

Answer 4

好的，我认为我答错了这个问题。所以如果提供了一个id参数，当我们返回由此过滤的结果时，如果它不是返回所有结果，就像这样。

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
});

- Yurgen

Answer 5

let where = {}

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

Model.findAll({
  where: where
});

- Sem

---

请认真听取问题。 - Abhishek Srivastava

@AbhishekSrivastava，我是否误解了问题？可以解释一下吗？ - Sem

问题主题：他需要一个带有非空ID或空ID的记录...这个逻辑如何找到它... - Abhishek Srivastava

因此，逻辑是创建一个空的“where”对象。如果提供了“id”，则将其添加为“where”对象的键，否则“where”对象仍然为空对象。Sequelize将返回模型的所有记录，而不进行任何过滤。 - Sem