mysql - 同型号多个外键问题

Question

我是 Sequelize 的新手，我正在使用它来获取附近用户的列表。但我收到以下错误

AggregateReview 使用别名关联到用户。您已包含别名(测试)，但它与关联中定义的别名不匹配 (AggregateReview)

我的模型如下

module.exports = (sequelize, DataTypes) => {
const AggregateReview = sequelize.define(
    "AggregateReview",
    {
        review_type: {
            type: DataTypes.ENUM(
                reviewConstants.REVIEW_TYPE_PLATE,
                reviewConstants.REVIEW_TYPE_CHEF,
                reviewConstants.REVIEW_TYPE_DRIVER
            )
        },
        chefID: {
            type: DataTypes.INTEGER,
            AllowNull: true,
            references: {
                model: "Users",
                key: "id"
            }
        },
        driverID: {
            type: DataTypes.INTEGER,
            AllowNull: true,
            references: {
                model: "Users",
                key: "id"
            }
        },
        plateId: {
            type: DataTypes.INTEGER,
            AllowNull: true,
            references: {
                model: "Plates",
                key: "id"
            }
        },
        userCount: {
            type: DataTypes.INTEGER
        },
        rating: DataTypes.DOUBLE
    },
    {}
);
AggregateReview.associate = function(models) {
    AggregateReview.belongsTo(models.User, {
        foreignKey: "driverID",
        as: "driver"
    });
    AggregateReview.belongsTo(models.User, {
        foreignKey: "chefID",
        as: "chef"
    });
    AggregateReview.belongsTo(models.Plates, {
        foreignKey: "plateId",
        as: "plate"
    });
};
return AggregateReview;

};

我的查询如下

const roundDigit = 2;
const currentUserLocationLat = req.body.lat || req.user.location_lat;
const currentUserLocationLon = req.body.lon || req.user.location_lon;
const radiusDistance =
    req.body.radius || shippingAddressConstants.DEFAULT_RADIUS;
const radiusDistanceUnit =
    req.body.radiusUnit || shippingAddressConstants.DISTANCE_MILES;
const multiplier =
    shippingAddressConstants.radiusDistanceUnitHaversineMap[
        radiusDistanceUnit
    ];
const query = [
    [
        sequelize.literal(`(round(${multiplier} * acos( cos( radians(${currentUserLocationLat}) ) * cos( radians( location_lat ) )
  * cos( radians( location_lon ) - radians(${currentUserLocationLon}) ) + sin( radians(${currentUserLocationLat}) ) * sin(radians(location_lat))),${roundDigit}))
  `),
        "distance"
    ]
];
const where = { user_type: "chef" };
const having = { distance: { [Sequelize.Op.lte]: radiusDistance } };
const order = [[sequelize.col("distance"), "ASC"]];
const response = await User.findAll({
    where,
    attributes: ["device_id", "device_registration_token", ...query],
    include: [
        {
            model: AggregateReview,
            as: "chef"
        }
    ],
    having,
    order
});
return response;

我的用户模型

module.exports = (sequelize, DataTypes) => {
const User = sequelize.define("User", {
    name: DataTypes.STRING,
    email: {
        type: DataTypes.STRING,
        unique: true
    },
    country_code: DataTypes.STRING,
    phone_no: {
        type: DataTypes.STRING,
        unique: true
    },
    auth_token: DataTypes.STRING,
    restaurant_name: DataTypes.STRING,
    password: DataTypes.STRING,
    location_lat: DataTypes.DECIMAL(10, 8),
    location_lon: DataTypes.DECIMAL(10, 8),
    user_type: DataTypes.ENUM(
        userConstants.USER_TYPE_USER,
        userConstants.USER_TYPE_CHEF,
        userConstants.USER_TYPE_ADMIN,
        userConstants.USER_TYPE_DRIVER
    ),
    imagePath: {
        type: DataTypes.STRING
    },

    // for password reset
    password_reset_token: DataTypes.STRING,

    verification_email_token: DataTypes.STRING,
    verification_email_status: DataTypes.ENUM(
        userConstants.STATUS_PENDING,
        userConstants.STATUS_VERIFIED
    ),
    verification_phone_token: DataTypes.STRING,
    verification_phone_status: DataTypes.ENUM(
        userConstants.STATUS_PENDING,
        userConstants.STATUS_VERIFIED
    ),
    status: DataTypes.INTEGER,
    user_ip: DataTypes.STRING,
    stripe_id: DataTypes.STRING,
    provider: DataTypes.STRING,
    provider_user_id: DataTypes.STRING,
    promotionalContent: {
        type: DataTypes.BOOLEAN,
        defaultValue: false
    },
    device_id: DataTypes.STRING,
    device_registration_token: DataTypes.STRING,
    order_flag: {
        type: DataTypes.BOOLEAN,
        defaultValue: true
    }
});

User.associate = function(models) {
    User.hasMany(models.Plates);
    User.hasMany(models.OrderDelivery, { foreignKey: "driverId" });
    User.hasMany(models.Order);
    User.hasMany(models.ShippingAddress, { as: "address" });
    User.hasMany(models.CustomPlateAuctionBid);
    User.hasMany(models.Review);
    User.hasOne(models.AggregateReview, { foreignKey: "driverId" });
    User.hasOne(models.Documents);
    User.hasOne(models.Basket);
    User.hasOne(models.Wallet);
    User.hasOne(models.Transactions);
};
return User;};

Answer 1

您的 sequelize 语句应如下所示 -

const response = await User.findAll({
    where,
    attributes: ["device_id", "device_registration_token", ...query],
    include: [
        {
            model: AggregateReview,
        }
    ],
    having,
    order
});

您需要删除模型 AggregateReview 的别名，因为您在定义 User 与 AggregateReview 的关联时尚未定义任何别名。在定义 AggregateReview 与 chef 的关联时，您已将 AggregateReview 的别名定义为 User 。