sql-server - NodeJs中针对MsSql的SequelizeJs关联

我在SQL Server中已有预先存在的表，我想在NodeJs项目中使用这些表。

第一个表是Report表，它具有{ReportId，ReportName}列。

第二个表是“收藏夹”表，它具有{FavouriteId，ReportId，UserName}列。

您可以在下面找到模型定义。我想分配外键并进行依赖。最后，我想为特定用户选择带有“收藏夹”标志的报告。

module.exports = function(sequelize, DataTypes) {
    var Favourite = sequelize.define("IUI_Favourites", {
            favourite_id: {
                type: DataTypes.INTEGER,
                primaryKey: true,
                field: 'iui_favourite_id'
            },
            report_id: {
                type: DataTypes.INTEGER,
                field: 'iui_report_id'
            },
            username: {
                type: DataTypes.STRING,
                field: 'iui_username'
            }
        },
        {
            timestamps: false,
            freezeTableName: true
        });
    return Favourite;
};


module.exports = function(sequelize, DataTypes) {
    var Report = sequelize.define("IUI_Reports", {
        report_id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            field: 'iui_report_id'
        },
        report_name: {
            type: DataTypes.STRING,
            field: 'iui_report_name'
        }
    },
    {
         timestamps: false,
         freezeTableName: true
    });
    return Report;
};

你能帮助我吗？
提前致谢。

最佳答案

在Favourite模型中，您需要使用classMethods函数创建属性associate。

    // in Favourite model
    classMethods: {
        associate: function(models){
            this.belongsTo(models.Report, { foreignKey: 'iui_report_id' });
        }
    },
    timestamps: false,
    freezeTableName: true
});

这定义了可以通过数据库中的Favourite外键字段为模型Report分配iui_report_id。

然后，在Report模型中，您必须定义1：M关系：

    // in Report model
    classMethods: {
        associate: function(models){
            this.hasMany(models.Favourite, { foreignKey: 'iui_report_id' });
        }
    },
    timestamps: false,
    freezeTableName: true
});

为了返回指定用户喜欢的报告，您必须使用指定条件在findAll()上调用Report方法：

Report.findAll({
     include: [
         {
             model: Favourite,
             where: {
                 username: 'admin'
             }
         }
     ]
}).then((reports) => {
    // here you get reports that are favourited by user with username 'admin'
});

这将生成一个SQL查询，该查询将使用Report和Favourite上的JOIN条件返回reports.id = favourite.report_id和favourite.username = 'admin'对象。我希望这是您想要的。

编辑

根据评论，如果指定的报告被任何用户收藏，则要将“收藏夹”标志设置为true。为此，您必须将此标志字段添加到Report模型中，例如is_favourited。它可以是存储在数据库中的字段，也可以是具有自己的getter的virtual序列字段（虚拟字段不存储在数据库中）。

在虚拟字段的情况下，可能看起来像这样：

var Report = sequelize.define('report', {
    ...
    is_favourited: {
        type: DataTypes.VIRTUAL(DataTypes.BOOLEAN),
        get: function() {
            return sequelize.models.Favourite.count({
                where: { iui_report_id: this.get('report_id') }
            }).then((countResult) => {
                return countResult > 0;
            });
        }
    }
}, {
   // additional model options e.g. classMethods etc.
});

如果存在任何具有指定true值的Favourite对象，则此虚拟属性返回iui_report_id。但是，它是异步的，因此您需要像.then()一样使用report.get('is_favourited').then((result) => { console.log(result); });调用它

另一方面，如果要将该标志存储在数据库中，则在创建新的is_favourited模型实例时必须将Favourite字段值手动设置为true。为此，您可以使用afterCreate模型上的Favourite挂钩。

// in Favourite model
timestamps: false,
freezeTableName: true,
hooks: {
    afterCreate: function(instance, options) {
        return sequelize.models.Report.findById(instance.get('iui_report_id').then((report) => {
             if ( company && !company.get('is_favourited') ) {
                 return company.set('is_favourited', true).save().then(() => { return; });
             }
    }
}

// in Report model
var Report = sequelize.define('report', {
    ...
    is_favourited: {
        type: DataTypes.BOOLEAN,
        defaultValue: false
    }
}, {
   // other model options...
});

编辑2

根据您的最后一条评论，您想创建这样的查询

SELECT Reps.rep_id, Reps.rep_name, CASE WHEN Favs.rep_id IS NULL THEN FALSE ELSE TRUE END is_favourited
FROM Reps
LEFT OUTER JOIN (SELECT rep_id FROM Favs WHERE user = 'alex') Favs ON Reps.rep_id = Favs.rep_id

该查询将始终返回is_favourited = TRUE，因为您在ON子句中的JOIN条件是Reps.rep_id = Favs.rep_id，因此即使Favs.rep_id是NULL，ON条件也不会通过（因为是主键，不能为Reps.rep_id。

但是，如果您确实要执行这样的查询，它将看起来像这样

Report.findAll({
    include: [
        {
            model: Favourite,
            where: { username: 'alex' }, // or any other passed value
            attributes: []
        }
    ],
    attributes: ['report_id', 'report_name', sequelize.literal('CASE WHEN "IUI_Favourites"."report_id" IS NULL THEN FALSE ELSE TRUE END is_favourited')],
    raw: true // because your Report model does not have is_favourited field
}).then((rows) => {
    // result
});