Operators and Conditions

Sleet-ORM supports a wide range of SQL operators and conditions to build complex queries. This page documents all available operators and their usage patterns.

Comparison Operators

Equality Operators

-- Equal to
Schema.users:where('status', '=', 'active'):exec()
Schema.users:where('status', 'active'):exec() -- Shorthand for equality

-- Not equal to
Schema.users:where('status', '!=', 'banned'):exec()
Schema.users:where('status', '<>', 'banned'):exec()

Numeric Comparisons

-- Greater than
Schema.users:where('age', '>', 18):exec()

-- Greater than or equal to
Schema.users:where('age', '>=', 21):exec()

-- Less than
Schema.users:where('score', '<', 100):exec()

-- Less than or equal to
Schema.users:where('score', '<=', 50):exec()

Pattern Matching

LIKE Operator

-- Contains pattern
Schema.users:where('name', 'LIKE', '%john%'):exec()

-- Starts with pattern
Schema.users:where('email', 'LIKE', 'admin%'):exec()

-- Ends with pattern
Schema.users:where('phone', 'LIKE', '%1234'):exec()

-- Case-insensitive LIKE (MySQL)
Schema.users:where('name', 'LIKE', '%JOHN%'):exec()

NOT LIKE Operator

-- Exclude patterns
Schema.users:where('email', 'NOT LIKE', '%@spam.com'):exec()
Schema.users:where('name', 'NOT LIKE', '%test%'):exec()

Range Operators

BETWEEN

-- Numeric range
Schema.users:whereBetween('age', 18, 65):exec()

-- Date range
Schema.orders:whereBetween('created_at', '2023-01-01', '2023-12-31'):exec()

-- NOT BETWEEN
Schema.products:where('price', 'NOT BETWEEN', {100, 500}):exec()

List Operators

IN Operator

-- Match any value in list
Schema.users:whereIn('status', {'active', 'pending', 'verified'}):exec()
Schema.users:whereIn('id', {1, 2, 3, 4, 5}):exec()

-- Subquery IN
Schema.users:whereIn('department_id', function(query)
    return query:select('id'):from('departments'):where('active', true)
end):exec()

NOT IN Operator

-- Exclude values in list
Schema.users:whereNotIn('status', {'banned', 'deleted'}):exec()
Schema.users:whereNotIn('id', {1, 2, 3}):exec()

NULL Operators

IS NULL

-- Check for NULL values
Schema.users:whereNull('deleted_at'):exec()
Schema.users:whereNull('email_verified_at'):exec()

IS NOT NULL

-- Check for non-NULL values
Schema.users:whereNotNull('email'):exec()
Schema.users:whereNotNull('last_login_at'):exec()

Logical Operators

AND Conditions

-- Multiple where conditions are AND by default
Schema.users
    :where('status', 'active')
    :where('age', '>=', 18)
    :where('verified', true)
    :exec()

-- Explicit AND grouping
Schema.users:where(function(query)
    query:where('status', 'active')
         :where('age', '>=', 18)
end):exec()

OR Conditions

-- OR conditions using orWhere
Schema.users
    :where('status', 'active')
    :orWhere('status', 'pending')
    :exec()

-- Complex OR grouping
Schema.users:where(function(query)
    query:where('status', 'active')
         :orWhere('priority', 'high')
end):exec()

NOT Conditions

-- NOT with where conditions
Schema.users:whereNot('status', 'banned'):exec()

-- NOT with complex conditions
Schema.users:whereNot(function(query)
    query:where('status', 'banned')
         :orWhere('deleted', true)
end):exec()

Advanced Operators

EXISTS

-- Check if related records exist
Schema.users:whereExists(function(query)
    query:select(Sleet.raw('1'))
         :from('orders')
         :whereRaw('orders.user_id = users.id')
end):exec()

NOT EXISTS

-- Check if related records don't exist
Schema.users:whereNotExists(function(query)
    query:select(Sleet.raw('1'))
         :from('orders')
         :whereRaw('orders.user_id = users.id')
end):exec()

Raw Operators

-- Custom SQL operators
Schema.users:whereRaw('YEAR(created_at) = ?', {2023}):exec()
Schema.users:whereRaw('MATCH(name, description) AGAINST(?)', {'search term'}):exec()

-- Complex raw conditions
Schema.products:whereRaw('price * discount_rate > ?', {100}):exec()

Date and Time Operators

Date Comparisons

-- Date equality
Schema.orders:whereDate('created_at', '2023-01-01'):exec()

-- Date range
Schema.orders:whereDate('created_at', '>=', '2023-01-01'):exec()

-- Month/Year comparisons
Schema.orders:whereMonth('created_at', 12):exec()
Schema.orders:whereYear('created_at', 2023):exec()

Time Comparisons

-- Time-based conditions
Schema.logs:whereTime('created_at', '>=', '14:00:00'):exec()
Schema.events:whereTime('start_time', 'BETWEEN', {'09:00:00', '17:00:00'}):exec()

JSON Operators (MySQL 5.7+)

JSON Path Queries

-- JSON contains
Schema.users:where('preferences->theme', 'dark'):exec()

-- JSON array contains
Schema.products:whereJsonContains('tags', 'electronics'):exec()

-- JSON length
Schema.users:whereJsonLength('settings', '>', 5):exec()

String Functions as Operators

Case Operations

-- Case-insensitive comparisons
Schema.users:whereRaw('LOWER(email) = LOWER(?)', {'ADMIN@EXAMPLE.COM'}):exec()

-- String length
Schema.users:whereRaw('LENGTH(name) > ?', {10}):exec()

Operator Precedence

When combining multiple operators, be aware of SQL operator precedence:

-- Parentheses for explicit grouping
Schema.users:where(function(query)
    query:where('status', 'active')
         :orWhere('priority', 'high')
end):where('verified', true):exec()

-- This creates: (status = 'active' OR priority = 'high') AND verified = true

Custom Operators

You can create custom operator logic using raw queries:

-- Custom distance calculation
Schema.locations:whereRaw('SQRT(POW(? - latitude, 2) + POW(? - longitude, 2)) < ?', {
    userLat, userLon, maxDistance
}):exec()

-- Full-text search (MySQL)
Schema.articles:whereRaw('MATCH(title, content) AGAINST(? IN BOOLEAN MODE)', {
    '+required -excluded'
}):exec()

Performance Considerations

• Always use appropriate indexes for columns used in WHERE conditions
• LIKE patterns starting with % cannot use indexes efficiently
• Use LIMIT with ORDER BY for better performance on large datasets
• Consider using EXISTS instead of IN for subqueries with large result sets

-- Good: Uses index on status column
Schema.users:where('status', 'active'):exec()

-- Less efficient: Leading wildcard prevents index usage
Schema.users:where('name', 'LIKE', '%john%'):exec()

-- Better: Trailing wildcard can use index
Schema.users:where('name', 'LIKE', 'john%'):exec()