demand

  • In mysql table to achieve tree structure data storage
  • Tree data query, return json format of the role tree array

implementation

  • There are usually four ways to store tree structure in mysql tables. In this paper, the first method is adopted, that is, each record is added with the level ID: PID field
  • Database table role structure, user name: root, password: 123456, database: test

  • The code is basically the same as before, mainly to achieve a one-dimensional object array into a tree array
  • The config.default.js, plugin.js and user files in the config directory remain unchanged
  • app/model/role.js
'use strict';

module.exports = app= > {
  const { INTEGER, STRING } = app.Sequelize;
  const Role = app.model.define('role', {
    id: { type: INTEGER, primaryKey: true.autoIncrement: true },
    name: STRING(50),
    pid: INTEGER,
  }, {
    timestamps: false});return Role;
};
Copy the code
  • app/controller/role.js
'use strict';

const Controller = require('egg').Controller;


class RoleController extends Controller {
  async index() {
    const { ctx } = this;
    const data = await ctx.model.Role.findAll({ raw: true}); ctx.body = ctx.service.role.buildTree(data); }}module.exports = RoleController;
Copy the code
  • app/service/role.js
'use strict';
const Service = require('egg').Service;
class RoleService extends Service {
  // Build the tree structure data
  buildTree(data) {
    const res = [];
    // Find all the root nodes
    for (const item of data) {
      if (!item.pid) {
        item.children = getNode(item.id);
        res.push(item);
      }
    }
    // Pass in the root id to recursively find all child nodes
    function getNode(id) {
      const node = [];
      for (const item of data) {
        if(item.pid === id) { item.children = getNode(item.id); node.push(item); }}if (node.length === 0) return;
      return node;
    }
    returnres; }}module.exports = RoleService;
Copy the code
  • app/router.js
'use strict';

/ * * *@param {Egg.Application} app - egg application
 */
module.exports = app= > {
  const { router, controller, jwt } = app;
  router.get('/', controller.home.index);

  router.post('/user/login', controller.user.login);
  // Query the user
  router.get('/user', controller.user.index);
  router.get('/user/:id', jwt, controller.user.show);
  / / new
  router.put('/user', jwt, controller.user.create);
  // Change the password
  router.post('/user/:id', jwt, controller.user.updatePwd);
  // Get the character tree
  router.get('/role', controller.role.index);
};
Copy the code

test

  • Get the character tree

reference

www.zhihu.com/question/20… www.jianshu.com/p/bdaffff30…