如何用Sequelize ORM构建指定SQL语句，实现collate关联

核心难点是处理关联时的COLLATE条件，下面直接给出完整的Sequelize查询代码，完全匹配你需要生成的SQL：

首先确保你已经定义好对应的数据模型（示例假设模型名称与SQL中的别名一致）：

const { Op, fn, col, where } = require('sequelize');

// 构建查询
const result = await OrderInfo.findAll({
  attributes: [
    'tel',
    'mobile',
    'order_sn',
    'order_id',
    [col('PickupDeliveryOrderRemark.remark'), 'remark'],
    [col('PickupStore.region'), 'PickupStore.region'],
    [col('PickupStore.district'), 'PickupStore.district'],
    [col('PickupStore.area'), 'PickupStore.area'],
    [col('PickupStore.shop_name'), 'PickupStore.shop_name'],
    [col('PickupStore.address'), 'PickupStore.address'],
    [col('PickupStore.latitude'), 'PickupStore.latitude'],
    [col('PickupStore.longitude'), 'PickupStore.longitude'],
    [col('PickupStore.business_working_day'), 'PickupStore.business_working_day'],
    [col('PickupStore.business_holiday'), 'PickupStore.business_holiday'],
    [col('PickupStore.code'), 'PickupStore.code'],
    [fn('IFNULL', col('MassDelivery.mass_key'), '/'), 'mass_key'],
    [col('DeliveryOrder.invoice_no'), 'invoice_no']
  ],
  include: [
    {
      model: PickupStore,
      as: 'PickupStore',
      attributes: [], // 字段已在主attributes中指定，此处设为空
      required: false, // 对应LEFT OUTER JOIN
      on: where(
        fn('COLLATE', col('OrderInfo.pickup_pt_code'), 'utf8_general_ci'),
        '=',
        col('PickupStore.code')
      )
    },
    {
      model: PickupDeliveryOrderRemark,
      as: 'PickupDeliveryOrderRemark',
      attributes: [],
      required: false,
      on: { order_id: col('OrderInfo.order_id') }
    },
    {
      model: MassDelivery,
      as: 'MassDelivery',
      attributes: [],
      required: false,
      on: { order_id: col('OrderInfo.order_id') }
    },
    {
      model: DeliveryOrder,
      as: 'DeliveryOrder',
      attributes: [],
      required: false,
      on: { order_id: col('OrderInfo.order_id') }
    }
  ],
  where: {
    shipping_id: 19,
    pay_status: 2,
    [Op.or]: [
      {
        '$DeliveryOrder.pickup_status$': {
          [Op.notIn]: [1, 2, 3, 4]
        }
      },
      {
        '$DeliveryOrder.pickup_status$': null
      }
    ]
  },
  raw: true // 若需返回原始数据对象而非Sequelize实例，启用此配置
});

关键部分说明 #

1. COLLATE关联条件：在PickupStore的include配置中，通过on选项结合sequelize.where和sequelize.fn生成COLLATE语句，完全对应SQL中的OrderInfo.pickup_pt_code collate utf8_general_ci = PickupStore.code。
2. IFNULL字段处理：在主attributes里用fn('IFNULL', ...)生成带别名的字段mass_key。
3. WHERE条件的OR逻辑：使用Sequelize的Op.or操作符，同时处理NOT IN和IS NULL两种情况，关联模型字段需用$模型别名.字段名$格式引用。
4. LEFT OUTER JOIN：通过required: false配置实现左外连接，与SQL中的LEFT OUTER JOIN等价。

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