node.js Sequelize 关联 hasOne，belongsTo

Sequelize associations hasOne, belongsTo

提问by Risto Novik

The problem is that I can not get working the relation hasOne, which does not eager load the state type object.

问题是我无法处理 hasOne 关系，它不急切加载状态类型对象。

All the queries are done on existing tables.

所有查询都在现有表上完成。

Here is the customer table, whats important is the cst_state_typefield:

这是客户表，重要的是cst_state_type字段：

module.exports = function(sequelize, DataTypes) {

    return sequelize.define('customer', {

        customer: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true,
            allowNull: true,
            validate: {
                isNumeric: true
            }
        },
        first_name: {
            type: DataTypes.STRING(100),
            validate: {
                isAlphanumeric: true
            }
        },
        last_name: DataTypes.STRING(100),
        identity_code: {
            type: DataTypes.STRING(20),
            allowNull: true,
            validate: {
                isNumeric: true
            }
        },
        note: DataTypes.STRING(1000),
        birth_date: DataTypes.DATE,


        created_by: DataTypes.INTEGER,
        updated_by: DataTypes.INTEGER,

        cst_type: DataTypes.INTEGER,
        cst_state_type:  {
            type: DataTypes.INTEGER,
        }

    }, {
        tableName: 'customer',

        updatedAt: 'updated',
        createdAt: 'created',
        timestamps: true
    });
};

cst_state_type table:

cst_state_type 表：

module.exports = function(sequelize, DataTypes) {

    return sequelize.define('StateType', {

        cst_state_type: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true,
            validate: {
            }
        },
        name: DataTypes.STRING(100),
    }, {
        tableName: 'cst_state_type',
        timestamps: false
    });
};

How the relations are described:

如何描述关系：

  global.db.Customer.hasOne(global.db.StateType, {
    foreignKey: 'cst_state_type',
    as: 'state_type'
  });

  global.db.StateType.belongsTo(global.db.Customer, {
    foreignKey: 'cst_state_type'
  });

And creating eager loading query:

并创建急切加载查询：

    db.Customer.findAll( {
        include: [
            { model: db.Address, as: 'addresses' },
            { model: db.StateType, as: 'state_type' }
        ]
    })
        .success(function (customers) {
            res.json(200, customers);
        })
        .fail(function (error) {
            res.json(500, { msg: error });
        });

采纳答案by Jan Aagaard Meier

I'm pretty sure that the error is in your associations somewhere. From the way you described your table structure, the assocations should look something like this:

我很确定错误出在您的关联中。根据您描述表结构的方式，关联应如下所示：

global.db.Customer.belongsTo(global.db.StateType, {
    foreignKey: 'cst_state_type',
    as: 'state_type'
});

global.db.StateType.hasMany(global.db.Customer, {
    foreignKey: 'cst_state_type'
});

As i understand it, the same state can be assigned to many customers, therefore StateType.hasMany. The relation from customer -> statetype is a "back-assocation", meaning that the foreign key is in the customer table. For that you need belongsTo

据我了解，相同的状态可以分配给许多客户，因此StateType.hasMany。来自 customer -> statetype 的关系是一个“反向关联”，这意味着外键在客户表中。为此你需要belongsTo

回答by Gerard

Thanks for you answer, it helped me a lot. You can also add the relations directly in your model by using classmethods. I added a example below, hope this helps!

谢谢你的回答，对我帮助很大。您还可以使用类方法直接在模型中添加关系。我在下面添加了一个示例，希望对您有所帮助！

UserModel (file)

用户模型（文件）

module.exports = function(sequelize, DataTypes){
    var User = sequelize.define(
        'User', {
            name: {
                type: DataTypes.STRING,
                allowNull: false
            }
        },
        {
            classMethods:{
                associate:function(models){
                    User.hasMany(models.Comment, { foreignKey: 'userId'} );
                }
            }
        }

    );
    return User;
};

CommentModel (file):

评论模型（文件）：

module.exports = function(sequelize, DataTypes){
    var Comment = sequelize.define(
        'Comment', {
            text: {
                type: DataTypes.STRING,
                allowNull: false
            }
        },
        {
            classMethods:{
                associate:function(models){
                    Comment.belongsTo(models.User, { foreignKey:'userId'} );
                }
            }
        }

    );
    return Comment;
};

You don't have to set the foreignkey, sequelize will handle it if you don't specify the foreignkeys.

您不必设置外键，如果您不指定外键，sequelize 将处理它。

Then in the query:

然后在查询中：

models.Comment.find({
        where: { id: id },
        include: [
            models.User
        ],
        limit: 1
    })