2

I have an app using Laravel as the back-end API and AngularJS as the front-end, with a multi-tiered user tree, where users "own" groups of other users.

For instance there may be an owner who owns supervisors, who in turn own employees.

The employees have "walls" with posts and comments. Only employees have walls. Any user in his tree branch may view/post/comment on his wall - so his direct supervisor and the owner have access to his wall.

I'd like this to be extendable in the future to add more "roles" in between, so I don't want to have separate tables for each user type.

I've looked at self-reflected models, where the users table has a parent_id column. I was having a difficult time as the Auth::user->with('children') is returning all users, ignoring the relationship set up in the model:

public function children() {
  return $this->hasMany('User', 'parent_id');
}

public function parent() {
  return $this->belongsTo('User', 'parent_id');
}

The question is this - is there a package available that allows me to scope queries automatically by these hierarchical user relationships?

Or what advice do you have for a user paradigm like this? All searches I've tried and packages I've looked at lead nowhere. I think using a nested sets package is too complicated for this task. None of the auth packages I've looked at truly fit this paradigm. They allow roles and permissions, but don't scope permissions based on parent-child relationships.

halfer
  • 19,824
  • 17
  • 99
  • 186
jdforsythe
  • 1,057
  • 12
  • 22
  • It's difficult to understand the true requirements of what you're asking. Would you not simply have a table, which contains roles, and then a user_role pivot which allows you to query whether a user is either an employee, supervisor etc? You can then add a "query scope" for this. If I'm misunderstanding, please let me know as I hate being on a deadline and drawing blank on a single issue, so I'd be happy to help! – SixteenStudio Aug 12 '15 at 16:44
  • I have roles and permissions implemented already.Roles are defined as the job titles, and permissions are based on resources, allowing to assign create, read, update, or delete independently per-resource in the system. The problem comes in scoping the result sets. Not every supervisor can view data from every employee - they can only view data on the employees assigned to them. The roles and permissions part rightly keeps supervisor data away from employees, but doesn't narrow the employee set down per supervisor. My question is how to define this hierarchy in addition to roles and permissions – jdforsythe Aug 12 '15 at 17:07
  • Ok so before I post an answer - if I understand correctly - If I'm a supervisor and I have x number of children, and some of those children ALSO have x number of children, I can view data sets for all of my children and their sub-children and their sub-children's children? – SixteenStudio Aug 13 '15 at 09:04
  • Yes exactly - and for the time being the number of levels of children is set, but in the future it must be extendable. For instance, maybe in the future a "team leader" position is created between supervisors and employees. Bonus points if the relationships are defined in the database and adding to the hierarchy later doesn't require editing the code (i.e. the customer can change it at will) – jdforsythe Aug 13 '15 at 12:49
  • I have a feeling there's some combination of self-reflective queries (parent_id in users table) and traits/query scopes to solve this, but I'm hoping for something elegant! – jdforsythe Aug 13 '15 at 12:50

3 Answers3

1

For the time being, a hard-coded solution that works is to add a children relationship to the User model:

public function children() {
  return $this->hasMany('User', 'parent_id');
}

...and in the AuthController (where I want the children returned):

$children = Auth::user()->children()->with('children', 'children.children')->get();

This gives 3 levels deep and can easily be extended (although not by just adding to the database) by adding 'children.children.children'.

I can also check if it's an employee (the lowest level of the hierarchy) with:

if(empty($children->toArray()) {}
jdforsythe
  • 1,057
  • 12
  • 22
  • Can you explain this a little more please? Where to use this one? if(empty($children->toArray()) {} – lucasvm1980 Nov 14 '18 at 15:21
  • Due to the particular use case, the first and second levels (grandparents and parents, respectively) were guaranteed to have children, so I could determine whether a user was a third level (grandchild, employee in the example) by verifying that it had no children of its own. It could be used in a controller or elsewhere to verify the role of a user. However, it is really only useful in this exact type of use case. If any grandparent or parent record could have no children, then this check is useless. – jdforsythe Nov 20 '18 at 01:08
0

Please attempt the following - and this is for children only - untested!

Add this to your User model class

public function scopeBelongingTo($query, $parentId)
{
    return $query->whereHas('parent', function($query) use ($parentId)
    {
        $query->where('id', $parentId);
        $query->orWhereHas('parent', function($query) use ($parentId)
        {
           $query->where('id', $parentId);
            $query->orWhereHas('parent', function($query) use ($parentId)
            {
               $query->where('id', $parentId);
                $query->orWhereHas('parent', function($query) use ($parentId)
                {
                   $query->where('id', $parentId);
                    $query->orWhereHas('parent', function($query) use ($parentId)
                    {
                       $query->where('id', $parentId);
                    });
                });
            });
        });
    });
}

To find children (and grandchildren, grand-grandchildren, etc), attempt the following:

$idOfParent = 1; // Replace this with the ID of a parent you wish to query their children sub children
$users = User::belongingTo($idOfParent)->get();
dd($users->toArray()); // Should output the parent's childrens in the entire hierarchy down to tier 5...

If this doesn't work come back with the error and we'll see from there.

Important - This particular example would only work at 5 levels. To increase the levels, we'd have to include the whereHas. There is probably a way to do this in a relationship but as long as your application is well separated, it should be easy to swap this out down the line.

SixteenStudio
  • 1,016
  • 10
  • 24
  • There is no error, just an empty array. I have user records: id parent_id 1 null 2 1 3 1 4 2 5 2 6 3 7 3 – jdforsythe Aug 17 '15 at 18:09
  • (sorry it's all inline, the comments won't save line breaks?) There is no error, just an empty array. I have one owner with parent_id null, two supervisors with parent_id=1 (the owner), and four employees, a pair pointing to each of the supervisors. No matter which id I try to use, I get an empty result set. I also tried modifying it to return just a single level of children and still get an empty result array. – jdforsythe Aug 17 '15 at 18:16
  • I imagine this should work. When I get a moment I will set up a VM and test this out myself, but that sounds like a good temporary solution. I do understand the desire to make this cleaner - it really would be better suited in an eloquent relationship if it can be used for this, and logically from what I can see it should work. – SixteenStudio Aug 25 '15 at 11:27
0

The chosen answer will be even better by adding the method to itself -->with('children') so every child result will have its children in it (making it recursive). No need to use the dot notation which might be impossible in some cases.

public function children() {
   return $this->hasMany('User', 'parent_id')->with('children');
}

And your AuthController will be:

$children = Auth::user()->children()->get();
MartinG
  • 153
  • 1
  • 11