Node实践总结6——多表查询

之前写过利用bookshelf和knex进行简单的数据库操作的方法。这一片主要是写如何进行多表查询。

0 实际场景

有需求才会有动力，如果基本的单表查询可以完成所有工作，就不会有多表查询什么事了。我的项目由于涉及用户、角色、权限等多个实体，要设计出合理的数据库表结构，则需要将实体、实体与实体间的关系分开，就必然涉及外键的问题，有外键就一定会出现联合查询或者嵌套查询。

---

1 最终效果

最终效果如下图

---

2 数据库设计

采用何种方法一定和底层数据库的设计有紧密的关系，这里涉及到设计范式的问题，就不往下展开了。

先说一下我的数据库表的结构：

• approles表 (id,rolename)
• dbroles表 (id,rolename)
• webusers表 (id,username,approle_id,dbrole_id)

id都是自增的数字作为主键，webusers表中approle_id和dbrole_id作为外键。

---

3 实现方法

要查询带有外键的表，通常将多张表联合成一个大表（即join操作）或者是使用嵌套查询，在子查询中完成对外键的“翻译”。

bookshelf提供了描述多个实体间对应关系的方法。
下面的代码段节选自/models/index.js，感兴趣的可以去Github上查看我完整的代码。

// WebUsers表示被管控的应用的用户信息及其角色const WebUsers = ds.bookshelf.Model.extend({    tableName: 'webusers',    approle: function() {        return this.belongsTo(AppRoles)    },    dbrole: function() {        return this.belongsTo(DbRoles)    }});// AppRoles表示被管控的应用所设计的角色const AppRoles = ds.bookshelf.Model.extend({    tableName: 'approles',    webuser: function() {        return this.hasOne(WebUsers);    }});// DbRoles表示被管控的应用所设计的角色const DbRoles = ds.bookshelf.Model.extend({    tableName: 'dbroles',    webuser: function() {        return this.hasOne(WebUsers)    }});

下面的代码段节选自/routes/index.js，通过withRelated将外键所属的表与主表join起来，完成整体的查询。

// 应用角色管理页router.get('/user_app', async function (ctx, next) {  var results = await model.WebUsers.forge().fetchAll({withRelated:['approle','dbrole']});  var relations = {};  for(var i = 0;i < results.length;i++){    relations[i] = {      'id': i + 1,      'username':results.models[i].attributes.username,      'approle':results.models[i].relations.approle.attributes.rolename    };  }  console.log(relations);  await ctx.render('user_app', {    title: 'OA-应用角色管理',    relations: relations  });});

后台最终传递给前端模板的数据就是如下图所示的样子。

最后就是把查询到的数据显示到网页上，那一部分和之前一样，就不写了，可以参考前面我写的这篇。