Operators

Sleet ORM provides a complete set of operators for building type-safe query conditions. All operators automatically handle parameter binding to prevent SQL injection.

Comparison Operators

Basic Comparisons

local sl = Sleet
local s  = require 'server.schema'
local db = sl.connect()

-- Equal to
local player = db.select()
    .from(s.players)
    .where(sl.eq(s.players.id, 123))
    .execute()[1]

-- Not equal to
local activePlayers = db.select()
    .from(s.players)
    .where(sl.ne(s.players.status, 'banned'))
    .execute()

-- Greater than
local highLevelPlayers = db.select()
    .from(s.players)
    .where(sl.gt(s.players.level, 50))
    .execute()

-- Greater than or equal to
local eligiblePlayers = db.select()
    .from(s.players)
    .where(sl.gte(s.players.age, 18))
    .execute()

-- Less than
local newPlayers = db.select()
    .from(s.players)
    .where(sl.lt(s.players.level, 10))
    .execute()

-- Less than or equal to
local moderateWarnings = db.select()
    .from(s.players)
    .where(sl.lte(s.players.warnings, 3))
    .execute()

Operator Reference

Operator	SQL	Description	Example
sl.eq(col, val)	=	Equal to	sl.eq(s.players.name, 'John')
sl.ne(col, val)	!=	Not equal to	sl.ne(s.players.status, 'banned')
sl.gt(col, val)	>	Greater than	sl.gt(s.players.level, 10)
sl.gte(col, val)	>=	Greater than or equal	sl.gte(s.players.money, 1000)
sl.lt(col, val)	<	Less than	sl.lt(s.players.age, 60)
sl.lte(col, val)	<=	Less than or equal	sl.lte(s.players.warnings, 5)

String Operators

Pattern Matching

-- LIKE pattern matching
local playersWithJohn = db.select()
    .from(s.players)
    .where(sl.like(s.players.name, '%John%'))
    .execute()

-- Starts with pattern
local steamPlayers = db.select()
    .from(s.players)
    .where(sl.like(s.players.identifier, 'steam:%'))
    .execute()

-- Ends with pattern
local gmailUsers = db.select()
    .from(s.players)
    .where(sl.like(s.players.email, '%@gmail.com'))
    .execute()

-- NOT LIKE
local nonDiscordPlayers = db.select()
    .from(s.players)
    .where(sl.notLike(s.players.identifier, 'discord:%'))
    .execute()

-- ILIKE case-insensitive (MySQL is case-insensitive by default)
local caseInsensitive = db.select()
    .from(s.players)
    .where(sl.ilike(s.players.name, '%JOHN%'))
    .execute()

String Operator Reference

Operator	SQL	Description	Example
sl.like(col, pattern)	LIKE	Pattern match	sl.like(s.players.name, '%John%')
sl.notLike(col, pattern)	NOT LIKE	Does not match	sl.notLike(s.players.email, '%spam%')
sl.ilike(col, pattern)	LIKE	Case-insensitive match	sl.ilike(s.players.name, '%ADMIN%')

Wildcard Reference

-- % matches zero or more characters
sl.like(s.players.name, 'John%')    -- starts with 'John'
sl.like(s.players.name, '%Smith')   -- ends with 'Smith'
sl.like(s.players.name, '%John%')   -- contains 'John'

-- _ matches single character
sl.like(s.players.code, 'A_B')      -- one char between A and B
sl.like(s.players.phone, '138____1234') -- specific phone format

NULL Operators

-- Check for NULL values
local playersWithoutEmail = db.select()
    .from(s.players)
    .where(sl.isNull(s.players.email))
    .execute()

-- Check for non-NULL values
local playersWithEmail = db.select()
    .from(s.players)
    .where(sl.isNotNull(s.players.email))
    .execute()

-- Find unbanned players (banned_until is NULL)
local activePlayers = db.select()
    .from(s.players)
    .where(sl.isNull(s.players.banned_until))
    .execute()

-- Find currently banned players
local bannedPlayers = db.select()
    .from(s.players)
    .where(sl.isNotNull(s.players.banned_until))
    .execute()

NULL Operator Reference

Operator	SQL	Description	Example
sl.isNull(col)	IS NULL	Value is null	sl.isNull(s.players.deleted_at)
sl.isNotNull(col)	IS NOT NULL	Value is not null	sl.isNotNull(s.players.email)

Range Operators

IN and NOT IN

-- IN - value in list
local vipPlayers = db.select()
    .from(s.players)
    .where(sl.inArray(s.players.role, { 'admin', 'moderator', 'vip' }))
    .execute()

-- Find specific levels
local specificLevels = db.select()
    .from(s.players)
    .where(sl.inArray(s.players.level, { 10, 20, 30, 40, 50 }))
    .execute()

-- NOT IN - value not in list
local normalPlayers = db.select()
    .from(s.players)
    .where(sl.notInArray(s.players.status, { 'banned', 'suspended', 'deleted' }))
    .execute()

-- Exclude specific IDs
local otherPlayers = db.select()
    .from(s.players)
    .where(sl.notInArray(s.players.id, { 1, 2, 3 }))
    .execute()

BETWEEN

-- BETWEEN range query
local middleAged = db.select()
    .from(s.players)
    .where(sl.between(s.players.age, 25, 65))
    .execute()

-- Date range
local recentPlayers = db.select()
    .from(s.players)
    .where(sl.between(s.players.created_at, '2024-01-01', '2024-12-31'))
    .execute()

-- Money range
local middleClass = db.select()
    .from(s.players)
    .where(sl.between(s.players.money, 10000, 100000))
    .execute()

Range Operator Reference

Operator	SQL	Description	Example
sl.inArray(col, values)	IN (...)	Value in list	sl.inArray(s.players.role, {'admin', 'user'})
sl.notInArray(col, values)	NOT IN (...)	Value not in list	sl.notInArray(s.players.status, {'banned'})
sl.between(col, min, max)	BETWEEN ... AND ...	Value in range	sl.between(s.players.level, 10, 50)

Logical Operators

AND Conditions

-- AND - all conditions must match
local activeVipPlayers = db.select()
    .from(s.players)
    .where(sl.and_(
        sl.eq(s.players.is_active, true),
        sl.eq(s.players.is_vip, true),
        sl.gte(s.players.level, 10)
    ))
    .execute()

-- Complex AND
local specificPlayers = db.select()
    .from(s.players)
    .where(sl.and_(
        sl.like(s.players.name, 'John%'),
        sl.gte(s.players.money, 5000),
        sl.lte(s.players.warnings, 2),
        sl.isNotNull(s.players.email)
    ))
    .execute()

OR Conditions

-- OR - any condition matches
local privilegedPlayers = db.select()
    .from(s.players)
    .where(sl.or_(
        sl.eq(s.players.is_admin, true),
        sl.eq(s.players.is_moderator, true),
        sl.gte(s.players.level, 100)
    ))
    .execute()

-- Multiple status check
local availablePlayers = db.select()
    .from(s.players)
    .where(sl.or_(
        sl.eq(s.players.status, 'online'),
        sl.eq(s.players.status, 'idle'),
        sl.eq(s.players.status, 'away')
    ))
    .execute()

NOT Conditions

-- NOT - negate condition
local notBannedPlayers = db.select()
    .from(s.players)
    .where(sl.not_(sl.eq(s.players.is_banned, true)))
    .execute()

-- Equivalent to
local notBannedPlayers2 = db.select()
    .from(s.players)
    .where(sl.eq(s.players.is_banned, false))
    .execute()

-- Complex NOT
local exceptionalPlayers = db.select()
    .from(s.players)
    .where(sl.not_(sl.and_(
        sl.lt(s.players.level, 10),
        sl.eq(s.players.is_premium, false)
    )))
    .execute()

Logical Operator Reference

Operator	SQL	Description	Example
sl.and_(cond1, cond2, ...)	AND	All conditions match	sl.and_(sl.eq(...), sl.gt(...))
sl.or_(cond1, cond2, ...)	OR	Any condition matches	sl.or_(sl.eq(...), sl.eq(...))
sl.not_(condition)	NOT	Negate condition	sl.not_(sl.eq(s.players.active, false))

Complex Condition Combinations

Nested Logic

-- Complex condition nesting
local targetPlayers = db.select()
    .from(s.players)
    .where(sl.and_(
        -- Base active conditions
        sl.eq(s.players.is_active, true),
        sl.isNull(s.players.deleted_at),
        
        -- Complex permission or level conditions
        sl.or_(
            -- Admin or moderator
            sl.or_(
                sl.eq(s.players.is_admin, true),
                sl.eq(s.players.is_moderator, true)
            ),
            -- Or high-level VIP
            sl.and_(
                sl.eq(s.players.is_vip, true),
                sl.gte(s.players.level, 50)
            ),
            -- Or wealthy regular player
            sl.and_(
                sl.gte(s.players.money, 100000),
                sl.gte(s.players.bank, 500000)
            )
        )
    ))
    .execute()

Dynamic Condition Building

-- Build query conditions from parameters
function FindPlayers(filters)
    local conditions = {}
    
    -- Base: non-deleted users
    table.insert(conditions, sl.isNull(s.players.deleted_at))
    
    -- Optional filters
    if filters.name then
        table.insert(conditions, sl.like(s.players.name, '%' .. filters.name .. '%'))
    end
    
    if filters.minLevel then
        table.insert(conditions, sl.gte(s.players.level, filters.minLevel))
    end
    
    if filters.maxLevel then
        table.insert(conditions, sl.lte(s.players.level, filters.maxLevel))
    end
    
    if filters.roles and #filters.roles > 0 then
        table.insert(conditions, sl.inArray(s.players.role, filters.roles))
    end
    
    if filters.isActive ~= nil then
        table.insert(conditions, sl.eq(s.players.is_active, filters.isActive))
    end
    
    -- Combine all conditions
    local whereClause = (#conditions == 1) and conditions[1] or sl.and_(table.unpack(conditions))
    
    return db.select()
        .from(s.players)
        .where(whereClause)
        .execute()
end

-- Usage
local results = FindPlayers({
    name = 'John',
    minLevel = 10,
    roles = { 'admin', 'moderator' },
    isActive = true
})

Best Practices

1. Use Indexed Columns

-- Good: use indexed columns for equality
db.select()
    .from(s.players)
    .where(sl.eq(s.players.identifier, identifier))  -- identifier usually has unique index
    .execute()

-- Avoid: leading wildcard prevents index usage on large tables
-- db.select().from(s.players).where(sl.like(s.players.name, '%John%'))
-- Prefer full-text search or dedicated search columns

2. Condition Order

-- Put most selective conditions first
db.select()
    .from(s.players)
    .where(sl.and_(
        sl.eq(s.players.identifier, identifier),  -- most selective (unique)
        sl.eq(s.players.is_active, true),        -- medium selectivity
        sl.isNotNull(s.players.name)             -- least selective
    ))
    .execute()

3. Avoid Deep Nesting

-- Break complex conditions into variables
local roleCondition = sl.inArray(s.players.role, { 'admin', 'moderator' })
local levelCondition = sl.gte(s.players.level, 50)
local moneyCondition = sl.gte(s.players.money, 100000)

local finalCondition = sl.and_(
    sl.eq(s.players.is_active, true),
    sl.or_(roleCondition, levelCondition, moneyCondition)
)

local results = db.select()
    .from(s.players)
    .where(finalCondition)
    .execute()

4. Parameter Safety

-- Correct: parameter binding (automatic protection)
local userInput = "John'; DROP TABLE users; --"
db.select()
    .from(s.players)
    .where(sl.eq(s.players.name, userInput))  -- safe, auto-escaped
    .execute()

-- Wrong: string concatenation leads to SQL injection
-- local sql = "SELECT * FROM players WHERE name = '" .. userInput .. "'"

Next Steps

• Learn Query Builder advanced usage
• See Column Types definitions
• See Examples for more real-world scenarios