mysql relational database generates a tree structure, and the Layui page renders the tree structure diagram (detailed)

#Foreword The technologies used in the project related to this article: Layui, Mysql;

In the project, you need to use a tree structure to display some data. It is found by consulting the relevant data of the tree view
The tree view has strict requirements on the format of data, not only id, parent node and child node, but also the hierarchical structure of data. Mysql does not support hierarchical results (you can directly render the tree view). The back-end needs to process the data again because of laziness (I don't want to write data processing).

A tree menu component named DTree is found, and its functions meet the current use. Therefore, it is used to realize the display and use of attribute graph.

1, Learn to use DTree

Upper code (see notes for detailed description)

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>Tree component</title>
  <!--introduce layuicss-->
  <link rel="stylesheet" href="../layui/css/layui.css">
  <!--introduce dtree of css-->
  <link rel="stylesheet" href="../Dtree/layui_ext/dtree/dtree.css">
  <link rel="stylesheet" href="../Dtree/layui_ext/dtree/font/dtreefont.css">
</head>
<body>
	<!--Load elements of the tree-->
	<ul id="demoTree" class="dtree" data-id="0"></ul>
	<!--Button-->
	<button class="layui-btn layui-btn-normal" id="btn">Submit</button>
	<!--introduce layui js-->
  <script src="../layui/layui.js"></script>
  <script>
  	
  	/*Pay attention to loading dtree module; The path cannot be wrong*/
  	layui.extend({
    dtree: '../Dtree/layui_ext/dtree/dtree'   
  	}).use(['dtree','layer','jquery'], function(){
    //Reference layui module
    var dtree = layui.dtree,
    layer = layui.layer,
    $ = layui.jquery;
    
    //For static data test
    var data=[{"checkArr":"{\"checked\":\"1\"}","id":1,"title":"user management ","parentId":0},{"checkArr":"{\"checked\":\"1\"}","id":2,"title":"User information","parentId":1},{"checkArr":"{\"checked\":\"1\"}","id":5,"title":"management information ","parentId":1},{"checkArr":"{\"checked\":\"1\"}","id":7,"title":"Test report","parentId":0},{"checkArr":"{\"checked\":\"1\"}","id":8,"title":"genetic analysis","parentId":7},{"checkArr":"{\"checked\":\"1\"}","id":9,"title":"gene sequencing ","parentId":7},{"checkArr":"{\"checked\":\"1\"}","id":12,"title":"Type management","parentId":7},{"checkArr":"{\"checked\":\"1\"}","id":13,"title":"Doctor patient relationship","parentId":0},{"checkArr":"{\"checked\":\"1\"}","id":14,"title":"Doctor patient relationship","parentId":13},{"checkArr":"{\"checked\":\"1\"}","id":15,"title":"hospital management ","parentId":0},{"checkArr":"{\"checked\":\"1\"}","id":16,"title":"hospital management ","parentId":15},{"checkArr":"{\"checked\":\"1\"}","id":17,"title":"Permission assignment","parentId":0},{"checkArr":"{\"checked\":\"1\"}","id":18,"title":"Authority management","parentId":17}];
    
		//Initialization tree
    var DemoTree = dtree.render({
      elem: "#demoTree "/ / corresponding element id
      ,data: data // Load using data
      ,dataStyle: "layuiStyle"//Use layui style data format
      ,dataFormat:"list"//Data is a collection type  
      ,response:{message: "msg",statusCode:200}//Modify the definition of returned data in response
      ,icon: "2"  //Modify secondary icon style
      ,checkbar: true //Show check box
      ,initLevel: "1" //The default is 2 tree view shrink status
      ,none: "Data loading exception, please try again?" //Prompt when no data is loaded
//	  ,url:"../Dtree/layui_ext/json/list_.json "/ / load using URL (can exist simultaneously with data loading)
    });
    // Bind node Click
    dtree.on("node('demoTree')" ,function(obj){
      //Click which obj is which
    });
    
    // Click the button to obtain the values of all selected nodes
	$("#btn").click(function(){
		//Gets the selected element
	  var params = dtree.getCheckbarNodesParam("demoTree");
	  //Print
	  console.log(JSON.stringify(params));
	  //Data can be transmitted back here
	  $.ajax({
		//Make asynchronous calls. Note: the transferred data should be converted to JSON format -- > json.stringify (data)
		ajax content
		});
	});
  });
  </script>
</body>
</html>

Introduction structure

Description dataFormat: "list"

Mainly used

var DemoTree = dtree.render({
     //Omit other attributes
      ,dataFormat:"list"//Data is a collection type  
      ,dataStyle: "layuiStyle"//Use layui style data format
      ,checkbar: true //Show check box
      
    });

dataFormat: "list": use collection type for tree view rendering. Of course, it also supports other types of data structures. See resources for details.
You can render the tree view without using hierarchy. That's why you use it!

dataStyle: "layuiStyle": the data obtained by the url request is layui style data

checkbar: true: whether the check mode is enabled in the tree view,

To achieve the check effect, there must be "checkArr": [{"type": "0", "checked": "0"}], / / the load check box is required (I don't use type in my use)

2, Using MySQL query results

explain

You need to obtain the data format recognized by the tree component:
Used here
(node ID,
Node name, title,
Parent node parentId,
checkArr is required to be a check box.

1. Alias the field name

2. If the alias is not enough, use field splicing

-- Necessary fields to implement tree view
-- Field name does not match alias on
SELECT a.id, a.menu_name title ,a.parent_id parentId,
-- If you can't achieve the desired effect, you can splice it
CONCAT('{\"checked\":\"',
-- Judge splicing as the check box is selected by default to pave the way 
CASE WHEN (ISNULL(ar.role_id)=1) THEN  '0' ELSE '1' END
,'\"}') checkArr 
-- Table to query
from authority a 
--Left join query other tables 
LEFT JOIN (SELECT * FROM authority_role WHERE role_id=4) ar ON a.id=ar.authority_id
-- It is better to sort from top to bottom in the tree structure (this is not the order, I haven't tried yet) 
WHERE a.state=0 ORDER BY id ;
# The data of the above sql query is: query the permissions owned by the role and generate a format recognizable by the tree view (variable parameter rid)

Query results: