where

Where condition values are essential for filtering and retrieving datas.

Parameters below can be used to the following methods:

findfindOneupdatedeletesoftDeletecountpaginate

Where conditions are flexible you may use it like these:

// Inline
$db->news->find(array('id' => 1));

// Inside where object
$db->news->find(
    array(
        'where' => array(
            'id' => 1
        )
    )
);

Using Primary ID:

$db->{table}->{method}(1);
$db->{table}->{method}(array('id' => 1));
$db->{table}->{method}(
    array(
        'where' => array(
            'id' => 1
        )
    )
);

Example:

$db->news->find(1);
$db->news->find(array('id' => 1));
$db->news->find(
    array(
        'where' => array(
            'id' => 1
        )
    )
);

Using other columns:

$db->{table}->{method}(array('status' => 'ACTIVE'));

Example:

$db->news->find(array('status' => 'ACTIVE'));
$db->news->find(array(
        'status' => 'ACTIVE',
        'created_at' => '2024-12-11 00:00:00'
    )
);
$db->news->find(array(
        'where' => array(
             'status' => 'ACTIVE',
             'created_at' => '2024-12-11 00:00:00'
        )
    )
);

---

Complex Conditions:

The conditions below can be wrapped inside the "where" object/array

This scopes the equal, like, greater than, greater than or equal, etc.

equal (=)

SELECT * FROM news WHERE id = 1

$db->news->find(
    array(
        'id' => array(
            'eq' => 1
        )
    )
);
// or 
$db->news->find(array('id' => 1));

in ( IN (....) )

SELECT * FROM news WHERE id in (1, 2, 3)

$db->news->find(
    array(
        'id' => array(
            'in' => array(1, 2, 3)
        )
    )
);

gt ( > $ )

SELECT * FROM news WHERE id > 5

$db->news->find(
    array(
        'id' => array(
            'gt' => 5
        )
    )
);

gte ( >= $ )

SELECT * FROM news WHERE id >= 5

$db->news->find(
    array(
        'id' => array(
            'gte' => 5
        )
    )
);

lt ( < $ )

SELECT * FROM news WHERE id < 5

$db->news->find(
    array(
        'id' => array(
            'lt' => 5
        )
    )
);

lte ( <= $ )

SELECT * FROM news WHERE id <= 5

$db->news->find(
    array(
        'id' => array(
            'lte' => 5
        )
    )
);

like ( LIKE '%$%' )

SELECT * FROM news WHERE name like '%test%'

$db->news->find(
    array(
        'id' => array(
            'like' => 'test'
        )
    )
);

between ( BETWEEN $ and $ )

SELECT * FROM news WHERE created_at between '2024-01-01' and '2024-12-31'

$db->news->find(
    array(
        'id' => array(
            'between' => array('2024-01-01', '2024-12-31')
        )
    )
);

---

How about the or's and the and's ?

or

SELECT * FROM news WHERE name like '%your test%' or name = 'my test'

$or_condition = array(
    'name' => array(
        'or' => array(
            'like' => 'your test',
            'eq' => 'my test',
        )
    )
);

and

SELECT * FROM news WHERE id ≥ 1 and id <= 20

$and_condition = array(
    'id' => array(
        'and' => array(
            'gte' => 1,
            'lte' => 20
        )
    )
);
// or
$and_condition = array(
    'id' => array(
        'gte' => 1,
        'lte' => 20
    )
);

not_null

SELECT * FROM news WHERE name IS NOT NULL

$not_null = array(
    'name' => array(
        'not_null' => true
    )
);

null

SELECT * FROM news WHERE name NULL

$is_null = array(
    'name' => array(
        'null' => true
    )
);

---

Combining Conditions

SELECT * FROM news

WHERE name like '%your test%' and name = 'my test'

and created_at between 'date1' and 'date2'

$where = array(
    'name' => array(
        'like' => 'your test',
        'eq' => 'my test'
    ),
    'created_at' => array(
        'between' => array(
            'date1',
            'date2'
        )
    )
)