CREATE TABLE `folder_s` ( `folder_id` int(10) NOT NULL AUTO_INCREMENT, `folder_name` varchar(10) DEFAULT NULL, `folder_type` varchar(10) DEFAULT NULL, `creation_date` varchar(20) DEFAULT NULL, `author` varchar(20) DEFAULT NULL, `parent_id` int(11) NOT NULL, PRIMARY KEY (`folder_id`), KEY `bbbb_idx` (`parent_id`), CONSTRAINT `FKparent_folder` FOREIGN KEY (`parent_id`) REFERENCES `folder_s` (`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE CREATE TABLE `document_s` ( `document_id` int(10) NOT NULL AUTO_INCREMENT, `document_name` varchar(20) DEFAULT NULL, `document_type` varchar(20) DEFAULT NULL, `creation_date` varchar(20) DEFAULT NULL, `parent_id` int(10) DEFAULT NULL, PRIMARY KEY (`document_id`), KEY `aaa` (`parent_id`), CONSTRAINT `aaa` FOREIGN KEY (`parent_id`) REFERENCES `folder_s` (`folder_id`) ON DELETE CASCADE ON UPDATE NO ACTION

Folder_s elements are folders that can contain other folders as well as documents from document_s.

Thus, if I have data for example

 INSERT INTO `folder_s` VALUES (1,'/','/','2017/02/21','admin',1), (2,'root','root','2017/02/21','admin',1), (3,'Folder A','Dosar','2017/02/26 23:27','nolek',2), (4,'Folder B','Catalog','2017/02/26 23:27','nolek',2), (5,'Folder C','Pachet','2017/02/26 23:27','nolek',3), (6,'Folder D','Dosar','2017/02/26 23:28','nolek',4); (7,'Folder E','Dosar','2017/02/26 23:28','nolek',5); (8,'Folder F','Dosar','2017/02/26 23:28','nolek',6); INSERT INTO `document_s` VALUES ('1', 'DocumentA', 'Dosar', '2017/02/26 23:27', '2'), ('2', 'DocumentB', 'DocExt', '2017/02/26 23:27', '2'), ('3', 'DocumentC', 'Dosar', '2017/02/26 23:27', '5'), ('4', 'DocumentD', 'DocExt', '2017/02/26 23:27', '5'), ('5', 'DocumentE', 'Dosar', '2017/02/26 23:27', '6'), ('6', 'DocumentF', 'DocExt', '2017/02/26 23:27', '6'), ('7', 'DocumentG', 'Dosar', '2017/02/26 23:27', '7'), ('8', 'DocumentH', 'DocInt', '2017/02/26 23:27', '8');

All that is in the root is a zero level. And you need to make a selection of documents of the 3rd level, that is

 Root FolderA FolderC FolderE DocumentG DocumentC DocumentD FolderB FolderD FolderF DocumentH DocumentE DocumentF DocumentA DocumentB

In this tree it will be

 DocumentC DocumentD DocumentE DocumentF

Tell me how it can be done in MySQL and how to do it at all.

  • @Mike You are right about arbitrary, okey, and if not in MySQL to do it, how should the recursive query look like - Evgeny
  • Here, for example, ru.stackoverflow.com/questions/573753, the main thing here is the Level column in which the current nesting level is considered and in the final part of the query you can safely give where level = 3 - Mike
  • @Mike Could you give me an example of the procedure? - Eugene
  • Well, everything is simple. The temporary table contains the id and level fields. initially insert into it the id of the root record. then we go in a loop from 1 to N (3) and do literally insert into tmp(id, level) select id,N from folder_s where parent_id in(select id from tmp where level=N-1) and finally select documents whose parent is in tmp with the appropriate level - Mike
  • @ Mike and I don’t understand how to do it in the forehead, it is possible to go to level 3 specifically - Eugene

1 answer 1

Unfortunately, there are no recursive queries in MySQL and you can only

 select * from ( select @grp:=(select group_concat(folder_id) from folder_s where find_in_set(parent_id,@grp)>0 and folder_id!=parent_id ) grp, @level:=@level+1 level from folder_s, (select @grp:='1',@level:=0) X limit 3 ) A join document_s on A.level=3 and find_in_set(parent_id,grp)>0

In connection with the use of find_in_set during the execution, you will need 3 full scans of the folder table and one full scan of the documents table.

It works quite simply, we get N rows from any table (in this case from the folder itself), limiting this amount to the limit . On each line by a subquery we get a list of child id for all parents whose id is in the list in the variable @grp, obtained in the previous line. Thus we emulate a cycle by levels.

For faster work, you can make a stored procedure that implements a real loop and at each level gets the same list of child folders but not as a string, but by line-by-line saving into a temporary table.

In principle, if the level is fixed 3rd, you can of course solve the problem in the usual way:

 select D.* from folder_s L1, folder_s L2, folder_s L3, document_s D where L1.parent_id=1 and L1.folder_id!=L1.parent_id and L2.parent_id=L1.folder_id and L3.parent_id=L2.folder_id and D.parent_id=L3.folder_id