WhereNullSpecification

A built-in specification for NULL/NOT NULL checks, determining if a field value is null or not null.

Namespace

php

DangerWayne\Specification\Specifications\Common\WhereNullSpecification

Constructor

php

public function __construct(
    string $field,
    bool $isNull = true
)

Parameters

$field (string) - The database column or field name to check
$isNull (bool) - Whether to check for NULL (true) or NOT NULL (false). Defaults to true.

Usage Examples

Basic NULL Checks

php

use DangerWayne\Specification\Specifications\Common\WhereNullSpecification;

// Check for NULL values
$nullEmailSpec = new WhereNullSpecification('email');

// Check for NOT NULL values
$hasEmailSpec = new WhereNullSpecification('email', false);

// Apply to query
$usersWithoutEmail = User::whereSpecification($nullEmailSpec)->get();
$usersWithEmail = User::whereSpecification($hasEmailSpec)->get();

// Use with collection
$users = User::all();
$unverifiedUsers = $users->filter(function($user) use ($nullEmailSpec) {
    return $nullEmailSpec->isSatisfiedBy($user);
});

Common Scenarios

Email Verification

php

// Users without verified email
$unverifiedSpec = new WhereNullSpecification('email_verified_at');
$unverifiedUsers = User::whereSpecification($unverifiedSpec)->get();

// Verified users
$verifiedSpec = new WhereNullSpecification('email_verified_at', false);
$verifiedUsers = User::whereSpecification($verifiedSpec)->get();

Soft Deletes

php

// Active records (not soft deleted)
$activeSpec = new WhereNullSpecification('deleted_at');
$activeRecords = Model::whereSpecification($activeSpec)->get();

// Soft deleted records
$deletedSpec = new WhereNullSpecification('deleted_at', false);
$deletedRecords = Model::whereSpecification($deletedSpec)->get();

Profile Completion

php

// Incomplete profiles
$incompleteProfileSpec = new WhereNullSpecification('profile_completed_at');
$incompleteUsers = User::whereSpecification($incompleteProfileSpec)->get();

// Complete profiles
$completeProfileSpec = new WhereNullSpecification('profile_completed_at', false);
$completeUsers = User::whereSpecification($completeProfileSpec)->get();

Combining with Other Specifications

AND Combinations

php

$activeSpec = new WhereSpecification('status', 'active');
$unverifiedSpec = new WhereNullSpecification('email_verified_at');

// Active users who haven't verified email
$activeUnverifiedSpec = $activeSpec->and($unverifiedSpec);
$targetUsers = User::whereSpecification($activeUnverifiedSpec)->get();

OR Combinations

php

$noEmailSpec = new WhereNullSpecification('email');
$noPhoneSpec = new WhereNullSpecification('phone');

// Users missing either email or phone
$missingContactSpec = $noEmailSpec->or($noPhoneSpec);
$incompleteContacts = User::whereSpecification($missingContactSpec)->get();

Complex Combinations

php

$activeSpec = new WhereSpecification('status', 'active');
$verifiedSpec = new WhereNullSpecification('email_verified_at', false);
$notDeletedSpec = new WhereNullSpecification('deleted_at');
$hasProfileSpec = new WhereNullSpecification('profile_completed_at', false);

// Fully active users: active status, verified, not deleted, profile complete
$fullyActiveSpec = $activeSpec
    ->and($verifiedSpec)
    ->and($notDeletedSpec)
    ->and($hasProfileSpec);

$fullyActiveUsers = User::whereSpecification($fullyActiveSpec)->get();

Real-World Examples

User Onboarding

php

// Different onboarding stages based on NULL fields
$noWelcomeEmailSpec = new WhereNullSpecification('welcome_email_sent_at');
$noProfilePhotoSpec = new WhereNullSpecification('profile_photo_path');
$noPreferencesSpec = new WhereNullSpecification('preferences_set_at');

// Users who need welcome email
$needsWelcomeUsers = User::whereSpecification($noWelcomeEmailSpec)->get();

// Users in different onboarding stages
$onboardingSpec = $noProfilePhotoSpec->or($noPreferencesSpec);
$onboardingUsers = User::whereSpecification($onboardingSpec)->get();

Data Quality Monitoring

php

// Monitor data completeness
$missingDataSpec = collect([
    'first_name', 'last_name', 'email', 'phone', 'address'
])->map(fn($field) => new WhereNullSpecification($field))
  ->reduce(fn($carry, $spec) => $carry ? $carry->or($spec) : $spec);

$incompleteRecords = Customer::whereSpecification($missingDataSpec)->get();

// Quality score: count NULL fields
$qualityReport = Customer::selectRaw('
    id,
    CASE 
        WHEN first_name IS NULL THEN 1 ELSE 0 END +
        CASE WHEN last_name IS NULL THEN 1 ELSE 0 END +
        CASE WHEN email IS NULL THEN 1 ELSE 0 END +
        CASE WHEN phone IS NULL THEN 1 ELSE 0 END +
        CASE WHEN address IS NULL THEN 1 ELSE 0 END as missing_fields_count
')->get();

Payment Processing

php

// Orders without payment information
$noBillingSpec = new WhereNullSpecification('billing_address_id');
$noPaymentSpec = new WhereNullSpecification('payment_method_id');
$notProcessedSpec = new WhereNullSpecification('processed_at');

// Orders needing payment setup
$paymentIncompleteSpec = $noBillingSpec->or($noPaymentSpec);
$incompleteOrders = Order::whereSpecification($paymentIncompleteSpec)->get();

// Pending payment processing
$pendingPaymentSpec = $paymentIncompleteSpec->not()->and($notProcessedSpec);
$pendingOrders = Order::whereSpecification($pendingPaymentSpec)->get();

Implementation Details

isSatisfiedBy() Method

php

public function isSatisfiedBy(mixed $candidate): bool
{
    $value = data_get($candidate, $this->field);
    
    return $this->isNull ? $value === null : $value !== null;
}

toQuery() Method

php

public function toQuery(Builder $query): Builder
{
    return $this->isNull 
        ? $query->whereNull($this->field)
        : $query->whereNotNull($this->field);
}

Performance Considerations

Index Strategy: NULL values are often not indexed, consider partial indexes
Query Optimization: NOT NULL checks are generally faster than NULL checks
Database Differences: Different databases handle NULL differently
Sparse Indexes: Consider sparse indexes for fields with many NULL values

Database-Specific Optimizations

php

// MySQL: Consider adding NOT NULL constraints where appropriate
// to improve performance and data integrity

// PostgreSQL: Use partial indexes for common NULL checks
// CREATE INDEX CONCURRENTLY idx_users_email_verified 
// ON users (email_verified_at) WHERE email_verified_at IS NOT NULL;

// For better performance with frequently checked NULL conditions:
$frequentlyCheckedSpec = new WhereNullSpecification('important_field', false);

Negation Patterns

Since WhereNullSpecification already has built-in negation via the constructor parameter, you have multiple ways to create opposite conditions:

php

// Method 1: Constructor parameter (preferred)
$hasEmailSpec = new WhereNullSpecification('email', false);

// Method 2: Using not() method
$nullEmailSpec = new WhereNullSpecification('email');
$hasEmailSpec2 = $nullEmailSpec->not();

// Both are equivalent, but Method 1 is more readable

Validation and Data Cleaning

php

class DataCleaningSpecifications
{
    public static function requiredFieldsMissing(): SpecificationInterface
    {
        $requiredFields = ['name', 'email', 'status'];
        
        return collect($requiredFields)
            ->map(fn($field) => new WhereNullSpecification($field))
            ->reduce(fn($carry, $spec) => $carry ? $carry->or($spec) : $spec);
    }
    
    public static function optionalFieldsComplete(): SpecificationInterface
    {
        $optionalFields = ['phone', 'address', 'bio'];
        
        return collect($optionalFields)
            ->map(fn($field) => new WhereNullSpecification($field, false))
            ->reduce(fn($carry, $spec) => $carry ? $carry->and($spec) : $spec);
    }
}

// Usage
$incompleteRecords = Model::whereSpecification(
    DataCleaningSpecifications::requiredFieldsMissing()
)->get();

Testing

php

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

class WhereNullSpecificationTest extends TestCase
{
    public function test_it_filters_null_values()
    {
        User::factory()->create(['email_verified_at' => now()]); // Not null
        User::factory()->create(['email_verified_at' => null]); // Null
        User::factory()->create(['email_verified_at' => null]); // Null
        
        $spec = new WhereNullSpecification('email_verified_at');
        
        $users = User::whereSpecification($spec)->get();
        
        $this->assertCount(2, $users);
        $this->assertTrue($users->every(fn($user) => $user->email_verified_at === null));
    }
    
    public function test_it_filters_not_null_values()
    {
        User::factory()->create(['email_verified_at' => now()]); // Not null
        User::factory()->create(['email_verified_at' => now()]); // Not null
        User::factory()->create(['email_verified_at' => null]); // Null
        
        $spec = new WhereNullSpecification('email_verified_at', false);
        
        $users = User::whereSpecification($spec)->get();
        
        $this->assertCount(2, $users);
        $this->assertTrue($users->every(fn($user) => $user->email_verified_at !== null));
    }
    
    public function test_it_handles_nested_fields()
    {
        $userWithProfile = User::factory()->create();
        $userWithProfile->profile()->create(['bio' => 'Some bio']);
        
        $userWithoutProfile = User::factory()->create();
        
        $spec = new WhereNullSpecification('profile.bio');
        
        // Test with loaded relationships
        $users = User::with('profile')->get();
        $usersWithoutBio = $users->filter(fn($user) => $spec->isSatisfiedBy($user));
        
        $this->assertCount(1, $usersWithoutBio);
    }
}

Common Patterns

Migration Helpers

php

class MigrationNullCheckSpecification extends WhereNullSpecification
{
    public static function pendingMigration(string $field): self
    {
        // Records that haven't been migrated yet
        return new self($field);
    }
    
    public static function completedMigration(string $field): self
    {
        // Records that have been migrated
        return new self($field, false);
    }
}

// Usage during data migration
$pendingMigration = MigrationNullCheckSpecification::pendingMigration('migrated_at');
$recordsToMigrate = OldModel::whereSpecification($pendingMigration)->get();

Feature Flag Specifications

php

class FeatureFlagSpecification extends WhereNullSpecification
{
    public static function featureEnabled(string $featureField): self
    {
        return new self($featureField, false);
    }
    
    public static function featureDisabled(string $featureField): self
    {
        return new self($featureField);
    }
}

// Usage
$betaUsersSpec = FeatureFlagSpecification::featureEnabled('beta_enabled_at');
$betaUsers = User::whereSpecification($betaUsersSpec)->get();

WhereNullSpecification ​

Namespace ​

Constructor ​

Parameters ​

Usage Examples ​

Basic NULL Checks ​

Common Scenarios ​

Email Verification ​

Soft Deletes ​

Profile Completion ​

Combining with Other Specifications ​

AND Combinations ​

OR Combinations ​

Complex Combinations ​

Real-World Examples ​

User Onboarding ​

Data Quality Monitoring ​

Payment Processing ​

Implementation Details ​

isSatisfiedBy() Method ​

toQuery() Method ​

Performance Considerations ​

Database-Specific Optimizations ​

Negation Patterns ​

Validation and Data Cleaning ​

Testing ​

Common Patterns ​

Migration Helpers ​

Feature Flag Specifications ​

See Also ​