A more dynamic query (mysql)

Question

I have data from a document management system. This data has directories & files. This data is stored in one table.

CREATE TABLE `Folders` (
  `id` int NOT NULL,
  `type` enum('DIRECTORY','FILE') NOT NULL DEFAULT 'DIRECTORY',
  `name` varchar(150),
  `parentId` int,
  `ownerId` int NOT NULL
);

Important thing here to note is that a directory could in theory have n number of subdirectories.

INSERT INTO Folders VALUES 
 (13,'DIRECTORY','Home',1,5),
 (14,'DIRECTORY','Dependants',13,5),
 (15,'DIRECTORY','Aaron Jacobs',14,5),
 (16,'FILE','Aaron Jacobs Passport.pdf',15,5),
 (17,'FILE','Aaron Jacobs ID.pdf',15,5),
 (18,'FILE','Aaron Jacobs Drivers License.pdf',15,5);

Fields id and parentId form the relation,with an id having a parentid. In the above id's 16,17 & 18 have parentId 15, meaning these three documents belong in directory Aaron Jacobs .

The document data such as document number is stored in a different table,

CREATE TABLE `Documents` (
  `documentId` int,
  `folderFileId` int,
  `documentNumber` varchar(132) 
) ;

Here folderFileId will be used to join Documents to the Folders table

INSERT INTO Documents VALUES (1,16,'PA123456'),(2,17,'ID123456'),(3,18,'DL123456');

I am trying to write a query that will give the document number for each file. The query below will give the result for a folder structure shown in the data above,

SELECT 
    `subfolders2`.`name` AS `FileName`,
    `subfolders`.`name` AS `Foldername`,
    `documents`.documentNumber
FROM
    `Folders` `folder`
        JOIN
    `Folders` `subfolders` ON (`folder`.`id` = `subfolders`.`parentId`)
        AND (`folder`.`type` = 'DIRECTORY')
        AND (`folder`.`name` = 'Dependants')
        JOIN
    `Folders` `subfolders2` ON (`subfolders`.`id` = `subfolders2`.`parentId`)
         AND `subfolders2`.`type` = 'FILE'
        JOIN
    `Documents` `documents` ON (`documents`.`folderFileId` = `subfolders2`.`id`);

which gives the result as,

FileName                        Foldername      documentNumber
Aaron Jacobs Passport.pdf         Aaron Jacobs     PA123456
Aaron Jacobs ID.pdf               Aaron Jacobs     ID123456
Aaron Jacobs Drivers License.pdf  Aaron Jacobs     DL123456

However, this would imply adding more/less joins on the Folders table for a structure that differs from the above. Would there be a more efficient way to write the query?

dbfiddle

The question isn't very clear, but I think what you need is a recursive CTE. — Barmar, Apr 29 '22 at 19:41
Besides recursive queries (which I only did in Oracle SQL), you could maintain an ANCESTOR cross table with (ID, ANCESTORID, LEVEL INT) where LEVEL 1 is immediate parent, 2 parent of parent and so on. Maybe with INSERT and DELETE triggers. Instead of LEVEL I also saw _distance to root_. — Joop Eggen, Apr 29 '22 at 19:48
@Barmar so in the above data we have three directories. My query will have to be refactored should another set of data come with only two directories or have more than three directories. — Bisoux, Apr 29 '22 at 19:58
That's why you would use a recursive CTE. It can handle any level of nesting automatically. — Barmar, Apr 29 '22 at 19:59
Thank you @Barmar. I created a recursive CTE and it works :) — Bisoux, Apr 29 '22 at 20:37

A more dynamic query (mysql)

0 Answers0