Laravel eloquent where condition parent/children with 3rd model many to many

Question

I have 2 models User and Modules Modules has a recursive parent/children relation having Many childrens (SubModules).

In other hand a User can Have Many Modules and Many Submodules (permissions to access them) so they're in a N to N table users_modules

So I want to extract all Modules of the users, but these Modules only needs to include subModules related with the user.

For Now I have something like this in my controller:

auth()->user()->modules->toArray();

but I don't know how can I filter those subModules.

This are my Models (without any changes because all I've tried is not working):

User

class User extends Authenticatable
{
   ...
    public function modules()
    {
        return $this->belongsToMany(Module::class, 'modules_users')->where('group', null)->with('subModules');
    }

Module

class Module extends Model
{
   ...

    public function users()
    {
        return $this->belongsToMany(User::class, 'modules_users');
    }

    public function subModules()
    {
        return $this->hasMany(Module::class, 'group');
    }

    public function parent(): BelongsTo
    {
        return $this->belongsTo(Module::class, 'group');
    }
}

Can anybody help me?

Answer 1

Edit

Based on the comments we have had back and forth, I think I understand the problem you are having. You need to access the user.modules.subModules where subModules belong to the user and maintain the related tree of your modules object.

The good news is that Laravel does allows query logic injection in these with() calls. You can do something like:

$userId = 5; // auth()->id();
$user = User::query()->where('id', $userId)
    ->with('modules.subModules', function ($query) {
        $query->whereHas('users', fn ($subQuery) => $subQuery->where('id', $userId));
    })
    ->first();

dd($user->toArray());

This outputs locally for me the following object:

Now just as a proof-of-concept, if I attach another subModule with a parent_id set to 1, I should see that in my sub_modules object

My recommendation does stand to look at the Library I recommended in my original answer, but the above query is fairly simple as it stands, there are limitations to how it's fetching the data and creating your object, but this all depends on how complex you will need to get.

---

Original Answer

There are a couple of solutions here, and I think the best approach will depend a little on your data structure.

The easiest and probably the best way you are trying to access the data would be to use Laravel's . syntax in the first parameter of your with() call.

$user = User::inRandomOrder()->with('modules.subModules')->first()

The main downside to this approach is the depth of your subModules(). This would be limited to the immediate children of any module the user may belong to.

If I update the query with another level of subModules you can see that my module 6 has their own children.

$user::query()->where('id', 5)->with('modules.subModules.subModules')->first();

Another possible solution would be to at a 3rd party library to help you access nested relationships easier.

Take a look at kalnov/nestedset, there's a lot there, but it would add the following methods to your models:

$result = Category::whereDescendantOf($node)->get();
$result = Category::whereNotDescendantOf($node)->get();
$result = Category::orWhereDescendantOf($node)->get();
$result = Category::orWhereNotDescendantOf($node)->get();
$result = Category::whereDescendantAndSelf($id)->get();

// Include target node into result set
$result = Category::whereDescendantOrSelf($node)->get();

Which my guess that is probably what you are looking for in an application context where the content the user is trying to access has be traced back to the user in some way. That can easily get hairy if the user can belong to different levels of Modules as well.