How to query a many-to-many relationship

Table Of Contents

• The Many-to-Many Reality
• Laravel Many-to-Many Query Patterns
• Advanced Many-to-Many Scenarios

The Many-to-Many Reality

Users have multiple roles, posts have multiple tags, products belong to multiple categories. These complex relationships need special querying techniques:

// Basic many-to-many setup
// User model
public function roles()
{
    return $this->belongsToMany(Role::class);
}

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

// Get users with their roles
$usersWithRoles = User::with('roles')->get();

// Get all users who have a specific role
$admins = User::whereHas('roles', function ($query) {
    $query->where('name', 'admin');
})->get();

// Get users with multiple specific roles
$moderators = User::whereHas('roles', function ($query) {
    $query->whereIn('name', ['admin', 'moderator']);
})->get();

Laravel Many-to-Many Query Patterns

Different ways to query complex many-to-many relationships:

// Posts with specific tags
$techPosts = Post::whereHas('tags', function ($query) {
    $query->where('slug', 'technology');
})->get();

// Posts with multiple required tags (AND condition)
$specificPosts = Post::whereHas('tags', function ($query) {
    $query->where('slug', 'laravel');
})->whereHas('tags', function ($query) {
    $query->where('slug', 'php');
})->get();

// Posts with any of multiple tags (OR condition)
$relatedPosts = Post::whereHas('tags', function ($query) {
    $query->whereIn('slug', ['laravel', 'php', 'vue']);
})->get();

// Count relationships
$usersWithRoleCounts = User::withCount('roles')
    ->having('roles_count', '>', 1)
    ->get();

// Advanced: Users with specific role and permission combination
$powerUsers = User::whereHas('roles.permissions', function ($query) {
    $query->where('name', 'delete_posts');
})->whereHas('roles', function ($query) {
    $query->where('name', 'admin');
})->get();

// Pivot table data access
$userRoles = User::find(1)->roles; // Collection of roles
foreach ($userRoles as $role) {
    echo $role->pivot->created_at; // Access pivot timestamps
}

// Custom pivot columns
// In User model:
public function roles()
{
    return $this->belongsToMany(Role::class)
        ->withPivot('assigned_at', 'assigned_by')
        ->withTimestamps();
}

// Access custom pivot data
$user = User::with('roles')->find(1);
foreach ($user->roles as $role) {
    echo $role->pivot->assigned_by; // Custom pivot column
}

Advanced Many-to-Many Scenarios

Handle complex business logic with many-to-many relationships:

// E-commerce: Products with categories and pricing
class Product extends Model
{
    public function categories()
    {
        return $this->belongsToMany(Category::class)
            ->withPivot('sort_order', 'featured')
            ->withTimestamps();
    }
    
    // Scope for featured products in specific category
    public function scopeFeaturedInCategory($query, $categoryId)
    {
        return $query->whereHas('categories', function ($q) use ($categoryId) {
            $q->where('category_id', $categoryId)
              ->where('category_product.featured', true);
        });
    }
}

// Social media: User followers/following
class User extends Model
{
    public function following()
    {
        return $this->belongsToMany(User::class, 'user_follows', 'follower_id', 'following_id')
            ->withTimestamps();
    }
    
    public function followers()
    {
        return $this->belongsToMany(User::class, 'user_follows', 'following_id', 'follower_id')
            ->withTimestamps();
    }
    
    // Mutual followers
    public function mutualFollowers(User $user)
    {
        return $this->followers()
            ->whereIn('users.id', $user->followers->pluck('id'))
            ->get();
    }
}

// Course enrollment with grades
class Student extends Model
{
    public function courses()
    {
        return $this->belongsToMany(Course::class, 'enrollments')
            ->withPivot('grade', 'enrollment_date', 'status')
            ->withTimestamps();
    }
    
    // Students with passing grades in specific course
    public static function passingStudents($courseId)
    {
        return static::whereHas('courses', function ($query) use ($courseId) {
            $query->where('course_id', $courseId)
                  ->where('enrollments.grade', '>=', 70);
        })->get();
    }
}

// Attach/Detach operations
$user = User::find(1);

// Attach roles
$user->roles()->attach([1, 2, 3]); // Attach multiple roles
$user->roles()->attach(1, ['assigned_by' => auth()->id()]); // With pivot data

// Sync roles (remove old, add new)
$user->roles()->sync([1, 2]); // Only roles 1 and 2 will remain

// Detach roles
$user->roles()->detach([1, 2]); // Remove specific roles
$user->roles()->detach(); // Remove all roles

// Toggle role (attach if not exists, detach if exists)
$user->roles()->toggle([1, 2]);

// Performance optimization: Eager loading with constraints
$usersWithAdminRole = User::with(['roles' => function ($query) {
    $query->where('name', 'admin');
}])->get();

// Subquery for counting related records
$categoriesWithProductCounts = Category::select([
    'categories.*',
    DB::raw('(SELECT COUNT(*) FROM category_product WHERE category_id = categories.id) as products_count')
])->get();

Many-to-many relationships require pivot tables to store the associations. Use whereHas() to filter by relationship existence, withCount() for counting relationships, and withPivot() to access additional pivot table columns. Always consider database indexes on foreign keys in pivot tables for better performance.

Related: Laravel Collections: Beyond Basic Array Operations | Advanced Eloquent Techniques and Optimizations in Laravel | Building Multi-tenant Applications with Laravel: A Comprehensive Guide