sequelize.js - 使用嵌套关系在 Sequelize 中加入多个表

Question

我试图用以下嵌套关系连接三个表

我的模型是，
表1:

module.exports = function(sequelize, DataTypes) {
return sequelize.define('tbl1', {
entityid: {
  type: DataTypes.INTEGER,
  allowNull: false,
  primaryKey: true,
  autoIncrement: true
},
lastname: {
  type: DataTypes.STRING,
  allowNull: true
},
firstname: {
  type: DataTypes.STRING,
  allowNull: false
},
middlename: {
  type: DataTypes.STRING,
  allowNull: true
},
salutation: {
  type: DataTypes.STRING,
  allowNull: false
},
gender: {
  type: DataTypes.INTEGER,
  allowNull: false
},
contactno: {
  type: DataTypes.BIGINT,
  allowNull: false
},
email: {
  type: DataTypes.STRING,
  allowNull: false
},
dob: {
  type: DataTypes.DATE, 
  allowNull: false,
},
doj: {
  type: DataTypes.DATE,
  allowNull: false,
},
dot: {
  type: DataTypes.DATE,
  allowNull: true
},
entitystatus: {
  type: DataTypes.INTEGER,
  allowNull: true
},
panno: {
  type: DataTypes.STRING,
  allowNull: false
},
designation: {
  type: DataTypes.STRING,
  allowNull: false
},
department: {
  type: DataTypes.INTEGER,
  allowNull: false
},
location: {
  type: DataTypes.INTEGER,
  allowNull: true
},
maritalstatus: {
  type: DataTypes.INTEGER,
  allowNull: false
}
}, {
tableName: 'tbl1',
freezeTableName: true
});
};

表2:

module.exports = function(sequelize, DataTypes) {
return sequelize.define('tbl2', {
decid: {
  type: DataTypes.INTEGER,
  allowNull: false,
  primaryKey: true,
  autoIncrement: true
},
tbl1Entityid: {
  type: DataTypes.INTEGER,
  allowNull: false,
  references: {
    model: 'tbl1',
    key: 'entityid'
  }
},
status: {
  type: DataTypes.INTEGER,
  allowNull: false,
  references: {
    model: 'cfg_codevalue',
    key: 'codevalueid'
  }
},
amountinvested: {
  type: DataTypes.BIGINT,
  allowNull: false
}
}, {
tableName: 'tbl2',
freezeTableName: true
});
};

表3:

 module.exports = function(sequelize, DataTypes) {
 return sequelize.define('tbl3', {
 detailid: {
  type: DataTypes.INTEGER,
  allowNull: false,
  primaryKey: true,
  autoIncrement: true
 },
 tbl2Decid : {
  type: DataTypes.INTEGER,
  allowNull: false,
  references: {
    model: 'tbl2',
    key: 'decid'
  }
 },
 sectionid: {
  type: DataTypes.INTEGER,
  allowNull: false,
  references: {
    model: 'tbl4',
    key: 'sectionid'
  }
 },
 tbl1Entityid: {
  type: DataTypes.INTEGER,
  allowNull: false,
  references: {
    model: 'tbl1',
    key: 'entityid'
  }
 },
 investmentid: {
  type: DataTypes.INTEGER,
  allowNull: false
 },
 noteid: {
  type: DataTypes.INTEGER,
  allowNull: false,
  references: {
    model: 'txn_note',
    key: 'noteid'
  }
 },
 amount: {
  type: DataTypes.INTEGER,
  allowNull: false
},
}, {
tableName: 'tbl3',
freezeTableName: true
});
};

这是关系:

 tbl1.hasOne(tbl2);
 tbl2.belongsTo(tbl1);
 tbl2.hasMany(tbl3);
 tbl3.belongsTo(tbl2);

 tbl1.find({
        where: {email:"abc@abc.com"},
        include: [{
            model: tbl2,                
            include: [{
                model: tbl3,
            }]
        }]
    }).success(function(result) {
        callback(result);
    }).failure(function(error) {
        callback(error);
    });

它没有显示来自 tbl3 的与 tbl2 相关的多行。我也尝试过使用 required 的内部连接，但它也显示相同的结果

Answer 1

所以我试图重现这个问题，但不幸的是我不能。加入工作得很好。这是我用来测试问题的模型文件。

模型/tbl1.js

module.exports = (sequelize, DataTypes) => sequelize.define('tbl1', {
    entityid: {
        type: DataTypes.INTEGER,
        allowNull: true,
        primaryKey: true,
        autoIncrement: true
    },
    lastname: {
        type: DataTypes.STRING,
        allowNull: true
    },
    firstname: {
        type: DataTypes.STRING,
        allowNull: true
    },
    middlename: {
        type: DataTypes.STRING,
        allowNull: true
    },
    salutation: {
        type: DataTypes.STRING,
        allowNull: true
    },
    gender: {
        type: DataTypes.INTEGER,
        allowNull: true
    },
    contactno: {
        type: DataTypes.BIGINT,
        allowNull: true
    },
    email: {
        type: DataTypes.STRING,
        allowNull: true
    },
    dob: {
        type: DataTypes.DATE,
        allowNull: true,
    },
    doj: {
        type: DataTypes.DATE,
        allowNull: true,
    },
    dot: {
        type: DataTypes.DATE,
        allowNull: true
    },
    entitystatus: {
        type: DataTypes.INTEGER,
        allowNull: true
    },
    panno: {
        type: DataTypes.STRING,
        allowNull: true
    },
    designation: {
        type: DataTypes.STRING,
        allowNull: true
    },
    department: {
        type: DataTypes.INTEGER,
        allowNull: true
    },
    location: {
        type: DataTypes.INTEGER,
        allowNull: true
    },
    maritalstatus: {
        type: DataTypes.INTEGER,
        allowNull: true
    }
}, {
    tableName: 'tbl1',
    freezeTableName: true,
    classMethods: {
        associate: models => {
            models.tbl1.hasOne(models.tbl2, {
                foreignKey: 'tbl1Entityid'
            });
        }
    }
});

模型/tbl2.js

module.exports = (sequelize, DataTypes) => sequelize.define('tbl2', {
    decid: {
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    tbl1Entityid: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    status: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    amountinvested: {
        type: DataTypes.BIGINT,
        allowNull: false
    }
}, {
    tableName: 'tbl2',
    freezeTableName: true,
    classMethods: {
        associate: models => {
            models.tbl2.belongsTo(models.tbl1, {
                foreignKey: 'tbl1Entityid'
            });
            models.tbl2.hasMany(models.tbl3, {
                foreignKey: 'tbl2Decid'
            });
        }
    }
});

模型/tbl3.js

module.exports = (sequelize, DataTypes) => sequelize.define('tbl3', {
    detailid: {
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    tbl2Decid: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    sectionid: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    investmentid: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    noteid: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    amount: {
        type: DataTypes.INTEGER,
        allowNull: false
    }
}, {
    tableName: 'tbl3',
    freezeTableName: true,
    classMethods: {
        associate: models => {
            models.tbl3.belongsTo(models.tbl2, {
                foreignKey: 'tbl2Decid'
            });
        }
    }
});

注意:我特意更改了一些字段以允许空值以使测试更容易。

UPDATE :这是我用来测试代码的主要代码。

应用程序.js

var Promise = require('bluebird'),
    path = require('path'),
    moment = require('moment'),
    _ = require('underscore'),
    sequelize = new(require('sequelize-values')())('postgres://postgres:postgres@localhost/development', {
        logging: console.log,
        native: false,
        pool: {
            maxConnections: 5,
            maxIdleTime: 3000
        },
        ssl: false,
        timezone: '+07:00'
    }),
    models = require('./models')(sequelize);

sequelize.sync().then(() => Promise.join(

    models.tbl1.upsert({
        entityid: 1,
        lastname: 'GHI',
        firstname: 'ABC',
        middlename: 'DEF',
        salutation: 'Mr.',
        contactno: '123456',
        email: 'abc@abc.com',
        dob: moment('12-12-1995', 'DD-MM-YYYY').format()
    }),
    models.tbl1.upsert({
        entityid: 2,
        lastname: 'Doe',
        firstname: 'Jane',
        middlename: 'Example',
        salutation: 'Ms.',
        contactno: '987654',
        email: 'jane.doe@example.com',
        dob: moment('01-04-1990', 'DD-MM-YYYY').format()
    }),

    models.tbl2.upsert({
        decid: 1,
        tbl1Entityid: 1,
        status: 1,
        amountinvested: 123456789
    }),
    models.tbl2.upsert({
        decid: 2,
        tbl1Entityid: 2,
        status: 3,
        amountinvested: 987654321
    }),

    models.tbl3.upsert({
        detailid: 1,
        tbl2Decid: 1,
        sectionid: 50,
        investmentid: 60,
        noteid: 70,
        amount: 80
    }),
    models.tbl3.upsert({
        detailid: 2,
        tbl2Decid: 1,
        sectionid: 150,
        investmentid: 160,
        noteid: 170,
        amount: 180
    }),
    models.tbl3.upsert({
        detailid: 3,
        tbl2Decid: 2,
        sectionid: 250,
        investmentid: 260,
        noteid: 270,
        amount: 280
    })

)).then(() => models.tbl1.findOne({
    where: {
        email: 'abc@abc.com'
    },
    include: [{
        model: models.tbl2,
        include: [{
            model: models.tbl3,
        }]
    }]
})).then(result => {
    console.log(result.getValues());
});

模型/index.js

var fs = require('fs'),
    path = require('path');

module.exports = sequelize => {
    var models = {};

    fs.readdirSync(__dirname).filter(file => {
        return (file.indexOf('.') !== 0) && (file !== 'index.js');
    }).forEach(file => {
        var model = sequelize.import(path.join(__dirname, file));
        models[model.name] = model;
    });

    Object.keys(models).forEach(modelName => {
        if ('associate' in models[modelName]) {
            models[modelName].associate(models);
        }
    });

    return models;
};