如何将Sequelize关联模型属性整合到父级attributes数组中

核心处理思路 #

由于你的findAndPaginate仅支持在父级attributes中配置字段，需要调整以下两点：

• 保留include但清空关联模型的attributes，仅保留JOIN关联逻辑
• 用Sequelize.col()直接引用关联表字段到父级attributes，同时修正计算字段的SQL写法

修改后的代码实现 #

const allItems = await findAndPaginate(CartItems, req.query, {
    attributes: [
        ["cart_items_id", "cartId"],
        ["cart_item_mrp", "mrp"],
        ["cart_item_discount", "discount"],
        ["cart_item_no_of_sessions", "sessionCount"],
        ["cart_item_status", "status"],
        ["cart_item_emi", "emi"],
        ["cart_item_created_at", "createdAt"],
        // 引用createdBy关联表的name字段
        [Sequelize.col('createdBy.name'), 'createdByName'],
        // 引用CartPaymentLog关联表的字段
        [Sequelize.col('CartPaymentLogs.cart_payment_log_id'), 'paymentLogId'],
        [Sequelize.col('CartPaymentLogs.cart_payment_log_payable_amount'), 'payableAmt'],
        [Sequelize.col('CartPaymentLogs.cart_payment_log_created_at'), 'createdDate'],
        // 修正后的paidStatus计算字段
        [Sequelize.literal(`
            CASE WHEN \`CartPaymentLogs\`.\`cart_payment_log_payment_invoice_id\` IN (SELECT payment_capture_invoice_id from payment_capture) THEN TRUE ELSE FALSE END
        `), 'paidStatus']
    ],
    include: [
        {
            model: User,
            as: "createdBy",
            attributes: [] // 清空属性，仅执行JOIN
        },
        {
            model: CartPaymentLog,
            attributes: [], // 清空属性，仅执行JOIN
        }
    ],
    where: {
        cart_item_lead_id: leadId,
        cart_item_status: {
            [Sequelize.Op.in]: isWishList ? ['4'] : ['0', '1']  
        },
        cart_item_deleted_status: '0',
    },
    order : [["cart_item_created_at","desc"]]
})

关键细节说明 #

1. 关联字段引用：通过Sequelize.col('关联别名.字段名')可以直接将关联表字段纳入父级查询结果，避免生成嵌套的关联对象字段。
2. 清空关联attributes：防止Sequelize自动生成关联表的默认字段选择，避免字段重复或命名冲突。
3. literal表别名转义：保持原有的反引号转义写法\CartPaymentLogs`.`cart_payment_log_payment_invoice_id``，确保SQL解析时能正确识别关联表。

预期生成的SQL示例 #

SELECT 
    `CartItems`.`cart_items_id` AS `cartId`,
    `CartItems`.`cart_item_mrp` AS `mrp`,
    `CartItems`.`cart_item_discount` AS `discount`,
    `CartItems`.`cart_item_no_of_sessions` AS `sessionCount`,
    `CartItems`.`cart_item_status` AS `status`,
    `CartItems`.`cart_item_emi` AS `emi`,
    `CartItems`.`cart_item_created_at` AS `createdAt`,
    `createdBy`.`name` AS `createdByName`,
    `CartPaymentLogs`.`cart_payment_log_id` AS `paymentLogId`,
    `CartPaymentLogs`.`cart_payment_log_payable_amount` AS `payableAmt`,
    `CartPaymentLogs`.`cart_payment_log_created_at` AS `createdDate`,
    CASE 
        WHEN `CartPaymentLogs`.`cart_payment_log_payment_invoice_id` IN (SELECT payment_capture_invoice_id from payment_capture) THEN TRUE 
        ELSE FALSE 
    END AS `paidStatus`
FROM 
    (SELECT 
         `CartItems`.`cart_items_id`,
         `CartItems`.`cart_item_mrp`,
         `CartItems`.`cart_item_discount`,
         `CartItems`.`cart_item_no_of_sessions`,
         `CartItems`.`cart_item_status`,
         `CartItems`.`cart_item_emi`,
         `CartItems`.`cart_item_created_at`,
         `CartItems`.`cart_item_created_by`
     FROM `cart_items` AS `CartItems`
     WHERE 
         `CartItems`.`cart_item_lead_id` = '200481'
         AND `CartItems`.`cart_item_status` IN ('4')
         AND `CartItems`.`cart_item_deleted_status` = '0'
     ORDER BY `CartItems`.`cart_item_created_at` DESC
     LIMIT 0, 10) AS `CartItems`
LEFT OUTER JOIN `user` AS `createdBy` ON `CartItems`.`cart_item_created_by` = `createdBy`.`userID`
LEFT OUTER JOIN `cart_payment_log` AS `CartPaymentLogs` ON `CartItems`.`cart_items_id` = `CartPaymentLogs`.`cart_payment_log_cart_items_id`
ORDER BY `createdAt` DESC;

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