SQL hierarchical document with nested sets

Question

i try to create the sql tables and statements for my scenario. I use mySQL.

I have a standard hierarchical structure for documents but the structure of a specific document for a client can be different as the standard structure.

My table that saves all paragraphs and the standard structure looks like this:

CREATE TABLE paragraphs (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title TEXT CHARACTER SET utf8 COLLATE utf8_german2_ci NOT NULL,
  lft INT NOT NULL,
  rgt INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and the inserts for example:

INSERT INTO paragraphs (id,title,lft,rgt)VALUES(1,'Heading 1',1,2);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(2,'Heading 2',3,4);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(3,'Heading 3',5,6);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(4,'Heading 4',7,8);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(5,'Heading 5',9,16);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(6,'Heading 5.1',10,11);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(7,'Heading 5.2',12,13);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(8,'Heading 5.3',14,15);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(9,'Heading 6',17,18);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(10,'Heading 7',19,20);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(11,'Heading 8',21,42);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(12,'Heading 8.1',22,31);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(13,'Heading 8.1.1',23,24);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(14,'Heading 8.1.2',25,26);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(15,'Heading 8.1.3',27,28);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(16,'Heading 8.1.4',29,30);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(17,'Heading 8.2',32,33);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(18,'Heading 8.3',34,35);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(19,'Heading 8.4',36,37);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(20,'Heading 8.4.1',38,39);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(21,'Heading 8.4.2',40,41);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(22,'Heading 9',43,44);
INSERT INTO paragraphs (id,title,lft,rgt)VALUES(23,'Heading 10',45,46);

My table that save the specific structure for a client looks like this:

CREATE TABLE document (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client INT NOT NULL,
    paragraph INT NOT NULL,
    lft INT NOT NULL,
    rgt INT NOT NULL,
    FOREIGN KEY (paragraph) REFERENCES paragraphs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and for this table, here is an example insert:

INSERT INTO document (client,paragraph,lft,rgt)VALUES(1,1,1,2);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(1,11,3,16);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(1,12,4,9);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(1,15,5,6);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(1,16,7,8);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(1,17,10,11);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(1,18,12,13);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(1,19,14,15);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(1,23,17,18);

INSERT INTO document (client,paragraph,lft,rgt)VALUES(2,9,1,2);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(2,21,3,4);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(2,22,5,6);

INSERT INTO document (client,paragraph,lft,rgt)VALUES(3,5,1,6);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(3,6,2,3);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(3,7,4,5);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(3,11,7,10);
INSERT INTO document (client,paragraph,lft,rgt)VALUES(3,16,8,9);

Now i have try to get all sorted paragraphs of a specific document and it work with the following query:

SELECT DISTINCT para.title
FROM document AS node,
document AS parent
INNER JOIN paragraphs AS para ON para.id = parent.paragraph
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.client = 1
ORDER BY node.lft;

But if i try to get the depth of the nodes (how describes here for example) it's not work correctly and i don't know why..

SELECT para.title, (COUNT(parent.id) - 1) AS depth
FROM document AS node,
document AS parent
INNER JOIN paragraphs AS para ON para.id = parent.paragraph
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.client = 1
GROUP BY para.title
ORDER BY node.lft;

Can you help me? :)

Possible duplicate of https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — philipxy, Jun 05 '19 at 04:31
What do you mean "get the depth of the nodes"? Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. To describe a result: Say enough that someone could go away & come back with a solution. Please give example output. A query that doesn't work doesn't tell us what you want. [mcve] PS Please paraphrase or quote from other text that is needed to understand your question. Make your post self-contained. Connect what is says to things you say in your question. PS Where is your "nested_category" table per the link's section "The Nested Set Model"? — philipxy, Jun 05 '19 at 04:57
The depth of the nodes is the number of parents until the root. I have explain that i have split the "nested_category" table because every document can different order of the paragraphs. Therefore my "nested_category" table consists of "paragraphs "and "document" table and that was my problem otherwise the website i have linked have the perfect solution but i have split the table and don't know how can i get the depth of the nodes now. — Basti G., Jun 05 '19 at 08:17
@philipxy no its not duplicate because i have split the table and in your linked thread i can't find a solution for my problem. — Basti G., Jun 05 '19 at 08:24
Yes, 'the "nested_category" table consists of "paragraphs "and "document" table'--so join that table to itself the way the link you give does. PS Just because you can't find the answer in a question & answer does not mean it is not a duplicate. — philipxy, Jun 05 '19 at 08:55
For example, i try to get the route to "Heading 8.1.4", so the query is believed to get all parents of the title. `SELECT * FROM standard_order AS st1 JOIN standard_order AS st2 ON st1.paragraph = st2.paragraph INNER JOIN paragraphs AS p1 ON st1.paragraph = p1.id INNER JOIN paragraphs AS p2 ON st2.paragraph = p2.id WHERE st1.lft BETWEEN st2.lft AND st2.rgt AND p1.title = 'Heading 8.1.4' ORDER BY st2.lft;` but the query gives me only the title in the where clause...why.. — Basti G., Jun 05 '19 at 12:04

SQL hierarchical document with nested sets

0 Answers0