Query Integration

Master how specifications integrate seamlessly with Laravel's Eloquent ORM and Query Builder. Learn optimization techniques, relationship handling, and advanced query patterns.

The Two Worlds of Specifications

Specifications bridge two different contexts:

1. Memory Context: Working with loaded objects and collections
2. Database Context: Building efficient SQL queries

Understanding this duality is key to writing effective specifications.

Basic Query Integration

The whereSpecification Method

Laravel Specifications adds a whereSpecification method to Eloquent:

// Basic usage
$activeUsers = User::whereSpecification(new ActiveUserSpecification())->get();

// With additional constraints
$recentActiveUsers = User::whereSpecification(new ActiveUserSpecification())
    ->where('created_at', '>', now()->subMonth())
    ->orderBy('created_at', 'desc')
    ->get();

// With pagination
$paginatedUsers = User::whereSpecification(new ActiveUserSpecification())
    ->paginate(15);

How toQuery() Works

When you call whereSpecification(), Laravel internally calls your specification's toQuery() method:

class ActiveUserSpecification extends AbstractSpecification
{
    public function isSatisfiedBy(mixed $candidate): bool
    {
        return $candidate->status === 'active';
    }
    
    // This method is called by whereSpecification()
    public function toQuery($query): Builder
    {
        return $query->where('status', 'active');
    }
}

// Internally, Laravel does:
$spec = new ActiveUserSpecification();
$query = User::query();
$result = $spec->toQuery($query); // Your toQuery method is called

Working with Relationships

Simple Relationship Queries

class UserWithOrdersSpecification extends AbstractSpecification
{
    public function isSatisfiedBy(mixed $candidate): bool
    {
        // For loaded objects, check the relationship
        if ($candidate->relationLoaded('orders')) {
            return $candidate->orders->isNotEmpty();
        }
        
        // Fallback to database query
        return $candidate->orders()->exists();
    }
    
    public function toQuery($query): Builder
    {
        return $query->whereHas('orders');
    }
}

// Usage
$usersWithOrders = User::whereSpecification(
    new UserWithOrdersSpecification()
)->get();

// Generated SQL: SELECT * FROM users WHERE EXISTS (SELECT * FROM orders WHERE orders.user_id = users.id)

Complex Relationship Constraints

class UserWithRecentOrdersSpecification extends AbstractSpecification
{
    public function __construct(
        private readonly int $days = 30
    ) {}
    
    public function isSatisfiedBy(mixed $candidate): bool
    {
        if ($candidate->relationLoaded('orders')) {
            return $candidate->orders
                ->where('created_at', '>', now()->subDays($this->days))
                ->isNotEmpty();
        }
        
        return $candidate->orders()
            ->where('created_at', '>', now()->subDays($this->days))
            ->exists();
    }
    
    public function toQuery($query): Builder
    {
        return $query->whereHas('orders', function ($orderQuery) {
            $orderQuery->where('created_at', '>', now()->subDays($this->days));
        });
    }
}

// Usage
$query = User::whereSpecification(
    new UserWithRecentOrdersSpecification(7)
);

// Generated SQL: 
// SELECT * FROM users 
// WHERE EXISTS (
//   SELECT * FROM orders 
//   WHERE orders.user_id = users.id 
//   AND orders.created_at > '2025-01-22 10:00:00'
// )

Counting Relationships

class UserWithMinimumOrdersSpecification extends AbstractSpecification
{
    public function __construct(
        private readonly int $minimumOrders
    ) {}
    
    public function isSatisfiedBy(mixed $candidate): bool
    {
        if ($candidate->relationLoaded('orders')) {
            return $candidate->orders->count() >= $this->minimumOrders;
        }
        
        return $candidate->orders()->count() >= $this->minimumOrders;
    }
    
    public function toQuery($query): Builder
    {
        return $query->whereHas('orders', null, '>=', $this->minimumOrders);
    }
}

// Advanced version with better performance
class OptimizedUserWithMinimumOrdersSpecification extends AbstractSpecification
{
    public function __construct(
        private readonly int $minimumOrders
    ) {}
    
    public function toQuery($query): Builder
    {
        return $query->withCount('orders')
            ->having('orders_count', '>=', $this->minimumOrders);
    }
    
    public function isSatisfiedBy(mixed $candidate): bool
    {
        // Check if orders_count is available (from withCount)
        if (isset($candidate->orders_count)) {
            return $candidate->orders_count >= $this->minimumOrders;
        }
        
        // Fallback to relationship count
        return $candidate->orders()->count() >= $this->minimumOrders;
    }
}

Advanced Query Patterns

Subquery Specifications

class UserWithHighValueOrdersSpecification extends AbstractSpecification
{
    public function __construct(
        private readonly float $minimumOrderValue
    ) {}
    
    public function toQuery($query): Builder
    {
        return $query->whereIn('id', function ($subquery) {
            $subquery->select('user_id')
                ->from('orders')
                ->where('total_amount', '>', $this->minimumOrderValue * 100)
                ->distinct();
        });
    }
    
    public function isSatisfiedBy(mixed $candidate): bool
    {
        return $candidate->orders()
            ->where('total_amount', '>', $this->minimumOrderValue * 100)
            ->exists();
    }
}

Join-Based Specifications

class UserInRegionSpecification extends AbstractSpecification
{
    public function __construct(
        private readonly string $regionName
    ) {}
    
    public function toQuery($query): Builder
    {
        return $query->join('addresses', 'users.id', '=', 'addresses.user_id')
            ->join('regions', 'addresses.region_id', '=', 'regions.id')
            ->where('regions.name', $this->regionName)
            ->select('users.*'); // Important: avoid selecting joined columns
    }
    
    public function isSatisfiedBy(mixed $candidate): bool
    {
        if ($candidate->relationLoaded('address.region')) {
            return $candidate->address->region->name === $this->regionName;
        }
        
        return $candidate->address()->whereHas('region', function ($query) {
            $query->where('name', $this->regionName);
        })->exists();
    }
}

Raw Query Specifications

For complex database-specific operations:

class UserWithComplexScoreSpecification extends AbstractSpecification
{
    public function __construct(
        private readonly float $minimumScore
    ) {}
    
    public function toQuery($query): Builder
    {
        return $query->whereRaw('
            (
                COALESCE(loyalty_points, 0) * 0.3 + 
                COALESCE(purchase_frequency, 0) * 0.4 + 
                COALESCE(referral_count, 0) * 0.3
            ) >= ?
        ', [$this->minimumScore]);
    }
    
    public function isSatisfiedBy(mixed $candidate): bool
    {
        $score = ($candidate->loyalty_points ?? 0) * 0.3 +
                ($candidate->purchase_frequency ?? 0) * 0.4 +
                ($candidate->referral_count ?? 0) * 0.3;
                
        return $score >= $this->minimumScore;
    }
}

Optimizing Query Performance

Index-Aware Specifications

Design specifications with database indexes in mind:

class OptimizedUserSpecification extends AbstractSpecification
{
    public function toQuery($query): Builder
    {
        // Good: Uses compound index on (status, created_at)
        return $query->where('status', 'active')
            ->where('created_at', '>', now()->subYear());
    }
}

class SuboptimalUserSpecification extends AbstractSpecification
{
    public function toQuery($query): Builder
    {
        // Bad: Function on indexed column prevents index usage
        return $query->whereRaw('UPPER(email) LIKE ?', ['%@EXAMPLE.COM']);
    }
}

class BetterUserSpecification extends AbstractSpecification
{
    public function toQuery($query): Builder
    {
        // Better: Uses index on email column
        return $query->where('email', 'like', '%@example.com');
    }
}

Eager Loading with Specifications

class UserWithOrderDetailsSpecification extends AbstractSpecification
{
    public function toQuery($query): Builder
    {
        // Eager load related data to avoid N+1 queries
        return $query->with(['orders.items.product'])
            ->whereHas('orders');
    }
    
    public function isSatisfiedBy(mixed $candidate): bool
    {
        // Take advantage of eager loaded data
        if ($candidate->relationLoaded('orders')) {
            return $candidate->orders->isNotEmpty();
        }
        
        return $candidate->orders()->exists();
    }
}

// Usage
$usersWithOrders = User::whereSpecification(
    new UserWithOrderDetailsSpecification()
)->get();

// All orders and related data are loaded in the initial query
foreach ($usersWithOrders as $user) {
    foreach ($user->orders as $order) {
        // No additional queries - data is already loaded
        echo $order->items->first()->product->name;
    }
}

Query Caching Specifications

class CachedUserSpecification extends AbstractSpecification
{
    public function __construct(
        private readonly string $cacheKey,
        private readonly int $cacheTtl = 3600
    ) {}
    
    public function toQuery($query): Builder
    {
        // Cache the query results
        return $query->remember($this->cacheTtl);
    }
    
    public function isSatisfiedBy(mixed $candidate): bool
    {
        return $candidate->status === 'active';
    }
    
    // Helper method for cache key generation
    public static function forActiveUsers(): self
    {
        return new self(
            cacheKey: 'specifications.active_users',
            cacheTtl: 3600
        );
    }
}

Collection Integration

Filtering Collections

$users = User::all(); // Loaded from database or cache
$spec = new ActiveVerifiedUserSpecification();

// Filter the collection
$activeUsers = $users->filter(
    fn($user) => $spec->isSatisfiedBy($user)
);

// More complex filtering
$eligibleUsers = $users->filter(function ($user) use ($spec) {
    return $spec->isSatisfiedBy($user) && $user->orders_count > 5;
});

Collection Partitioning

$users = User::with('orders')->get();
$spec = new PremiumUserSpecification();

// Partition into premium and regular users
[$premiumUsers, $regularUsers] = $users->partition(
    fn($user) => $spec->isSatisfiedBy($user)
);

echo "Premium users: " . $premiumUsers->count();
echo "Regular users: " . $regularUsers->count();

Collection Aggregation

class RevenueCalculator
{
    public function calculateBySpecification(
        Collection $orders, 
        SpecificationInterface $spec
    ): float {
        return $orders
            ->filter(fn($order) => $spec->isSatisfiedBy($order))
            ->sum('total_amount');
    }
}

// Usage
$orders = Order::with('user')->get();
$calculator = new RevenueCalculator();

$premiumRevenue = $calculator->calculateBySpecification(
    $orders,
    new PremiumUserOrderSpecification()
);

$newCustomerRevenue = $calculator->calculateBySpecification(
    $orders,
    new NewCustomerOrderSpecification()
);

Advanced Integration Patterns

Specification-Based Scopes

Create reusable query scopes with specifications:

// In your Eloquent model
class User extends Model
{
    public function scopeWhereSpecification($query, SpecificationInterface $spec)
    {
        return $spec->toQuery($query);
    }
    
    public function scopeActive($query)
    {
        return $query->whereSpecification(new ActiveUserSpecification());
    }
    
    public function scopeEligibleForDiscount($query)
    {
        return $query->whereSpecification(
            (new ActiveUserSpecification())
                ->and(new VerifiedUserSpecification())
                ->and(new MinimumPurchaseSpecification(100))
        );
    }
}

// Usage
$users = User::active()->eligibleForDiscount()->get();

Dynamic Query Building

class DynamicUserQueryBuilder
{
    private Builder $query;
    private array $appliedSpecs = [];
    
    public function __construct()
    {
        $this->query = User::query();
    }
    
    public function addSpecification(SpecificationInterface $spec, string $name = null): self
    {
        $this->query = $spec->toQuery($this->query);
        $this->appliedSpecs[] = $name ?? get_class($spec);
        return $this;
    }
    
    public function when(bool $condition, SpecificationInterface $spec): self
    {
        if ($condition) {
            $this->addSpecification($spec);
        }
        return $this;
    }
    
    public function getQuery(): Builder
    {
        return $this->query;
    }
    
    public function getAppliedSpecifications(): array
    {
        return $this->appliedSpecs;
    }
    
    public function get(): Collection
    {
        return $this->query->get();
    }
}

// Usage
$builder = new DynamicUserQueryBuilder();

$users = $builder
    ->addSpecification(new ActiveUserSpecification(), 'active')
    ->when($request->boolean('verified'), new VerifiedUserSpecification())
    ->when($request->filled('role'), new UserRoleSpecification($request->role))
    ->get();

// Debug applied specifications
logger('Applied specifications', [
    'specs' => $builder->getAppliedSpecifications(),
    'sql' => $builder->getQuery()->toSql()
]);

Query Result Analysis

class QueryAnalyzer
{
    public function analyzeSpecification(SpecificationInterface $spec): array
    {
        $query = User::whereSpecification($spec);
        
        return [
            'sql' => $query->toSql(),
            'bindings' => $query->getBindings(),
            'count' => $query->count(),
            'execution_time' => $this->measureExecutionTime($query),
            'explain' => $this->explainQuery($query),
        ];
    }
    
    private function measureExecutionTime(Builder $query): float
    {
        $start = microtime(true);
        $query->get();
        return microtime(true) - $start;
    }
    
    private function explainQuery(Builder $query): array
    {
        $connection = $query->getConnection();
        $sql = 'EXPLAIN ' . $query->toSql();
        
        return $connection->select($sql, $query->getBindings());
    }
}

// Usage
$analyzer = new QueryAnalyzer();
$analysis = $analyzer->analyzeSpecification(
    (new ActiveUserSpecification())
        ->and(new PremiumUserSpecification())
);

dump($analysis);

Testing Query Integration

Database Query Tests

class SpecificationQueryTest extends TestCase
{
    use RefreshDatabase;
    
    public function test_specification_generates_correct_sql()
    {
        $spec = new ActiveUserSpecification();
        $query = User::whereSpecification($spec);
        
        $this->assertEquals(
            'select * from "users" where "status" = ?',
            $query->toSql()
        );
        
        $this->assertEquals(['active'], $query->getBindings());
    }
    
    public function test_specification_returns_correct_results()
    {
        // Create test data
        $activeUser = User::factory()->create(['status' => 'active']);
        $inactiveUser = User::factory()->create(['status' => 'inactive']);
        
        $spec = new ActiveUserSpecification();
        $results = User::whereSpecification($spec)->get();
        
        $this->assertCount(1, $results);
        $this->assertTrue($results->contains($activeUser));
        $this->assertFalse($results->contains($inactiveUser));
    }
    
    public function test_composed_specification_query()
    {
        $spec = (new ActiveUserSpecification())
            ->and(new VerifiedUserSpecification());
            
        $query = User::whereSpecification($spec);
        
        $expectedSql = 'select * from "users" where "status" = ? and "email_verified_at" is not null';
        $this->assertEquals($expectedSql, $query->toSql());
    }
    
    public function test_relationship_specification_query()
    {
        $spec = new UserWithOrdersSpecification();
        $query = User::whereSpecification($spec);
        
        $this->assertStringContainsString('exists', strtolower($query->toSql()));
        $this->assertStringContainsString('orders', $query->toSql());
    }
}

Performance Testing

class SpecificationPerformanceTest extends TestCase
{
    public function test_specification_query_performance()
    {
        // Create test data
        User::factory()->count(1000)->create(['status' => 'active']);
        User::factory()->count(1000)->create(['status' => 'inactive']);
        
        $spec = new ActiveUserSpecification();
        
        // Measure query execution time
        $start = microtime(true);
        $results = User::whereSpecification($spec)->get();
        $executionTime = microtime(true) - $start;
        
        // Assert performance is acceptable (adjust threshold as needed)
        $this->assertLessThan(0.1, $executionTime, 'Query took too long');
        $this->assertCount(1000, $results);
    }
    
    public function test_eager_loading_prevents_n_plus_one()
    {
        // Create users with orders
        User::factory()->count(10)
            ->has(Order::factory()->count(3))
            ->create(['status' => 'active']);
        
        DB::enableQueryLog();
        
        $spec = new UserWithOrderDetailsSpecification();
        $users = User::whereSpecification($spec)->get();
        
        // Access related data
        foreach ($users as $user) {
            $user->orders->count(); // Should not trigger additional queries
        }
        
        $queries = DB::getQueryLog();
        
        // Should only execute 1 query due to eager loading
        $this->assertLessThanOrEqual(1, count($queries));
    }
}

Troubleshooting Common Issues

Memory vs Query Inconsistency

// Problem: Inconsistent behavior between memory and query modes
class ProblematicSpecification extends AbstractSpecification
{
    public function isSatisfiedBy(mixed $candidate): bool
    {
        // Uses PHP date formatting
        return date('Y', strtotime($candidate->created_at)) === '2025';
    }
    
    public function toQuery($query): Builder
    {
        // Uses SQL date formatting - might be different!
        return $query->whereYear('created_at', 2025);
    }
}

// Solution: Keep logic consistent
class ConsistentDateSpecification extends AbstractSpecification
{
    private readonly Carbon $targetYear;
    
    public function __construct(int $year = null)
    {
        $this->targetYear = Carbon::createFromFormat('Y', $year ?? now()->year);
    }
    
    public function isSatisfiedBy(mixed $candidate): bool
    {
        return Carbon::parse($candidate->created_at)->year === $this->targetYear->year;
    }
    
    public function toQuery($query): Builder
    {
        return $query->whereYear('created_at', $this->targetYear->year);
    }
}

Relationship Loading Issues

// Problem: Assuming relationships are loaded
class BadRelationshipSpecification extends AbstractSpecification
{
    public function isSatisfiedBy(mixed $candidate): bool
    {
        // This will cause N+1 queries if orders aren't loaded!
        return $candidate->orders->count() > 5;
    }
}

// Solution: Check if relationship is loaded
class SafeRelationshipSpecification extends AbstractSpecification
{
    public function isSatisfiedBy(mixed $candidate): bool
    {
        if ($candidate->relationLoaded('orders')) {
            return $candidate->orders->count() > 5;
        }
        
        // Fallback to efficient database query
        return $candidate->orders()->count() > 5;
    }
}

Next Steps

Now that you understand query integration, learn how to test your specifications thoroughly:

• Testing Strategies → - Comprehensive testing approaches
• Performance & Caching → - Optimize specification performance
• Laravel Integration → - Framework-specific patterns

---

Query Mastery

Effective specifications seamlessly work in both memory and database contexts. Always consider performance implications and keep your memory and query logic consistent.

Next: Testing Strategies →