One, foreword
The recent revision of the team’s scheduling system involved recursive query of the database. There was a demand data table, and the demand data in the table defined the data inheritance relationship with parentId as the foreign key, and the relationship between the requirements presented a tree relationship. The demand data table is as follows:
mysql> desc needs; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(45) | YES | | NULL | | | parentId | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rowsin set (0.01 sec)
Copy the codeThere are requirements that need to find the entire hierarchy of sub-requirements based on a root requirement.
For example, the tree structure of A requirement is as follows:
The data are as follows:
mysql> select * from needs;
+----+------+----------+
| id | name | parentId |
+----+------+----------+
| 1 | A | NULL |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 2 |
| 5 | E | 2 |
| 6 | F | 3 |
| 7 | G | 3 |
| 8 | H | 5 |
| 9 | I | 5 |
| 10 | J | 8 |
+----+------+----------+
10 rows in set (0.00 sec)
Copy the codeSecond, MySQL implementation
1. Custom function implementation
Implementation idea: Firstly, according to the relationship between the child parenId and the parent ID, find out all the ids of the hierarchical data, and then pull out all the data of these ids.
(1) Function declaration
DELIMITER //
CREATE FUNCTION `getParentList`(rootId INT)
RETURNS char(400)
BEGIN
DECLARE fid int default 1;
DECLARE str char(44) default rootId;
WHILE rootId > 0 DO
SET fid=(SELECT parentId FROM needs WHERE id=rootId);
IF fid > 0 THEN
SET str=CONCAT(str , ', ' , fid);
SET rootId=fid;
ELSE SET rootId=fid;
END IF;
END WHILE;
return str;
END //
Copy the codeGrammar explanation:
DELIMITER: Define the MySQL DELIMITER as //. The default DELIMITER is; , to prevent internal use of functions; The interrupt function
CREATE FUNCTION FUNCTION name (parameter) RETURNS Type of the returned value: a user-defined FUNCTION
DECLARE: Declares variables
Body of the WHILE conditional DO loop: WHILE loop
IF condition THEN content body ELSE Content body: IF judgment
SET variable = value: Stores the value
CONCAT(str1,str2,…) : function to concatenate multiple strings into a single string
(2) Function call
mysql> DELIMITER; -> SELECT getParentList(1); + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | getParentList (1) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + |, 1,2,3,4,5,6,7,8,9,10 | +-----------------------+ 1 rowin set (0.01 sec)
Copy the codeGrammar explanation:
DELIMITER; : Because DELIMITER // changed the DELIMITER before, you need to call again to change the DELIMITER to;
SELECT function () : calls the function and searches for results
(3) Combined with FIND_IN_SET, pull out all sub-requirements
mysql> SELECT * FROM needs WHERE FIND_IN_SET(ID , getParentList(1));
+----+------+----------+
| id | name | parentId |
+----+------+----------+
| 1 | A | NULL |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 2 |
| 5 | E | 2 |
| 6 | F | 3 |
| 7 | G | 3 |
| 8 | H | 5 |
| 9 | I | 5 |
| 10 | J | 8 |
+----+------+----------+
10 rows in set (0.03 sec)
Copy the codeFIND_IN_SET(STR,strlist) : function that queries the result of strlist containing STR and splits the items in strlist with,
2. Recursive CTE implementation
(1) Introduction of recursive CTE
Common Table Expression (CTE) is a common table expression. You can query the defined expression by self-reference. MySQL 8.0 or later.
Recursive CTE consists of three parts: initial query part, recursive query part and termination recursive condition.
The syntax is as follows:
Recursive_query WITH RECURSIVE cte_name AS(initial_query) SELECT * from recursive_query FROM cte_nameCopy the codeFor more information about CTE, see the documentation: A Definitive Guide To MySQL Recursive CTE
(2) Recursive CTE implementation
WITH RECURSIVE needsTree AS
( SELECT id,
name,
parentId,
1 lvl
FROM needs
WHERE id = 1
UNION ALL
SELECT nd.id,
nd.name,
nd.parentId,
lvl+1
FROM needs AS nd
JOIN needsTree AS nt ON nt.id = nd.parentId
)
SELECT * FROM needsTree ORDER BY lvl;
Copy the codeImplementation explanation:
Initial query part: Find the first level requirements
Recursive query part: Find sub-requirements
Condition for termination of recursion: parentId of the child is equal to the ID of the parent
Query result:
+------+------+----------+------+
| id | name | parentId | lvl |
+------+------+----------+------+
| 1 | A | NULL | 1 |
| 2 | B | 1 | 2 |
| 3 | C | 1 | 2 |
| 6 | F | 3 | 3 |
| 7 | G | 3 | 3 |
| 4 | D | 2 | 3 |
| 5 | E | 2 | 3 |
| 8 | H | 5 | 4 |
| 9 | I | 5 | 4 |
| 10 | J | 8 | 5 |
+------+------+----------+------+
10 rows in set (0.00 sec)
Copy the codeThree, Sequqlize implementation
1. Sequelize is introduced
Sequelize is a Node.js ORM framework that maps the table structure of a relational database to an object. It supports Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. In the background development of the scheduling system, I chose the framework to operate the database, which can be more convenient to process data.
Sequelize You can view the Sequelize official document.
2. Recursive implementation
1. Connect to the mysql database
var Sequelize = require('sequelize');
const sequelize = new Sequelize('schedule' , 'root' , '12345678' , {
host : '127.0.0.1',
dialect : 'mysql',
port : '3306',
})
module.exports = {
sequelize
}
Copy the codeGrammar explanation:
New Sequelize(databse, username, Password, options) : Instantiate Sequelize and connect to the database
Options = {host, // database host dialect, // database port // database port number, default: 3306}Copy the code2. Define the schema model table of the data table
module.exports = function(sequelize, DataTypes) {
return sequelize.define('needs', {
id: {
type: DataTypes.INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true
},
name: {
type: DataTypes.STRING(45),
allowNull: false
},
parentId: {
type: DataTypes.INTEGER(11),
allowNull: true,
},
}, {
tableName: 'needs',
timestamps: false
});
};
Copy the codeGrammar explanation:
Sequelize. define(modelName, Attribute, Options) : Defines the model of a data table.
Attribute: An object. The key value is the name of the column, and the value is the definition of the column, such as the data type, whether the primary key is used, and whether the column is required
Options: Some configuration of the data table. For example, the corresponding data tableName tableName, whether the timestamp is required, etc
3. Import the table model
const { sequelize } = require('.. /config/db'); Const Needs = sequelize.import('./needs.js');
Copy the codeGrammar explanation:
Sequelize.import (PATH) : Imports the data table model
4. Recursive query
Implementation idea: similar to CTE implementation idea, first find out the first level of demand, and then recursively find out the sub-demand.
class NeedModule{
constructor(id){
this.id = id;
}
async getNeedsTree() {let rootNeeds = await Needs.findAll({
where : {
id : this.id
}
})
rootNeeds = await this.getChildNeeds(rootNeeds);
return rootNeeds;
}
async getChildNeeds(rootNeeds){
let expendPromise = [];
rootNeeds.forEach(item => {
expendPromise.push(Needs.findAll({
where : {
parentId : item.id
}
}))
})
let child = await Promise.all(expendPromise);
for(let [idx , item] of child.entries()){
if(item.length > 0){
item = await getChildNeeds(item);
}
rootNeeds[idx].child = item;
}
returnrootNeeds; }}Copy the codeGrammar explanation:
FindALL (options) : Queries multiple data sets
Options: Queries the configuration
options.where: Query conditions
The query results are as follows:
As you can see from the search results, using Sequelize queries can better divide hierarchical data into hierarchical storage.
3. Nested attribute implementation
The nested property in Sequelize’s findAll method can find the data of an inherited relationship based on the join relationship.
1. Define table relationships
Since the requirement table is required for self-join queries, the table relationships need to be defined first. Query is a one-to-many relationship, so hasMany is used to define relationships.
Needs.hasMany(
Needs,
{
as: 'child',
foreignKey: 'parentId'});Copy the codeGrammar explanation:
Sourcemodel.hasmany (targetModel, options) : the tables defining the source and target models are one-to-many relationships, and foreign keys are added to the targetModel
Options: Defines some properties of the table relationship. Alias of the target model when as defines a join query. ForeignKey is the foreignKey name.
2. Self-link query
async getNeedTree(id){
return await Needs.findAll({
where : {
id
},
include : {
model: Needs,
as:'child',
required : false,
include : {
all : true,
nested : true,}}})}Copy the codeGrammar explanation:
Include: joins the query list
-
Include. model: The model to join queries
-
Include. as: Alias for the join query model
-
Include. requeired: If true, the join query is an internal join. False indicates the left connection. Default to true if there is a WHERE, and false otherwise.
-
Include. all: Query all models in a nested manner
-
Include. nested: indicates nested queries
Using this method, the deepest child of the query results in three levels. You can use this method if you can ensure that the data inheritance relationship is at most three levels.
Four,
MySQL 8+ can be implemented using CTE, using less code than custom functions, and using WITH… AS can optimize recursive queries. Sequelize currently supports CTE, but only PostgreSQL, SQLite, and MSSQL databases. Sequelize supports CTE, but only PostgreSQL, SQLite and MSSQL databases.
Reference documentation
Mysql > query http://www.cnblog…
2.Managing Hierarchical Data in MySQL Using the Adjacency List Model
3.A Definitive Guide To MySQL Recursive CTE
4. http://docs.seque…