How to count and follow foreign keys from a foreign key ID

Question

So basically I have a table called folders, which store data which I use to build up a file structure on a website, now one of the limits I wish to impose is only allowing the user to make 3 folders deep, as in they can only make 3 folders in like so:

Folder 1
File 1.1
    >
    Folder 2
    File 2.1
        >
        Folder 3
        File 3.1

Hopefully that visualises how far I want to go.

The table has: folderID, folderName and parentFolderID.

parentFolderID is set to NULL if that folder does not below to another folder, if a folder does belong to another folder then parentFolderID is set to another folders folderID.

How could I use SQL to check how deep a user has gone already, so that I can return an error and say they have reached they maximum folder depth? Is this sort of thing possible in SQL or should I process this backend?

UPDATE:

Since using information from here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql

I came up with this:

    SELECT node.folderName, (COUNT(parent.folderID))
    FROM folders AS node,
    folders AS parent
    WHERE node.folderID BETWEEN parent.parentFolderID AND parent.folderID
    GROUP BY node.folderName
    ORDER BY node.folderName;

But when it checks on a folder which is the third one deep, it only returns 1 as what was counted when it should be 3? What am I doing wrong?

Which database are you using? Oracle is very good at traversing trees. — McMurphy, Nov 12 '17 at 23:03

score 1 · Answer 1 · answered Nov 12 '17 at 23:19

1

Shame. I don't know how to do tree-walking with mySQL but as you've set a hard limit at 3 you could something like: -

select 
    child.folder_id,
    parent.folder_id,
    grand_parent.folder_id
from
    folder child
     left outer join
    folder parent
      on parent.folder_id = child.parent_folder_id
      left outer join
    folder grand_parent
      on grand_parent.parent_folder id = parent.parent_folder_id
;

If you do that check in a BEFORE INSERT trigger then if grand_parent.folder is not NULL you reject the insert.

answered Nov 12 '17 at 23:19

McMurphy

1,235
1
15
39

If you can, try to avoid unnecessary `LEFT OUTER JOIN`, they are slower than `INNER JOIN` ( cf https://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server/2726683#2726683 ) especially when you put it in trigger that'll be run on every single insert ;) – Blag Nov 12 '17 at 23:42
The outer join was intentional as I expect ant and all antecedent information to be useful but I agree with you in general. Having said that, if this were Oracle, I would remove any artificial hard-limits and use select BY PRIOR. – McMurphy Nov 12 '17 at 23:53

Blag · Accepted Answer · 2017-11-12T23:32:06.670

1

You can get all folderID of Deep 3 that should not allow children:

SELECT p3.folderID
FROM folders p1
    INNER JOIN folders p2
            ON p1.folderID = p2.parentFolderID
        INNER JOIN folders p3
                ON p2.folderID = p3.parentFolderID
WHERE p1.parentFolderID IS NULL

With the good stored procedure :

CREATE PROCEDURE p(
    IN parentFolderID int,
    IN folderName varchar(15)
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM folders p1
            INNER JOIN folders p2
                    ON p1.folderID = p2.parentFolderID
                INNER JOIN folders p3
                        ON p2.folderID = p3.parentFolderID
        WHERE
            p1.parentFolderID IS NULL
            AND p3.folderID = @parentFolderID
    )
    THEN
        INSERT INTO folders(`folderName`, `parentFolderID`)
        VALUES(@folderName, @parentFolderID);
    END IF;
END
//

edited Nov 12 '17 at 23:32

answered Nov 12 '17 at 23:21

Blag

5,818
2
22
45

This is really good, I will test it out, but is there a way to make it check as far as 20 without having to write out 20 joins? – Erdss4 Nov 12 '17 at 23:31
1

@Erdss4 that mean mandatory recursion: you'll have to make a procedure that'll call herself and check the deep. If you don't want to use recursion, you can take a look at this : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql probably the best way to handle what you want without recursion. If you want more information about this, let my know, I'm pretty familiar of it ;) – Blag Nov 12 '17 at 23:35
Hey I've updated my question and used the link you sent, but having issues, could you take a look? – Erdss4 Nov 13 '17 at 01:06
@Erdss4 read again the link I give you, i think you missed the point of the right/left value... – Blag Nov 13 '17 at 12:56
Oh so I guess my model of using Adjacency Lists is a bad idea of having folderID (Main Index) and parentFolderID (Pointer to main Index)? Should I be storing this differently in the database, I'm really stuck on where to look now? – Erdss4 Nov 13 '17 at 17:58
@Erdss4 the question is really about how deep you want your folders ? if 3 to 5, you can keep as you do with self join `parent`. If more than 5, but without a heavy load (aka just a personal website / not performance thirsty) you can switch to a recursive Proc that'll do the job. If more than 5 and a real load to handle, you'll need to remove your `parentID` for folder (keep it for file in a table `file`) and change the DB to add a `left` / `right` field that follow the link I gave you ;) – Blag Nov 15 '17 at 00:28

How to count and follow foreign keys from a foreign key ID

2 Answers2