sequelize内联查询,执行sql显示关联表字段的as中有对应的关联model名,如何去除

bohe2005 发布于 2018/03/22 13:38
阅读 1K+
收藏 0

环境: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;

 多谢。

加载中
0
紫外線
紫外線

直接写SQL

0
bohe2005
bohe2005

引用来自“紫外線”的评论

直接写SQL

嗯,等会试试。

项目要求是不要写sql,有办法处理吗?

另外一个问题:https://www.oschina.net/question/2362544_2276453 烦请也看看呢。

返回顶部
顶部