sequelize内联查询,执行sql显示关联表字段的as中有对应的关联model名,如何去除
< 返回列表时间: 2018-03-22来源:开源中国
HDC调试需求开发(15万预算),能者速来!>>>
环境:win10,NodeJS + express4.x + sqlite3 + sequelize
情况描述:A表关联B表查询,在查询页面定义好两个model,分别是Amodel、Bmodel,调用了方法sync
问题1:使用association定义model之间的关系,include查询,查询sql中A表字段的 别名 含有Amodel
(1)代码执行后,显示sql SELECT `Bmodel`.`ID`, `Bmodel`.`CODE`, `Bmodel`.`NAME`, `Bmodel`.`ISDELETE`, `Bmodel`.`BID`, `Amodel`.`ID` AS `Amodel.ID`, `Amodel`.`ANAME` AS `Amodel.ANAME`, `Amodel`.`ACODE` AS `Amodel.ACODE`, `Amodel`.`ISDELETE` AS `Amodel.ISDELETE` FROM `PAR_B_TB` AS `Bmodel` INNER JOIN `PAR_A_TB` AS `Amodel` ON `Bmodel`.`BID` = `Amodel`.`ID` AND `Amodel`.`ISDELETE` != 1 WHERE `Bmodel`.`ISDELETE` != 1;
(2)代码 var list = []; var includeJoin = [{ association: Bmodel.belongsTo(Amodel, {foreignKey: 'BID'}), 'where': {"ISDELETE": {$ne: 1}} }] Bmodel.findAll({ include: includeJoin, "where": { "ISDELETE": {$ne: 1} } }).then(function(result) { for (var i = 0; i < result.length; i++) { list.push(result[i].toJSON()); } console.log("list:" + list); if (list) { res.json({errno:0, title: '', data: list, s_name: ""}); } else { res.json({errno:0, title: '', data: [], s_name: ""}); } }).catch((err)=>{ console.log("json err ==>" + err); });

问题(2)提前定义表之间关系,屏蔽查询代码中的 association,提示TableName未定义

代码 Bmodel.belongsTo(Amodel, {foreignKey: 'BID'}); var list = []; var includeJoin = [{ 'where': {"ISDELETE": {$ne: 1}} }] Bmodel.findAll({ include: includeJoin, "where": { "ISDELETE": {$ne: 1} } }).then(function(result) { for (var i = 0; i < result.length; i++) { list.push(result[i].toJSON()); } console.log("list:" + list); if (list) { res.json({errno:0, title: '', data: list, s_name: ""}); } else { res.json({errno:0, title: '', data: [], s_name: ""}); } }).catch((err)=>{ console.log("json err ==>" + err); });

附:Bmodel定义(Amodel类似) var Bmodel = sequelizeConn.define("Bmodel",{ ID: {type: Sequelize.STRING, allowNull: false, primaryKey: true, unique: true}, CODE: {type: Sequelize.STRING}, NAME: {type: Sequelize.STRING}, ISDELETE: {type: Sequelize.INTEGER}, BID: {type: Sequelize.STRING}, },{ tableName: 'PAR_B_TB', // 自定义表名 freezeTableName: true, // 是否需要增加createdAt、updatedAt、deletedAt字段 timestamps: false }); Bmodel.sync();

请问,上面两种情况,如何解决,以便能得到 类似下面的 sql SELECT `Bmodel`.`ID`, `Bmodel`.`CODE`, `Bmodel`.`NAME`, `Bmodel`.`ISDELETE`, `Bmodel`.`BID`, `Amodel`.`ID` AS `ID`, `Amodel`.`ANAME` AS `ANAME`, `Amodel`.`ACODE` AS `ACODE`, `Amodel`.`ISDELETE` AS `ISDELETE` FROM `PAR_B_TB` AS `Bmodel` INNER JOIN `PAR_A_TB` AS `Amodel` ON `Bmodel`.`BID` = `Amodel`.`ID` WHERE `Bmodel`.`ISDELETE` != 1 AND `Amodel`.`ISDELETE` != 1;
多谢。
热门排行