WhereHasSpecification

A built-in specification for relationship existence checks, determining if a model has related records that meet specified criteria.

Namespace

DangerWayne\Specification\Specifications\Common\WhereHasSpecification

Constructor

public function __construct(
    string $relation,
    SpecificationInterface $specification = null,
    string $operator = '>=',
    int $count = 1
)

Parameters

• $relation (string) - The relationship method name
• $specification (SpecificationInterface|null) - Optional specification to apply to related records
• $operator (string) - Comparison operator for count check (>=, >, =, <, <=, etc.)
• $count (int) - Number to compare against (defaults to 1)

Usage Examples

Basic Relationship Existence

use DangerWayne\Specification\Specifications\Common\WhereHasSpecification;

// Users who have posts
$hasPostsSpec = new WhereHasSpecification('posts');
$activeAuthors = User::whereSpecification($hasPostsSpec)->get();

// Users who have orders
$hasOrdersSpec = new WhereHasSpecification('orders');
$customers = User::whereSpecification($hasOrdersSpec)->get();

Relationship with Conditions

// Users who have published posts
$publishedSpec = new WhereSpecification('status', 'published');
$hasPublishedPostsSpec = new WhereHasSpecification('posts', $publishedSpec);
$publishedAuthors = User::whereSpecification($hasPublishedPostsSpec)->get();

// Users who have recent orders
$recentSpec = new WhereSpecification('created_at', '>=', now()->subDays(30));
$hasRecentOrdersSpec = new WhereHasSpecification('orders', $recentSpec);
$recentCustomers = User::whereSpecification($hasRecentOrdersSpec)->get();

Count-Based Filtering

// Users with exactly 5 posts
$exactlyFivePostsSpec = new WhereHasSpecification('posts', null, '=', 5);

// Users with more than 10 orders
$frequentCustomersSpec = new WhereHasSpecification('orders', null, '>', 10);

// Users with at least 3 published posts
$publishedSpec = new WhereSpecification('status', 'published');
$prolificAuthorsSpec = new WhereHasSpecification('posts', $publishedSpec, '>=', 3);

Common Scenarios

Content Management

// Authors with published content
$publishedSpec = new WhereSpecification('status', 'published');
$activeAuthorsSpec = new WhereHasSpecification('posts', $publishedSpec);
$activeAuthors = User::whereSpecification($activeAuthorsSpec)->get();

// Categories with active products
$activeProductSpec = new WhereSpecification('is_active', true);
$activeCategoriesSpec = new WhereHasSpecification('products', $activeProductSpec);
$activeCategories = Category::whereSpecification($activeCategoriesSpec)->get();

E-commerce

// Customers who made purchases this month
$thisMonthSpec = new WhereBetweenSpecification(
    'created_at', 
    now()->startOfMonth(), 
    now()->endOfMonth()
);
$activeCustomersSpec = new WhereHasSpecification('orders', $thisMonthSpec);
$activeCustomers = User::whereSpecification($activeCustomersSpec)->get();

// Products with positive reviews
$positiveReviewSpec = new WhereSpecification('rating', '>=', 4);
$wellRatedProductsSpec = new WhereHasSpecification('reviews', $positiveReviewSpec);
$recommendedProducts = Product::whereSpecification($wellRatedProductsSpec)->get();

User Engagement

// Users with recent activity
$recentActivitySpec = new WhereSpecification('created_at', '>=', now()->subDays(7));
$activeUsersSpec = new WhereHasSpecification('activities', $recentActivitySpec);
$engagedUsers = User::whereSpecification($activeUsersSpec)->get();

// Users with multiple login sessions
$multipleSessionsSpec = new WhereHasSpecification('sessions', null, '>', 1);
$activeUsers = User::whereSpecification($multipleSessionsSpec)->get();

Combining with Other Specifications

AND Combinations

$verifiedSpec = new WhereSpecification('email_verified_at', '!=', null);
$hasOrdersSpec = new WhereHasSpecification('orders');
$premiumSpec = new WhereSpecification('subscription_type', 'premium');

// Verified users with orders and premium subscription
$targetUsersSpec = $verifiedSpec->and($hasOrdersSpec)->and($premiumSpec);
$targetUsers = User::whereSpecification($targetUsersSpec)->get();

OR Combinations

$hasPostsSpec = new WhereHasSpecification('posts');
$hasCommentsSpec = new WhereHasSpecification('comments');

// Users who either have posts or comments
$activeUsersSpec = $hasPostsSpec->or($hasCommentsSpec);
$contentCreators = User::whereSpecification($activeUsersSpec)->get();

Complex Combinations

$publishedPostSpec = new WhereSpecification('status', 'published');
$recentOrderSpec = new WhereSpecification('created_at', '>=', now()->subDays(30));
$highValueOrderSpec = new WhereSpecification('total', '>=', 1000);

$hasPublishedPostsSpec = new WhereHasSpecification('posts', $publishedPostSpec);
$hasRecentOrdersSpec = new WhereHasSpecification('orders', $recentOrderSpec);
$hasHighValueOrdersSpec = new WhereHasSpecification('orders', $highValueOrderSpec);

// Authors with published posts AND (recent orders OR high value orders)
$targetSpec = $hasPublishedPostsSpec->and(
    $hasRecentOrdersSpec->or($hasHighValueOrdersSpec)
);

Real-World Examples

Customer Segmentation

// VIP customers: high-value orders and loyalty program
$highValueSpec = new WhereSpecification('total', '>=', 5000);
$loyaltyMemberSpec = new WhereSpecification('loyalty_tier', '!=', null);

$hasHighValueOrdersSpec = new WhereHasSpecification('orders', $highValueSpec);
$hasLoyaltySpec = new WhereHasSpecification('loyaltyMembership', $loyaltyMemberSpec);

$vipCustomersSpec = $hasHighValueOrdersSpec->and($hasLoyaltySpec);
$vipCustomers = User::whereSpecification($vipCustomersSpec)->get();

Content Curation

// Popular posts: multiple comments and high ratings
$multipleCommentsSpec = new WhereHasSpecification('comments', null, '>=', 5);
$highRatingSpec = new WhereSpecification('rating', '>=', 4.5);
$positiveReviewsSpec = new WhereHasSpecification('reviews', $highRatingSpec, '>=', 10);

$popularPostsSpec = $multipleCommentsSpec->and($positiveReviewsSpec);
$trendingPosts = Post::whereSpecification($popularPostsSpec)
    ->orderBy('created_at', 'desc')
    ->get();

Inventory Management

// Products needing restock: low stock with recent sales
$lowStockSpec = new WhereSpecification('quantity', '<=', 10);
$recentSalesSpec = new WhereSpecification('created_at', '>=', now()->subDays(7));

$hasRecentSalesSpec = new WhereHasSpecification('orderItems', $recentSalesSpec);
$restockNeededSpec = $lowStockSpec->and($hasRecentSalesSpec);

$productsToRestock = Product::whereSpecification($restockNeededSpec)->get();

Advanced Patterns

Nested Relationships

// Users who have posts with approved comments
$approvedCommentSpec = new WhereSpecification('status', 'approved');
$hasApprovedCommentsSpec = new WhereHasSpecification('comments', $approvedCommentSpec);

// This requires a HasManyThrough or custom relationship
$engagedAuthorsSpec = new WhereHasSpecification('posts', $hasApprovedCommentsSpec);
$engagedAuthors = User::whereSpecification($engagedAuthorsSpec)->get();

Dynamic Relationship Filtering

class DynamicRelationshipSpecification
{
    public static function hasRelatedWithCondition(
        string $relation,
        string $field,
        mixed $value,
        int $minCount = 1
    ): WhereHasSpecification {
        $condition = new WhereSpecification($field, $value);
        return new WhereHasSpecification($relation, $condition, '>=', $minCount);
    }
    
    public static function fromRequest(
        Request $request,
        array $relationshipConfig
    ): ?SpecificationInterface {
        $specs = collect();
        
        foreach ($relationshipConfig as $relation => $config) {
            if ($request->has($config['param'])) {
                $condition = new WhereSpecification(
                    $config['field'], 
                    $request->input($config['param'])
                );
                $specs->push(new WhereHasSpecification($relation, $condition));
            }
        }
        
        return $specs->reduce(fn($carry, $spec) => $carry ? $carry->and($spec) : $spec);
    }
}

Implementation Details

isSatisfiedBy() Method

public function isSatisfiedBy(mixed $candidate): bool
{
    if (!method_exists($candidate, $this->relation)) {
        return false;
    }
    
    $related = $candidate->{$this->relation};
    
    // Handle different relationship types
    if ($related instanceof Collection) {
        $count = $this->specification 
            ? $related->filter(fn($item) => $this->specification->isSatisfiedBy($item))->count()
            : $related->count();
    } else {
        // Handle single relationships
        $count = $related !== null ? 1 : 0;
        if ($this->specification && $related) {
            $count = $this->specification->isSatisfiedBy($related) ? 1 : 0;
        }
    }
    
    return $this->compareCount($count);
}

private function compareCount(int $actualCount): bool
{
    return match($this->operator) {
        '>' => $actualCount > $this->count,
        '>=' => $actualCount >= $this->count,
        '=' => $actualCount === $this->count,
        '<' => $actualCount < $this->count,
        '<=' => $actualCount <= $this->count,
        '!=' => $actualCount !== $this->count,
        default => false,
    };
}

toQuery() Method

public function toQuery(Builder $query): Builder
{
    return $query->whereHas(
        $this->relation,
        function ($query) {
            if ($this->specification) {
                $this->specification->toQuery($query);
            }
        },
        $this->operator,
        $this->count
    );
}

Performance Considerations

1. Relationship Indexes: Ensure foreign keys have proper indexes
2. Eager Loading: Use with() when accessing relationships in memory
3. Subquery Optimization: Database optimizes whereHas as subqueries
4. Count Caching: Consider caching relationship counts for frequently accessed data

Optimization Strategies

// Use withCount for better performance when you only need counts
$users = User::withCount([
    'posts' => function ($query) {
        $query->where('status', 'published');
    }
])->having('posts_count', '>=', 5)->get();

// Consider denormalizing counts for frequently queried relationships
// Add posts_count column to users table and maintain it
$activeAuthorsSpec = new WhereSpecification('posts_count', '>', 0);

Testing

use Tests\TestCase;
use DangerWayne\Specification\Specifications\Common\WhereHasSpecification;
use DangerWayne\Specification\Specifications\Common\WhereSpecification;

class WhereHasSpecificationTest extends TestCase
{
    public function test_it_filters_by_relationship_existence()
    {
        $userWithPosts = User::factory()->has(
            Post::factory()->count(3)
        )->create();
        $userWithoutPosts = User::factory()->create();
        
        $spec = new WhereHasSpecification('posts');
        
        $users = User::whereSpecification($spec)->get();
        
        $this->assertCount(1, $users);
        $this->assertEquals($userWithPosts->id, $users->first()->id);
    }
    
    public function test_it_filters_by_relationship_with_conditions()
    {
        $userWithPublishedPosts = User::factory()->has(
            Post::factory()->state(['status' => 'published'])->count(2)
        )->create();
        
        $userWithDraftPosts = User::factory()->has(
            Post::factory()->state(['status' => 'draft'])->count(2)
        )->create();
        
        $publishedSpec = new WhereSpecification('status', 'published');
        $spec = new WhereHasSpecification('posts', $publishedSpec);
        
        $users = User::whereSpecification($spec)->get();
        
        $this->assertCount(1, $users);
        $this->assertEquals($userWithPublishedPosts->id, $users->first()->id);
    }
    
    public function test_it_handles_count_operators()
    {
        User::factory()->has(Post::factory()->count(5))->create();
        User::factory()->has(Post::factory()->count(3))->create();
        User::factory()->has(Post::factory()->count(1))->create();
        
        $spec = new WhereHasSpecification('posts', null, '>=', 3);
        
        $users = User::whereSpecification($spec)->get();
        
        $this->assertCount(2, $users);
    }
}

Common Patterns

Relationship Aggregation

class RelationshipAggregationSpecification extends WhereHasSpecification
{
    public static function hasMinimumRelated(
        string $relation, 
        int $minimum
    ): self {
        return new self($relation, null, '>=', $minimum);
    }
    
    public static function hasExactlyRelated(
        string $relation, 
        int $exact
    ): self {
        return new self($relation, null, '=', $exact);
    }
    
    public static function hasRelatedWithValue(
        string $relation,
        string $field,
        mixed $value,
        int $minimum = 1
    ): self {
        $condition = new WhereSpecification($field, $value);
        return new self($relation, $condition, '>=', $minimum);
    }
}

Polymorphic Relations

// For polymorphic relationships
$commentableSpec = new WhereSpecification('commentable_type', Post::class);
$hasPostCommentsSpec = new WhereHasSpecification('comments', $commentableSpec);

// Users who commented on posts (not other commentable types)
$postCommenters = User::whereSpecification($hasPostCommentsSpec)->get();

See Also

• WhereSpecification - Basic WHERE conditions
• WhereInSpecification - IN conditions
• WhereBetweenSpecification - Range conditions
• SpecificationBuilder - Fluent builder with whereHas()