Sequelize v6中使用Op.or构建动态Where子句报错排查求助

What's Causing the Error? #

Your error Error: Invalid value { customer_id: 'dg5j5435r4gfd' } boils down to two key issues tied to Sequelize v6's stricter type validation compared to v5:

1. Mismatched Data Types
   Your Sheet model defines customer_id as a TINYINT(2).UNSIGNED (a small unsigned integer between 0-255), but you're passing string values like 'dg5j5435r4gfd' or '123456' into the query. Sequelize v6 no longer implicitly converts strings to integers like v5 did, so this triggers a validation failure.

2. Stricter Validation in Op.or Blocks
   When you set customer_id: customerID directly, you might have been passing a numeric value (or v6 allows looser checks for single-field assignments). But when using Op.or, every value in the array gets strict type validation—hence the error when using string IDs.

First, double-check you're importing Op correctly (required in v6):

// CommonJS
const { Op } = require('sequelize');
// ES Modules
import { Op } from 'sequelize';

Step-by-Step Fixes #

1. Convert IDs to Numeric Values

First, turn your string customer IDs into integers to match the model's data type. Add error handling to catch invalid ID formats:

// Convert string IDs to integers (base 10)
const numericCustomerID = parseInt(customerID, 10);
const numericCoreCustomerID = parseInt(coreCustomerID, 10);

// Optional: Validate the conversion to avoid NaN values
if (isNaN(numericCustomerID) || isNaN(numericCoreCustomerID)) {
  throw new Error("Customer IDs must be valid numbers");
}

2. Rebuild the Where Block with Valid Types

Update your where condition to use the numeric IDs in the Op.or block:

let whereBlock = { deleted_at: null };

if (args.includeCore) {
  if (customerID !== 'all') {
    whereBlock[Op.or] = [
      { customer_id: numericCustomerID },
      { customer_id: numericCoreCustomerID }
    ];
  }
} else {
  whereBlock.customer_id = numericCustomerID;
}

3. Edge Case Checks

• Ensure coreCustomerID also falls within the TINYINT(2) range (0-255)—values outside this range will still trigger errors.
• If you actually need customer_id to accept string values, modify your Sheet model's customer_id field to DataTypes.STRING instead of TINYINT(2).UNSIGNED (don't forget to migrate your database schema after this change).

Full Working Example #

Here's how your updated query will look:

const { Op } = require('sequelize'); // Make sure this is imported

// Convert IDs to numbers
const numericCustomerID = parseInt(customerID, 10);
const numericCoreCustomerID = parseInt(coreCustomerID, 10);

if (isNaN(numericCustomerID) || isNaN(numericCoreCustomerID)) {
  throw new Error("Invalid customer ID format");
}

let whereBlock = { deleted_at: null };
if (args.includeCore) {
  if (customerID !== 'all') {
    whereBlock[Op.or] = [
      { customer_id: numericCustomerID },
      { customer_id: numericCoreCustomerID }
    ];
  }
} else {
  whereBlock.customer_id = numericCustomerID;
}

const files = await db.Sheet.findAll({
  raw: true,
  attributes: [
    'sheet_name',
    'sheet_file_name',
    ['brand_name', 'brand'],
    'updated_at',
    'active',
    [Sequelize.col('Chemical.name'), 'chemical'],
    [Sequelize.col('Load.value'), 'load'],
  ],
  include: [
    {
      model: db.Load.scope(null),
      required: true,
      as: 'Load',
      attributes: ['value'],
    },
    {
      model: db.Chemical.scope(null),
      required: true,
      as: 'Chemical',
      attributes: ['name'],
    },
  ],
  where: whereBlock,
  order: [['active', 'DESC']],
});

This should resolve the validation error while keeping your Op.or logic intact for Sequelize v6.

内容的提问来源于stack exchange，提问作者dmikester1