CODEWITHSUNDEEP
phpmysqllaraveleloquent
Ricky Barnett
Ricky Barnett

Reputation: 1170

Laravel recursive relations

I have the following tables:

directories
    id
    parent_directory_id

users
    id

user_directory_access
    directory_id
    user_id

Example:

I need to find directories with an id of 45 that has any recursive parent_directory_id that is present in the user_directory_access table that has a user_id of 3

I'd ideally like a user to automatically inherit access to a directory if they have access to any of the parent directories (recursive throughout the hierarchy) so for example if I have access to the root directory I should be allowed access to all children via 1 record stored in the user_directory_access table.

What would be a viable solution? I'm open to restructuring if required.

Upvotes: 0

Views: 90

Answers (1)

solarc
solarc

Reputation: 5738

You could have a map (cached in memcache or a file or in another table) of directory_id and the list of parent_directory_id of its parents, so for this structure:

1
┣━━━2
┃   ┗━━━4
┃       ┗━━━5
┗━━━3

Your map would look like this:

$parents_map = [
    1 => [],
    2 => [1],
    3 => [1],
    4 => [2, 1],
    5 => [4, 2, 1],
];

Whenever you add a new child directory, look up the parents_map of its own parent and add it to the map:

// new directory 6 whose parent is 3:
$new_id = 6;
$parent_id = 3;
$parents_map[$new_id] = array_merge([$parent_id], $parents_map[$parent_id]);

Then when you need to check the permissions for a directory, look it up in the map and see if any of its parents have permission.

$directory_id = 6;
foreach($parents_map[$directory_id] as $parent_id) {
    check_permissions($user_id, $parent_id);
}

Upvotes: 2

Related Questions