Skip to content

Latest commit

 

History

History
144 lines (107 loc) · 2.34 KB

comparison-helpers.md

File metadata and controls

144 lines (107 loc) · 2.34 KB

Comparison Helper Functions

Build complex WHERE conditions using comparison helpers.

LIKE Pattern Matching

Basic LIKE

use tommyknocker\pdodb\helpers\Db;

$users = $db->find()
    ->from('users')
    ->where(Db::like('email', '%@example.com'))
    ->get();

Case-Insensitive LIKE

$users = $db->find()
    ->from('users')
    ->where(Db::ilike('name', 'john%'))
    ->get();

NOT LIKE

$users = $db->find()
    ->from('users')
    ->where(Db::not(Db::like('email', '%@spam.com')))
    ->get();

BETWEEN Ranges

Value Range

$users = $db->find()
    ->from('users')
    ->where(Db::between('age', 18, 65))
    ->get();

// SQL: age BETWEEN 18 AND 65

NOT BETWEEN

$users = $db->find()
    ->from('users')
    ->where(Db::notBetween('age', 0, 17))
    ->get();

IN Clauses

Basic IN

$users = $db->find()
    ->from('users')
    ->where(Db::in('status', ['active', 'pending', 'verified']))
    ->get();

NOT IN

$users = $db->find()
    ->from('users')
    ->where(Db::notIn('status', ['deleted', 'banned']))
    ->get();

NULL Checks

IS NULL

$users = $db->find()
    ->from('users')
    ->where(Db::isNull('deleted_at'))
    ->get();

IS NOT NULL

$users = $db->find()
    ->from('users')
    ->where(Db::isNotNull('email'))
    ->get();

Coalesce

Handle NULL Values

$users = $db->find()
    ->from('users')
    ->select([
        'display_name' => Db::coalesce('username', 'email', 'Anonymous')
    ])
    ->get();

Common Patterns

Search Multiple Fields

$keyword = $_GET['search'];

$users = $db->find()
    ->from('users')
    ->where(function($query) use ($keyword) {
        $query->where(Db::like('name', "%{$keyword}%"))
             ->orWhere(Db::like('email', "%{$keyword}%"))
             ->orWhere(Db::like('bio', "%{$keyword}%"));
    })
    ->get();

Active User Filter

$activeUsers = $db->find()
    ->from('users')
    ->where(Db::isNull('deleted_at'))
    ->andWhere(Db::in('status', ['active', 'verified']))
    ->andWhere(Db::between('age', 18, 65))
    ->get();

Next Steps