How to use `whereHas()` and `orWhereHas()` for relationship queries

Table Of Contents

• Laravel's Built-in Solution: Relationship Filtering
• Advanced whereHas() Filtering Patterns
• orWhereHas() for Alternative Conditions
• Performance Optimization and Best Practices

Laravel's Built-in Solution: Relationship Filtering

Need users who wrote Laravel posts or products with 5-star reviews? Standard where() only works on the current table, but whereHas() filters by related data:

// WRONG - This doesn't work (posts table doesn't exist in users query)
$authors = User::where('posts.title', 'LIKE', '%Laravel%')->get(); // Error

// RIGHT - Filter users who have posts containing "Laravel"
$laravelAuthors = User::whereHas('posts', function ($query) {
    $query->where('title', 'LIKE', '%Laravel%');
})->get();

// Get products that have reviews with rating >= 4
$wellReviewedProducts = Product::whereHas('reviews', function ($query) {
    $query->where('rating', '>=', 4);
})->get();

// Users who have written posts AND have comments
$activeUsers = User::whereHas('posts')
    ->whereHas('comments')
    ->get();

Advanced whereHas() Filtering Patterns

Handle complex relationship filtering with multiple conditions:

// E-commerce: Products with reviews by verified buyers
$trustedProducts = Product::whereHas('reviews', function ($query) {
    $query->whereHas('user', function ($userQuery) {
        $userQuery->where('email_verified_at', '!=', null)
                  ->where('purchase_count', '>', 0);
    });
})->get();

// Blog: Posts in specific categories with recent comments
$trendingPosts = Post::whereHas('comments', function ($query) {
    $query->where('created_at', '>=', now()->subDays(7));
})->whereHas('category', function ($query) {
    $query->whereIn('slug', ['technology', 'programming']);
})->get();

// Users with orders in last 30 days totaling over $500
$vipCustomers = User::whereHas('orders', function ($query) {
    $query->where('created_at', '>=', now()->subDays(30))
          ->selectRaw('SUM(total)')
          ->groupBy('user_id')
          ->havingRaw('SUM(total) > 500');
})->get();

// Count-based filtering - Users with more than 10 posts
$prolificAuthors = User::whereHas('posts', function ($query) {
    // This checks existence, not count
}, '>', 10)->get(); // The second parameter is the count

// Better approach for counting
$prolificAuthors = User::has('posts', '>', 10)->get();

// Complex nested relationships - Orders with items from specific brands
$brandOrders = Order::whereHas('items.product.brand', function ($query) {
    $query->whereIn('name', ['Nike', 'Adidas']);
})->get();

orWhereHas() for Alternative Conditions

Combine multiple relationship conditions with OR logic:

// Users who have posts OR comments (active in some way)
$activeUsers = User::whereHas('posts')
    ->orWhereHas('comments')
    ->get();

// Products that are featured OR have high ratings
$prominentProducts = Product::where('featured', true)
    ->orWhereHas('reviews', function ($query) {
        $query->selectRaw('AVG(rating)')
              ->groupBy('product_id')
              ->havingRaw('AVG(rating) >= 4.5');
    })->get();

// Advanced: Users active in Laravel ecosystem (posts, packages, or events)
$laravelCommunity = User::whereHas('posts', function ($query) {
    $query->whereHas('tags', function ($tagQuery) {
        $tagQuery->where('name', 'Laravel');
    });
})->orWhereHas('packages', function ($query) {
    $query->where('framework', 'Laravel');
})->orWhereHas('eventAttendances', function ($query) {
    $query->whereHas('event', function ($eventQuery) {
        $eventQuery->where('type', 'laravel-meetup');
    });
})->get();

// Conditional filtering based on user input
$query = Product::query();

if ($request->has('with_reviews')) {
    $query->whereHas('reviews');
}

if ($request->has('discounted')) {
    $query->orWhereHas('discounts', function ($discountQuery) {
        $discountQuery->where('expires_at', '>', now());
    });
}

$filteredProducts = $query->get();

Performance Optimization and Best Practices

Optimize relationship queries for better database performance:

// BAD - Multiple separate queries
$usersWithPosts = User::whereHas('posts')->get();
foreach ($usersWithPosts as $user) {
    echo $user->posts->count(); // N+1 query problem
}

// GOOD - Eager load with count
$usersWithPosts = User::whereHas('posts')
    ->withCount('posts')
    ->get();

foreach ($usersWithPosts as $user) {
    echo $user->posts_count; // No additional queries
}

// Combine whereHas with eager loading
$authorsWithRecentPosts = User::whereHas('posts', function ($query) {
    $query->where('published_at', '>=', now()->subMonth());
})->with(['posts' => function ($query) {
    $query->where('published_at', '>=', now()->subMonth())
          ->orderBy('published_at', 'desc');
}])->get();

// Index optimization - Add indexes for relationship queries
// CREATE INDEX idx_posts_user_published ON posts(user_id, published_at);
// CREATE INDEX idx_reviews_product_rating ON reviews(product_id, rating);

// Scope methods for reusable relationship queries
class User extends Model
{
    public function scopeWithPublishedPosts($query)
    {
        return $query->whereHas('posts', function ($postQuery) {
            $postQuery->where('status', 'published');
        });
    }
    
    public function scopeActiveInLastDays($query, $days = 30)
    {
        return $query->whereHas('posts', function ($postQuery) use ($days) {
            $postQuery->where('created_at', '>=', now()->subDays($days));
        })->orWhereHas('comments', function ($commentQuery) use ($days) {
            $commentQuery->where('created_at', '>=', now()->subDays($days));
        });
    }
}

// Usage of scopes
$activeAuthors = User::withPublishedPosts()
    ->activeInLastDays(7)
    ->get();

// Subquery approach for complex aggregations
$topCategories = Category::whereHas('posts', function ($query) {
    $query->selectRaw('COUNT(*)')
          ->groupBy('category_id')
          ->havingRaw('COUNT(*) > 5');
})->withCount('posts')
  ->orderBy('posts_count', 'desc')
  ->get();

Use whereHas() to filter models based on their relationships, and orWhereHas() for alternative relationship conditions. Always consider eager loading when you need the related data afterward, and add database indexes on foreign keys and frequently queried columns for better performance.

Related: Laravel Collections: Beyond Basic Array Operations | Laravel Events and Listeners: Building Decoupled Applications | Building Multi-tenant Applications with Laravel: A Comprehensive Guide