complex Eloquent relationship & query builder in Laravel involving multiple models with different types of relationships

 

Scenario

1. A User can belong to multiple Roles (Many-to-Many).
2. Each Role can have multiple Permissions (Many-to-Many).
3. A Post belongs to a User (One-to-Many).
4. A Comment belongs to a Post (One-to-Many) and to a User (One-to-Many).

Users Table

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamps();
});




Roles Table
Schema::create('roles', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});


Role_User Pivot Table
Schema::create('role_user', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->foreignId('role_id')->constrained()->onDelete('cascade');
});



Permissions Table
Schema::create('permissions', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});


Permission_Role Pivot Table
Schema::create('permission_role', function (Blueprint $table) {
    $table->id();
    $table->foreignId('role_id')->constrained()->onDelete('cascade');
    $table->foreignId('permission_id')->constrained()->onDelete('cascade');
});


Posts Table
Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->string('title');
    $table->text('content');
    $table->timestamps();
});


Comments Table
Schema::create('comments', function (Blueprint $table) {
    $table->id();
    $table->foreignId('post_id')->constrained()->onDelete('cascade');
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->text('content');
    $table->timestamps();
});



Eloquent Relationships
User Model
class User extends Model
{
    public function roles()
    {
        return $this->belongsToMany(Role::class);
    }

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

    public function comments()
    {
        return $this->hasMany(Comment::class);
    }

    public function permissions()
    {
        return $this->roles()->with('permissions');
    }
}


Role Model

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

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

Permission Model

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

Post Model

class Post extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}


Comment Model
class Comment extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

Query Examples
Get a user's roles and permissions:
$user = User::with('roles.permissions')->find($userId); 
Get all posts by a user's roles:

$posts = Post::whereHas('user.roles', function ($query) {
    $query->where('name', 'Admin');
})->get();

Get all comments for a user's posts:
$comments = Comment::whereHas('post.user', function ($query) use ($userId) {
    $query->where('id', $userId);
})->get();
Get all permissions for a user:
$permissions = $user->permissions->pluck('name');

Check if a user has a specific permission:
$hasPermission = $user->permissions->contains('name', 'edit-post');




Query builder:
Scenario
Retrieve all posts with the following:
The post's title, content, and the author's name (from the users table).
The total number of comments on the post.
Include only posts with more than 10 comments.
Filter by posts created in the last 30 days.
Only include authors who have the Admin role.

use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

$posts = DB::table('posts')
    ->select('posts.id', 'posts.title', 'posts.content', 'users.name as author_name', DB::raw('COUNT(comments.id) as total_comments'))
    ->join('users', 'posts.user_id', '=', 'users.id') // Join with users table to get the author's name
    ->join('role_user', 'users.id', '=', 'role_user.user_id') // Join with the pivot table for roles
    ->join('roles', 'role_user.role_id', '=', 'roles.id') // Join with roles table
    ->leftJoin('comments', 'posts.id', '=', 'comments.post_id') // Join with comments table to count comments
    ->where('roles.name', 'Admin') // Filter by role name
    ->where('posts.created_at', '>=', Carbon::now()->subDays(30)) // Filter posts from the last 30 days
    ->groupBy('posts.id', 'posts.title', 'posts.content', 'users.name') // Group to calculate the total comments per post
    ->havingRaw('COUNT(comments.id) > ?', [10]) // Only include posts with more than 10 comments
    ->orderByDesc('total_comments') // Order by the total number of comments
    ->get();

Explanation
Joins:
users: To get the author's name.
role_user and roles: To ensure the author has the Admin role.
comments: To calculate the total number of comments per post.
Filters:
Filter posts created within the last 30 days using Carbon::now()->subDays(30).
Filter authors who have the Admin role using roles.name.
Aggregations:
Use COUNT(comments.id) to calculate the total number of comments per post.
GROUP BY ensures the aggregation works properly.
Having Clause:
Filters groups with more than 10 comments using HAVING.
Sorting:
Sort posts in descending order of total comments.
Sample Output
The query might return a collection like this:
json

[
  {
    "id": 1,
    "title": "How to Use Laravel",
    "content": "This is a guide to Laravel.",
    "author_name": "John Doe",
    "total_comments": 15
  },
  {
    "id": 2,
    "title": "Advanced Laravel Tips",
    "content": "Tips for advanced Laravel users.",
    "author_name": "Jane Smith",
    "total_comments": 12
  }
]


Example 1: Nested Subqueries and Aggregates
Retrieve a list of users with:
Their total post count.
The latest post's title.
Only users who have written more than 5 posts.
$users = DB::table('users')
    ->select('users.id', 'users.name', DB::raw('COUNT(posts.id) as total_posts'), 
        DB::raw('(SELECT title FROM posts WHERE posts.user_id = users.id ORDER BY created_at DESC LIMIT 1) as latest_post_title'))
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->groupBy('users.id', 'users.name')
    ->having('total_posts', '>', 5)
    ->orderByDesc('total_posts')
    ->get();

Example 2: Multi-Level Filtering with Nested Joins
Retrieve all comments made by users who have the Editor role and include:
Comment content.
Post title.
User's name.
Only include posts created in the last 60 days.
$comments = DB::table('comments')
    ->select('comments.content as comment_content', 'posts.title as post_title', 'users.name as user_name')
    ->join('posts', 'comments.post_id', '=', 'posts.id')
    ->join('users', 'comments.user_id', '=', 'users.id')
    ->join('role_user', 'users.id', '=', 'role_user.user_id')
    ->join('roles', 'role_user.role_id', '=', 'roles.id')
    ->where('roles.name', 'Editor')
    ->where('posts.created_at', '>=', Carbon::now()->subDays(60))
    ->orderBy('posts.created_at', 'desc')
    ->get();


Example 3: Paginated Results with Filters and Sorting
Retrieve paginated results of posts with:
The post's title, content, and author.
The number of comments on each post.
Sorted by the most commented posts.
$posts = DB::table('posts')
    ->select('posts.id', 'posts.title', 'posts.content', 'users.name as author', DB::raw('COUNT(comments.id) as total_comments'))
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
    ->groupBy('posts.id', 'posts.title', 'posts.content', 'users.name')
    ->orderByDesc('total_comments')
    ->paginate(10);

Example 4: Using Raw Queries for Complex Filters
Retrieve all users who have:
At least one post with more than 50 comments.
Include the count of such posts.
$users = DB::table('users')
    ->select('users.id', 'users.name', DB::raw('COUNT(posts.id) as qualifying_posts'))
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->whereRaw('(SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id) > 50')
    ->groupBy('users.id', 'users.name')
    ->having('qualifying_posts', '>', 0)
    ->get();


Example 5: Complex Query with Multiple Where Clauses and OR Conditions
Retrieve products with:
A price between $100 and $500 or a category of Electronics.
Only those created in the last 30 days.
$products = DB::table('products')
    ->select('id', 'name', 'price', 'category', 'created_at')
    ->where(function ($query) {
        $query->whereBetween('price', [100, 500])
              ->orWhere('category', 'Electronics');
    })
    ->where('created_at', '>=', Carbon::now()->subDays(30))
    ->orderBy('created_at', 'desc')
    ->get();


Example 6: Hierarchical Data Using Self-Joins
Retrieve categories and their subcategories:
Include the parent category name and subcategory name.
$categories = DB::table('categories as parent')
    ->select('parent.name as parent_name', 'child.name as subcategory_name')
    ->leftJoin('categories as child', 'parent.id', '=', 'child.parent_id')
    ->whereNotNull('child.parent_id')
    ->orderBy('parent.name')
    ->get();

Example 7: Query with Multiple Aggregates
Retrieve products with:
The average price of products in the same category.
The total stock of all products in the same category.
$products = DB::table('products')
    ->select('category', DB::raw('AVG(price) as avg_price'), DB::raw('SUM(stock) as total_stock'))
    ->groupBy('category')
    ->having('avg_price', '>', 50) // Only include categories with an average price > 50
    ->get();


Example 8: Combining Multiple Subqueries
Retrieve the top 5 users who have:
The highest total number of comments.
The sum of their posts’ likes.
$topUsers = DB::table('users')
    ->select('users.id', 'users.name', 
        DB::raw('(SELECT COUNT(*) FROM comments WHERE comments.user_id = users.id) as total_comments'),
        DB::raw('(SELECT SUM(likes) FROM posts WHERE posts.user_id = users.id) as total_likes'))
    ->orderByDesc(DB::raw('total_comments + total_likes'))
    ->limit(5)
    ->get();