如何用单条Sequelize语句查询关联Sale/Trade的Artwork三种情况？

在Sequelize 6.x中有三张表：artwork、sale、trade，其中sale和trade均通过artwork_id关联到artwork。业务需求是查询存在sale记录、存在trade记录、同时存在两者的所有artwork。目前只能用三条分开的语句实现，现在可以合并成单条Sequelize语句，以下是具体实现方案：

原有三条查询语句 #

// 1. 查询同时关联sale和trade的artwork
let dataset = await Artwork.findAll(
  include:[
     {model:Sale},
     {model:Trade}
  ],
);

// 2. 查询仅关联sale的artwork
let dataset = await Artwork.findAll(
  include:[
     {model:Sale, required:true},
     {model:Trade, required:false}
  ],
);

// 3. 查询仅关联trade的artwork
let dataset = await Artwork.findAll(
      include:[
         {model:Sale, required:false},
         {model:Trade, required:true}
      ],
    );

合并后的单条查询方案 #

方案一：子查询+逻辑或 #

通过EXISTS子查询判断Artwork是否存在关联的Sale或Trade记录，同时用distinct去重避免一对多关联导致的重复结果：

const { Op } = require('sequelize');

const dataset = await Artwork.findAll({
  include: [
    { model: ForSale, required: false },
    { model: ForTrade, required: false }
  ],
  where: {
    [Op.or]: [
      Sequelize.literal(`EXISTS (SELECT 1 FROM "for_sales" WHERE "for_sales"."artwork_id" = "Artwork"."id")`),
      Sequelize.literal(`EXISTS (SELECT 1 FROM "for_trades" WHERE "for_trades"."artwork_id" = "Artwork"."id")`)
    ]
  },
  distinct: true
});

方案二：左连接+关联字段非空判断 #

利用Sequelize的关联字段引用，判断关联表的主键是否非空，同样搭配distinct去重：

const { Op } = require('sequelize');

const dataset = await Artwork.findAll({
  include: [
    { model: ForSale, required: false, as: 'sales' },
    { model: ForTrade, required: false, as: 'trades' }
  ],
  where: {
    [Op.or]: [
      { '$sales.id$': { [Op.ne]: null } },
      { '$trades.id$': { [Op.ne]: null } }
    ]
  },
  distinct: true
});

注意事项 #

• 两种方案均覆盖三种业务场景：仅关联Sale、仅关联Trade、同时关联两者。
• 如果不需要返回关联的Sale/Trade数据，可以直接去掉include部分，仅保留where条件，能提升查询效率。
• 注意SQL语句中的表名需与实际数据库表名一致（比如for_sales、for_trades，若模型定义时设置了tableName需对应调整）。

模型定义与关联关系 #

Artwork模型 #

Artwork.init({
    name: {
        type: DataTypes.STRING,
        validate:{
             len:{
               args:[1,55],
               msg:"最多55个字符",
             },
           },
    },
    author: {
        type:DataTypes.STRING
    },
    category_id:{  // 例如：绘画、摄影、书法
      type:DataTypes.INTEGER, 
    },
    wt_g: {
      type:DataTypes.DECIMAL(10,2),
    },
    production_year: {
      type: DataTypes.STRING,
    },
    dimension: {
        type:DataTypes.STRING,
    },
    uploader_id: {
        type: DataTypes.INTEGER,
        notNull:true,
    },
    description: {
        type: DataTypes.TEXT,
    },
    note: {
      type:DataTypes.TEXT
    },
    tag: {
        type:DataTypes.ARRAY(DataTypes.STRING),
    },
    deleted: {
      type:DataTypes.BOOLEAN,
      defaultValue:false,
    },
    status: { 
      type: DataTypes.STRING,
      defaultValue:"active"
    },
    artwork_data: {
      type: DataTypes.JSONB
    },
    last_updated_by_id: {type: DataTypes.INTEGER},
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE
  }

ForSale模型 #

ForSale.init({
    buyer_id: {
        type:DataTypes.INTEGER
    },
    artwork_id: {
        type:DataTypes.INTEGER,
        allowNull:false,
    },
    status: {
        type:DataTypes.STRING,
    },
    price: {
        type:DataTypes.INTEGER,
        allowNull:false
    },
    shipping_cost: {
        type:DataTypes.INTEGER,
    },
    shipping_method: {
      type:DataTypes.INTEGER, // 1-快递, 2-空运, 3-陆运
    },
    transaction_closed:{
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    seller_refund_value:{type: DataTypes.INTEGER},
    buyer_refund_value:{type: DataTypes.INTEGER},
    escrow_value:{type: DataTypes.INTEGER},
    deposit_date:{type: DataTypes.DATE},
    buy_date:{type: DataTypes.DATE},
    dispute_date:{type: DataTypes.DATE},
    active_date:{type: DataTypes.DATE},
    close_date:{type: DataTypes.DATE},
    seller_receive_payment_date:{type: DataTypes.DATE},
    buyer_receive_product_date:{type: DataTypes.DATE},
    forsale_data: {
        type: DataTypes.JSONB,  // 买家哈希地址、卖家哈希地址、交易费用等
    },
    deployed_address: {
      type: DataTypes.STRING
    },
    smartcontract_name: {
      type: DataTypes.STRING,
    },
    last_updated_by_id: {type: DataTypes.INTEGER},
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE
 }

ForTrade模型 #

ForTrade.init({
    bidder_id: {
        type:DataTypes.INTEGER
    },
    artwork_id: {
        type:DataTypes.INTEGER,
        allowNull:false,
    },
    status: {
        type:DataTypes.STRING,
    },
    price: {
        type:DataTypes.INTEGER,
        allowNull:false
    },
    shipping_method: {   // 1-快递, 2-空运, 3-陆运
        type:DataTypes.INTEGER,
    },
    shipping_cost: {
      type:DataTypes.INTEGER,
    },
    transaction_closed:{
      type: DataTypes.BOOLEAN,
      defaultValue:false,
    },
    poster_refund_value:{type: DataTypes.INTEGER},
    bidder_refund_value:{type: DataTypes.INTEGER},
    escrow_value:{type: DataTypes.INTEGER},  // 总押金为2*escrow_value
    poster_deposit_date:{type: DataTypes.DATE},
    bidder_deposit_date:{type: DataTypes.DATE},
    bid_date:{type: DataTypes.DATE},
    active_date:{type: DataTypes.DATE},
    poster_receive_product_date:{type: DataTypes.DATE},
    poster_ship_product_date:{type: DataTypes.DATE},
    bidder_receive_product_date:{type: DataTypes.DATE},
    bidder_ship_product_date:{type: DataTypes.DATE},
    close_date:{type: DataTypes.DATE},
    fortrade_data: {
        type: DataTypes.JSONB,  // 买家哈希地址、卖家哈希地址、交易费用等
    },
    deployed_address: {
      type: DataTypes.STRING
    },
    smartcontract_name: {
      type: DataTypes.STRING,
    },
    last_updated_by_id: {type: DataTypes.INTEGER},
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE
 }

关联关系 #

Artwork.hasMany(ForSale, {foreignKey: 'artwork_id'});
Artwork.hasMany(ForTrade, {foreignKey: 'artwork_id'});
ForSale.belongsTo(Artwork, {foreignKey: "artwork_id"});
ForTrade.belongsTo(Artwork, {foreignKey: "artwork_id"})

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