node.js - 使用Sequelize原始查询时如何在嵌套数组中获得一对多关联

Question

我正在使用以下查询

SELECT DISTINCT "branchManagement"."id",
    "branchManagement"."branch_unique_id" AS "branchUniqueId",
    "branchManagement"."name",
    "branchManagement"."branch_head" AS "branchHead",
    "branchManagement"."mobile_number" AS "mobileNumber",
    "branchManagement"."email_id" AS "emailId",
    "branchManagement"."address",
    "branchManagement"."state_id" AS "stateId",
    "branchManagement"."city_id" AS "cityId",
    "branchManagement"."pin_code" AS "pinCode",
    "branchManagement"."created_by" AS "createdBy",
    "branchManagement"."modified_by" AS "modifiedBy",
    "branchManagement"."is_active" AS "isActive",
    "branchManagement"."createdAt",
    "branchManagement"."updatedAt",
    "state"."id" AS "state.id",
    "state"."name" AS "state.name",
    "city"."id" AS "city.id",
    "city"."name" AS "city.name",
    "assignedPincode"."id" AS "assignedPincode.id",
    "assignedPincode"."branchManagementId" AS "assignedPincode.branchManagementId",
    "assignedPincode"."pinCode" AS "assignedPincode.pinCode", "assignedPincode"."isActive" AS "assignedPincode.isActive",
    "assignedPincode"."createdAt" AS "assignedPincode.createdAt", "assignedPincode"."updatedAt" AS "assignedPincode.updatedAt"
    FROM "branch_management" AS "branchManagement"
    LEFT OUTER JOIN "state" AS "state" ON "branchManagement"."state_id" = "state"."id"
    LEFT OUTER JOIN "city" AS "city" ON "branchManagement"."city_id" = "city"."id"
    LEFT OUTER JOIN "branchPincode" AS "assignedPincode" ON "branchManagement"."id" = "assignedPincode"."branchManagementId" AND "assignedPincode"."isActive" = true
    WHERE (("branchManagement"."name" ILIKE '%' OR CAST("branchManagement"."id" AS VARCHAR) ILIKE '%' OR
    "branchManagement"."branch_unique_id" ILIKE '%' OR "branchManagement"."branch_head" ILIKE '%' OR
    CAST("mobile_number" AS VARCHAR) ILIKE '%'))
    AND "branchManagement"."is_active" = true

期待每个分支内的assignedPincode数组，但为每个assignedPincode获得不同的记录有没有办法在原始查询中使用Sequelize预先加载
尝试过 raw:true 和 nest:true 但得到了预期的输出

Answer 1

如果您需要分组关联的模型记录，则不应使用 raw 选项，因为您只会得到类似于 sequelize.query 的结果。
如果您根本不需要模型实例，您可以将它们转换为普通对象(包括嵌套关联)，如下所示:

const models = await Model.findAll({})
const plainObjects = models.map(x => x.get({ plain: true })