Laravel Eloquent get all Posts stored in categories with Roles

Question

I have four tables in a database named: Category, User, Role and then Post. In the Category table I have a column category_id which by this column i can have multiple child in category.

Every user belongsToMany roles and categories and each category belongsToMany posts, i must get all posts by role which logged into our application

As you can see in below screen shot manager 1 and manager 2 belongsToMany programings, dart, flutter and php.

you can suppose manager 1 user id is 1 and manager 2 is 2 and both of them are manager role

my question is how can i get all posts which logged user belongsToMany categories by role

logged user is manager 1 and i want to get all posts which saved into categories from parent which that's PROGRAMINGS

for example:

$categories = Category::whereNull('category_id')->whereHas('users.roles', function($q){
    return $q->whereLabel('is-manager');
})->with(['posts' => function ($query) {
    $query->with('language');
}])->get();
dd($categories->pluck('posts'));

NOTE:

with @Med.ZAIRI answer which posted on this thread every user in MANAGER 2 which is't synced into MANAGER 1, can see all of MANAGER 1 posts

In the Model Category add a relationship, like:

/**
* this will get the parent category
*/
public function parentCategory()
{
    return $this->belongsTo( Category::class, 'category_id', 'id' );
}

Then, try to get Posts with their categories and their parent Categories, and the users with their Roles, like:

$posts = Post::with( ['category.parentCategory', 'user.roles'])->get()

my used models in this senario:

class Category extends Model
{
    use SoftDeletes;

    protected $guarded = ['id'];
    protected $hidden = ['id', 'category_id'];

    public function parentCategory()
    {
        return $this->belongsTo( Category::class, 'category_id', 'id' );
    }

    public function categories()
    {
        return $this->hasMany(Category::class);
    }

    public function roles()
    {
        return $this->belongsToMany(Role::class);
    }

    public function posts()
    {
        return $this->belongsToMany(Post::class);
    }

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

    public function childrenCategories()
    {
        return $this->hasMany(Category::class)->with('categories');
    }
}


class Role extends Model
{
    protected $guarded = ['id'];

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

    public function permission()
    {
        return $this->belongsToMany(Permission::class);
    }

    public function hasPermission($permission)
    {
        return !!$permission->intersect($this->roles->permission)->count();
    }
}


class User extends Authenticatable
{
    use Notifiable, SoftDeletes, UsersOnlineTrait;

    protected $guarded = [
        'id',
    ];
    protected $hidden = [
        'password', 'remember_token',
    ];

    protected $casts = [
        'email_verified_at' => 'datetime',
        'avatar_path' => 'array',
        'experiences' => 'array',
    ];

    public function group()
    {
        return $this->belongsToMany(UserGroup::class, 'user_user_group');
    }

    public function child()
    {
        return $this->hasMany(User::class)->with('child');
    }

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

    public function properties()
    {
        return $this->hasOne(UsersProperty::class);
    }

    public function roles()
    {
        return $this->belongsToMany(Role::class);
    }

    public function hasRole($role)
    {
        return $this->roles->contains('id', $role);
        /*if (is_string($role)) {
        } else {
            return !!$role->intersect($this->roles)->count();
        }*/
    }

    public function hasRoleByName($role)
    {
        if ($role == null) return false;
        if (is_string($role)) {
            return $this->roles->contains('name', $role) || $this->roles->contains('label', $role);
        } else {
            return !!$role->intersect($this->roles)->count();
        }
    }

    public function categories()
    {
        return $this->belongsToMany(Category::class);
    }

}

Answer 1

Trait with methods to get all available posts for the logged in user based on the role

class User extends Model
{
    use HasFactory, Notifiable, HasPosts;
}



<?php

namespace App\Concerns;

use App\Models\Category;
use Illuminate\Support\Str;
use Illuminate\Support\Collection;

trait HasPosts
{
    /**
     * Get all available posts for the currently logged in user
     *
     * @return void
     */
    public function posts()
    {
        $method = 'postsFor' . Str::studly(str_replace('is-', '', $this->roles->first()->label));

        return $this->{$method}()->collapse();
    }

    /**
     * Get all posts associated with all categories including
     * their subcategories for the logged in Portal Manager
     */
    public function postsForPortalManager(): Collection
    {
        return Category::with([
            'subcategories.posts.language', 
            'posts.language'
        ])
        ->get()
        ->pluck('posts');
    }

    /**
     * Get all posts for the logged in Manager which belong to
     * one of the categories associated with the Manager
     */
    public function postsForManager(): Collection
    {
        return $this->categories()
            ->with('posts.language')
            ->get()
            ->pluck('posts')
            ->filter(function ($collection) {
                return !!$collection->count();
            });
    }

    /**
     * Get only the posts which belong to the categories for the Editor
     * and which are authored by the logged in Editor
     */
    public function postsForEditor(): Collection
    {
        return $this->categories()
            ->with([
                'posts' => function ($query) {
                    $query->where('user_id', $this->id)->with('language');
                }, 
                'posts.language'
            ])
            ->get()
            ->pluck('posts')
            ->filter(function ($collection) {
                return !!$collection->count();
            });
    }

    /**
     * Get only the posts which belong to the categories for the Writer
     * and which are authored by the logged in Writer
     */
    public function postsForWriter(): Collection
    {
        return $this->categories()
            ->with([
                'posts' => function ($query) {
                    $query->where('user_id', $this->id)->with('language');
                }, 
                'posts.language'
            ])
            ->get()
            ->pluck('posts')
            ->filter(function ($collection) {
                return !!$collection->count();
            });
    }
}

Then for any authenticated user we can fetch available posts with

$user->posts()

It works with the seed data you have provided.

ella is not able to see the post on Flutter and scarlett is not able to see the post on laravel

Only assumption here is that have taken the first role of the authenticated user to lookup the method used.