Tuesday, 25 April 2017

Sequelize simple inner join

I have two models, policyTable and policy_rule defined as below. There is a column called policy_id in policy_rule which is a foreign key and references to id column in policyTable. The policy_rule can have multiple policyTable, i.e there is a 1:N relationship.

var policyTable = dbController.db.define('policyTable', {
    id: {
        type: Sequelize.BIGINT,
        autoIncrement: true,
        primaryKey: true
    },
    't': {
        type: Sequelize.BIGINT,
        unique: true
    },
    'name': {
        type: Sequelize.STRING,
    },
    src: {
        type: Sequelize.STRING
    }
}, {
    timestamps: false,
    freezeTableName: true,
    tableName: 'zo_policy',
});

var policy_rule = dbController.db.define('policy_rule', {
    policy_id: {
        type: Sequelize.BIGINT,
        references: {
            model: policyTable,
            key: 'id'
        },
        onUpdate: 'cascade',
        onDelete: 'cascade'
    },
    agent_id: {
        type: Sequelize.BIGINT,
    },
    enabled: {
        type: Sequelize.BOOLEAN,
    }
}, {
    timestamps: false,
    freezeTableName: true,
    tableName: 'zo_policy_rule',
});

Now I want to join these two models and get all columns of both of them. How can I do that? I tried the below code but it says

Error: policy_rule is not associated to policyTable!

function getAllPolicies() {
    return policyTable.findAndCountAll({
        include: [{
            model: policy_rule
            }]
    }).then(function (users) {
        console.log(users);
        data.count = users.count;
        data.users = users.rows;
        console.log(data);
        return data;
    });
};



via Prerak Sola

No comments:

Post a Comment