Sequelize多对多关联过滤：获取含指定Crossing Point的Trip及全部关联点

问题需求 #

需要查询包含指定ID的Crossing Point的Trip，同时返回每个符合条件的Trip的全部Crossing Point。

当前模型定义 #

Trip 模型

const Trip = sequelize.define("trips", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    name: {
        type: DataTypes.STRING(64),
        allowNull: false,
    },
});

关联模型（Municipality & CrossingPoint）

const Municipality = sequelize.define("municipalities", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    name: {
        type: DataTypes.STRING(128),
        allowNull: false,
    },
});

const crossingPoint = sequelize.define("crossing_points",{
    id:{
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    description: {
        type: DataTypes.STRING(64),
        allowNull: true
    },
}) 

关联关系 #

db.trip.belongsToMany(db.municipality, {
    through: {
        model: db.crossingPoint,
        unique: false,
    }
});

db.municipality.belongsToMany(db.trip,{
    through: {
        model: db.crossingPoint,
        unique: false,
    }
});

现有查询代码 #

const trips = Trip.findAll({
    include: [
        {
            model: Municipality,
            through: { attributes: ["description"] },
            attributes: ["id", "name"],
            where: { id: id },
            required: true
        },
    ],
});

问题表现 #

现有代码能筛选出包含指定ID（如65）的Trip，但仅返回该指定的Crossing Point，无法返回Trip的全部关联数据：

{
    "id": 1,
    "name": "Trip 1",
    "createdAt": "2023-02-20T15:42:24.000Z",
    "updatedAt": "2023-02-20T15:42:24.000Z",
    "municipalities": [
        {
            "id": 65,
            "name": "Štěpánov"
        }
    ]
}

期望输出 #

返回符合条件的Trip，并包含其所有Crossing Point：

{
    "id": 1,
    "name": "Trip 1",
    "createdAt": "2023-02-20T15:42:24.000Z",
    "updatedAt": "2023-02-20T15:42:24.000Z",
    "municipalities": [
        {
            "id": 365,
            "name": "Krňany"
        },
        {
            "id": 65,
            "name": "Štěpánov"
        },
        {
            "id": 366,
            "name": "Chlum (Strakonice)"
        }
    ]
}

解决方案 #

问题核心是过滤条件写在include的关联模型中，导致关联数据被同步过滤，只保留匹配条目。正确做法是在Trip层级筛选符合条件的记录，同时完整加载所有关联的Crossing Point。

方法一：使用SQL EXISTS子查询 #

const trips = await Trip.findAll({
    include: [
        {
            model: Municipality,
            through: { attributes: ["description"] },
            attributes: ["id", "name"]
        }
    ],
    where: {
        [Sequelize.Op.or]: [
            Sequelize.literal(`EXISTS (
                SELECT 1 FROM crossing_points cp
                WHERE cp.tripId = trips.id AND cp.municipalityId = ${targetMunicipalityId}
            )`)
        ]
    }
});

方法二：添加筛选专用关联（ORM风格） #

const trips = await Trip.findAll({
    include: [
        {
            model: Municipality,
            through: { attributes: ["description"] },
            attributes: ["id", "name"]
        },
        // 仅用于筛选的关联，不返回数据
        {
            model: Municipality,
            as: 'filterMunicipality',
            through: { attributes: [] },
            attributes: [],
            where: { id: targetMunicipalityId },
            required: true
        }
    ]
});

说明 #

1. 方法一通过原生SQL EXISTS子查询精准筛选包含指定Municipality的Trip，主include正常加载所有关联数据。
2. 方法二通过添加带别名的筛选关联，避免与主关联冲突，设置空属性列表不返回冗余数据，required: true确保仅返回匹配的Trip。

两种方案均能实现需求，推荐方法二，更贴合Sequelize的ORM设计逻辑。

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