WhereInSpecification

A built-in specification for WHERE IN conditions, checking if a field value exists within a given set of values.

Namespace

DangerWayne\Specification\Specifications\Common\WhereInSpecification

Constructor

public function __construct(
    string $field,
    array $values
)

Parameters

• $field (string) - The database column or field name to check
• $values (array) - The array of values to check against

Usage Examples

Basic Usage

use DangerWayne\Specification\Specifications\Common\WhereInSpecification;

// Check if status is one of the allowed values
$spec = new WhereInSpecification('status', ['active', 'pending', 'approved']);

// Apply to query
$users = User::whereSpecification($spec)->get();

// Use with collection
$filteredUsers = $users->filter(function($user) use ($spec) {
    return $spec->isSatisfiedBy($user);
});

Common Scenarios

User Roles

// Users with specific roles
$staffSpec = new WhereInSpecification('role', ['admin', 'moderator', 'editor']);
$staffMembers = User::whereSpecification($staffSpec)->get();

Product Categories

// Products in specific categories
$categories = [1, 5, 8, 12]; // Category IDs
$categorySpec = new WhereInSpecification('category_id', $categories);
$products = Product::whereSpecification($categorySpec)->get();

Order Statuses

// Orders in processable states
$processableSpec = new WhereInSpecification('status', [
    'pending',
    'processing', 
    'ready_to_ship'
]);
$orders = Order::whereSpecification($processableSpec)->get();

Combining with Other Specifications

AND Combinations

$activeSpec = new WhereSpecification('is_active', true);
$roleSpec = new WhereInSpecification('role', ['admin', 'moderator']);

// Active users with admin or moderator role
$activeStaffSpec = $activeSpec->and($roleSpec);
$activeStaff = User::whereSpecification($activeStaffSpec)->get();

OR Combinations

$premiumRolesSpec = new WhereInSpecification('role', ['premium', 'vip']);
$highSpenderSpec = new WhereSpecification('total_spent', '>=', 1000);

// Premium/VIP users OR high spenders
$valuableUsersSpec = $premiumRolesSpec->or($highSpenderSpec);
$valuableUsers = User::whereSpecification($valuableUsersSpec)->get();

Complex Combinations

$categoriesSpec = new WhereInSpecification('category_id', [1, 2, 3]);
$brandsSpec = new WhereInSpecification('brand_id', [10, 20, 30]);
$inStockSpec = new WhereSpecification('stock_quantity', '>', 0);

// Products in specific categories AND brands AND in stock
$complexSpec = $categoriesSpec
    ->and($brandsSpec)
    ->and($inStockSpec);

$products = Product::whereSpecification($complexSpec)->get();

Real-World Examples

E-commerce Filtering

// Filter products by multiple criteria
$colorSpec = new WhereInSpecification('color', ['red', 'blue', 'green']);
$sizeSpec = new WhereInSpecification('size', ['S', 'M', 'L']);
$materialSpec = new WhereInSpecification('material', ['cotton', 'polyester']);

$filterSpec = $colorSpec
    ->and($sizeSpec)
    ->and($materialSpec);

$filteredProducts = Product::whereSpecification($filterSpec)->get();

Permission System

// Check if user has any of the required permissions
$requiredPermissions = ['edit_posts', 'publish_posts', 'delete_posts'];
$permissionSpec = new WhereInSpecification('permission_key', $requiredPermissions);

$authorizedUsers = User::whereHas('permissions', function($query) use ($permissionSpec) {
    $permissionSpec->toQuery($query);
})->get();

Geographic Filtering

// Filter by multiple locations
$countriesSpec = new WhereInSpecification('country_code', ['US', 'CA', 'GB']);
$citiesSpec = new WhereInSpecification('city', ['New York', 'Los Angeles', 'London']);

$locationSpec = $countriesSpec->and($citiesSpec);
$locations = Location::whereSpecification($locationSpec)->get();

Implementation Details

isSatisfiedBy() Method

public function isSatisfiedBy(mixed $candidate): bool
{
    $value = data_get($candidate, $this->field);
    
    return in_array($value, $this->values, true);
}

toQuery() Method

public function toQuery(Builder $query): Builder
{
    return $query->whereIn($this->field, $this->values);
}

Performance Considerations

1. Array Size: Large arrays in WHERE IN can impact performance
2. Indexing: Ensure the field has an appropriate index
3. Type Consistency: Keep array values type-consistent
4. Query Optimization: Consider chunking for very large arrays

Optimizing Large Sets

// For large sets, consider chunking
$allIds = range(1, 10000);
$chunks = array_chunk($allIds, 1000);

$results = collect();
foreach ($chunks as $chunk) {
    $spec = new WhereInSpecification('id', $chunk);
    $results = $results->merge(
        Model::whereSpecification($spec)->get()
    );
}

Negation (NOT IN)

To create a NOT IN condition, use the not() method:

$excludeStatusSpec = new WhereInSpecification('status', ['deleted', 'archived']);
$notDeletedSpec = $excludeStatusSpec->not();

// Get all users NOT deleted or archived
$activeUsers = User::whereSpecification($notDeletedSpec)->get();

Dynamic Value Building

class DynamicInSpecification
{
    public static function fromRequest(Request $request, string $field): ?WhereInSpecification
    {
        $values = $request->input($field);
        
        if (empty($values)) {
            return null;
        }
        
        // Handle comma-separated strings
        if (is_string($values)) {
            $values = explode(',', $values);
        }
        
        // Clean and validate values
        $values = array_map('trim', $values);
        $values = array_filter($values);
        
        return new WhereInSpecification($field, $values);
    }
}

Testing

use Tests\TestCase;
use DangerWayne\Specification\Specifications\Common\WhereInSpecification;

class WhereInSpecificationTest extends TestCase
{
    public function test_it_filters_by_values_array()
    {
        User::factory()->create(['role' => 'admin']);
        User::factory()->create(['role' => 'moderator']);
        User::factory()->create(['role' => 'user']);
        
        $spec = new WhereInSpecification('role', ['admin', 'moderator']);
        
        $users = User::whereSpecification($spec)->get();
        
        $this->assertCount(2, $users);
        $this->assertTrue($users->pluck('role')->contains('admin'));
        $this->assertTrue($users->pluck('role')->contains('moderator'));
        $this->assertFalse($users->pluck('role')->contains('user'));
    }
    
    public function test_it_handles_empty_arrays()
    {
        $spec = new WhereInSpecification('status', []);
        
        $users = User::whereSpecification($spec)->get();
        
        $this->assertCount(0, $users);
    }
}

Common Patterns

Configuration-Based Values

class AllowedStatusSpecification extends WhereInSpecification
{
    public function __construct()
    {
        $allowedStatuses = config('app.allowed_statuses', ['active']);
        parent::__construct('status', $allowedStatuses);
    }
}

Enum Integration

use App\Enums\UserRole;

class UserRoleSpecification extends WhereInSpecification
{
    public function __construct(array $roles)
    {
        // Convert enums to values
        $values = array_map(fn($role) => $role->value, $roles);
        parent::__construct('role', $values);
    }
}

// Usage
$spec = new UserRoleSpecification([
    UserRole::Admin,
    UserRole::Moderator
]);

See Also

• WhereSpecification - Basic WHERE conditions
• WhereBetweenSpecification - Range conditions
• WhereNullSpecification - NULL checks
• SpecificationBuilder - Fluent builder with whereIn()