Sequelize中findAndCountAll无法显示外键关联信息求助

问题 #

使用NodeJS和Sequelize开发API时，调用findAndCountAll查询Car模型数据，仅返回fuelId外键ID，无法获取关联的Fuel模型详细信息（如名称）。

相关代码 #

MyControllers.js #

const sequelize = require('../db/sequelize')
const getAll = (req, res) => {
    const modelName = req.params.modelName;
    const Model = sequelize[modelName];
    if (!Model) {
        const message = `Le modèle "${modelName}" n'existe pas.`;
        return res.status(404).json({ message });
    }
        Model.findAndCountAll({
            include: [
                {
                    model: sequelize['fuels'],
                    foreignKey: 'fuelId',
                    as: 'fuels',
                    attributes: ['id','name']
                }
            ]
        })
            .then(items => {
                const status = 200
                const isSuccess = true
                const message = 'La liste a bien été récupérée.'
                const total = items.count
                res.json({ isSuccess, status, message, total, results: items })
            })
} 

Sequelize.js #

const { Sequelize, DataTypes } = require('sequelize');
const FuelModel = require('../models/fuel');
const CarModel = require('../models/car');
const fuelMock = require('./mocks/mock-fuel');
const carMock = require('./mocks/mock-cars');
const fuels = FuelModel(sequelize, DataTypes);
const cars = CarModel(sequelize, DataTypes);

fuels.hasMany(cars, {foreignKey: 'fuelId', as: 'cars'});
cars.belongsTo(fuels, {foreignKey: 'fuelId', as: "fuels"});

const initDb = async () => {
    const _ = await sequelize.sync({ force: true })
    fuelMock.map(fuel => {
        fuels.create({
            name: fuel.name
        })
    })
    carMock.map(car => {
        cars.create({
            name: car.name,
            picture: car.picture,
            power: car.power,
            horses: car.horses,
            kms: car.kms,
            first_registration: car.first_registration,
            seating_places: car.seating_places,
            doors: car.doors,
            co2: car.co2,
            price: car.price,
            fuelId: car.fuelId,
        })
        .then(car => console.log(JSON.stringify(car)))
    })
}

module.exports = { 
    initDb, fuels,cars
}

当前返回结果 #

"rows": [
  {
    "id": 1,
    "name": "AUDI | A1 | SPORTBACK 30 TFSI S-LINE 1",
    "picture": "annonces/1200x900/53188-dlTXSoYqsnthVjFPwkfg.jpg",
    "power": 81,
    "horses": 110,
    "kms": 11400,
    "first_registration": "03-2023",
    "seating_places": 5,
    "doors": 5,
    "co2": 125,
    "price": 24980,
    "created": "2024-02-26T12:25:16.000Z",
    "fuelId": 1
  }
]

Car模型 #

module.exports = (sequelize, DataTypes) => {
    const Car = sequelize.define('Car', {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: {
                msg: 'Le nom est déjà pris.'
            },
            validate: {
                notEmpty: { msg: 'Le nom ne peut pas être vide' },
                notNull: { msg: 'Le nom est une propriété requise' }
            }
        },
        picture: {
            type: DataTypes.STRING,
            allowNull: false,
            validate: {
                isUrl: { msg: 'Utilisez uniquement une URL valide pour l\'image.' },
                notNull: { msg: 'L\'image est une propriété requise' }
            }
        },
        power: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        horses: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        kms: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        first_registration: {
            type: DataTypes.STRING,
            allowNull: false,
        },
        seating_places: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        doors: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        co2: {
            type: DataTypes.INTEGER,
            allowNull: false,
        },
        price: {
            type: DataTypes.INTEGER,
            allowNull: false,
        }, {
    timestamps: true,
    createdAt: 'created',
    updatedAt: false,
    tableName: 'cars'
})

return Car
}

Fuel模型 #

module.exports = (sequelize, DataTypes) => {
    const Fuel =  sequelize.define('Fuel', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
        type: DataTypes.STRING,
        allowNull: false,
      },
    }, {
      timestamps: true,
      createdAt: 'created',
      updatedAt: false,
        tableName: 'fuels'
    })

    return Fuel
  }

解决方法 #

问题核心是关联别名不匹配：belongsTo关联表示「一个Car属于一个Fuel」，应使用单数别名，且查询时的include配置必须和关联定义的as完全一致。

步骤1：修正关联定义 #

修改Sequelize.js中的belongsTo关联配置，将别名改为单数fuel：

fuels.hasMany(cars, {foreignKey: 'fuelId', as: 'cars'});
cars.belongsTo(fuels, {foreignKey: 'fuelId', as: "fuel"}); // 别名改为单数fuel

步骤2：同步修改查询的include配置 #

修改MyControllers.js中的findAndCountAll配置，将as改为fuel，可省略已在关联中定义的foreignKey：

Model.findAndCountAll({
    include: [
        {
            model: sequelize['fuels'],
            as: 'fuel', // 匹配修改后的别名
            attributes: ['id','name']
        }
    ]
})

修改后的返回结果 #

完成修改后，查询结果中将包含关联的Fuel信息：

"rows": [
  {
    "id": 1,
    "name": "AUDI | A1 | SPORTBACK 30 TFSI S-LINE 1",
    "picture": "annonces/1200x900/53188-dlTXSoYqsnthVjFPwkfg.jpg",
    "power": 81,
    "horses": 110,
    "kms": 11400,
    "first_registration": "03-2023",
    "seating_places": 5,
    "doors": 5,
    "co2": 125,
    "price": 24980,
    "created": "2024-02-26T12:25:16.000Z",
    "fuelId": 1,
    "fuel": {
      "id": 1,
      "name": "汽油" // 示例Fuel名称
    }
  }
]

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