how implement a folder, subfolder in a Database

Question

I'm having a problem with logic programming, but I think that could be another solution much easier to do this in a SQL command, however, let's go to the problem.

I have a table in MySQL (InnoDb) like this:

CREATE TABLE IF NOT EXISTS `folder` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `folder_id` INT NOT NULL ,
  `name` VARCHAR(100) NULL ,
  `hidden` TINYINT(1)  NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_folder_folder1` (`folder_id` ASC) ,
  CONSTRAINT `fk_folder_folder1`
    FOREIGN KEY (`folder_id` )
    REFERENCES `folder` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

It seems like a pretty simple, but I want to list the folders, in a way that the subfolders of this folder will appear below it. Like in any file repository.

For example:
In the table:

id  name    folder_id
1   root    1
2   etc     1
3   dev     1
4   apache  2
5   php     2
6   mysql   2
7   hda     3
8   hda1    3
9   lib     1

I want to show it like this:

root
    etc
        apache
        php
        mysql
    dev
        hda
        hda1
    lib

The question is, have a way to do this by SQL command or in PHP?
I try make in PHP, and I don't see a way without a recursive function. But i haven't success.

Solution:
Because the MySQL doesn't accept the WITH clause, the recursive function in SQL is out of the box. Thanks guys.

SELECT c2.name
FROM folder c1 LEFT OUTER JOIN folder c2
ON c2.folder_id = c1.id WHERE c2.folder_id = 1  // the id of the parent folder
AND c2.id != c2.folder_id               // Remove the parent folder
ORDER BY c2.folder ASC

possible duplicate of [Sorting tree with a materialized path?](http://stackoverflow.com/questions/2797720/sorting-tree-with-a-materialized-path) — D'Arcy Rittich, Apr 05 '12 at 17:44

score 4 · Accepted Answer · answered Apr 05 '12 at 17:43

4

you're in danger of implementing an SQL anti pattern called naive trees. See the pdf of the book, it shows several good solutions.

answered Apr 05 '12 at 17:43

David Chan

7,347
1
28
49

2

This is only an anti-pattern with MySQL because it (still) does not support recursive queries. In other DBMS it's very efficient to deal with such a structure. – Apr 05 '12 at 17:46
1

I read the PDF that was linked but did not see an alternative to using a tree. If someone is going to crusade against an anti-pattern I think alternative solutions need to be proposed. IN the meantime I will continue to use either Oracle's connect by, write a stored proc or a CTE. And the very useful tree structure. – Karl Apr 05 '12 at 19:56
@Karl, In the end of the Chapter, have a recursive implementation for SQL using the WITH clause, but have some RDBMS that doesn't suport hierarchies stored (see section 3.4). – Guilherme Apr 06 '12 at 02:58

how implement a folder, subfolder in a Database

1 Answers1