Dynamic WHERE Clause Construction

The where_clause ($emps->db->where_clause()) method takes an associative array describing a set of conditions and converts it into a SQL WHERE clause. In this array, each key is either a column name or a special operator that defines how the condition should be built.

The design of the where_clause method is notably inspired by the query syntax used in NoSQL databases like MongoDB. In MongoDB, queries are structured as JSON-like objects where you can use operators such as $gt, $lt, $in, and others to specify conditions on document fields. This approach allows developers to write complex queries in a more natural, object-oriented format, without having to manually construct SQL strings. By adopting a similar paradigm, EMPS_DB enables you to build SQL conditions using structured PHP arrays, making your code more intuitive and expressive.

This design choice not only simplifies the process of building and maintaining queries but also bridges the gap between NoSQL and SQL thinking. Developers who are accustomed to the flexibility of MongoDB’s query syntax will find it familiar and straightforward to construct conditions in EMPS_DB. In addition, by encapsulating SQL logic within a structured array, the risk of errors from manual string concatenation is minimized, resulting in cleaner, more maintainable code that is easier to debug and extend.

Below is a list of all supported keywords and how they work:

Special Logical Operators

$and
- Usage: An array of conditions that should be combined with AND.
- Example:
```
['$and' => [
  'status' => 1,
  'type' => 'admin'
]]
```
$or
- Usage: An array of conditions that should be combined with OR.
- Example:
```
['$or' => [
  'status' => 0,
  'status' => 2
]]
```
$nt
- Usage: Negates the entire subclause that follows.
- Example:
```
['$nt' => ['status' => 1]]
```
- This generates a clause like: notstatus= 1.

Comparison Operators (used within an array for a given column)

When a column’s value is provided as an array, you can use the following keys to control the comparison:

$gt
- Meaning: Greater than.
- Example:
```
'price' => ['$gt' => 100]
```
- Generates: `price` > 100.
$gte
- Meaning: Greater than or equal to.
- Example:
```
'price' => ['$gte' => 100]
```
- Generates: `price` >= 100.
$lt
- Meaning: Less than.
- Example:
```
'price' => ['$lt' => 200]
```
- Generates: `price` < 200.
$lte
- Meaning: Less than or equal to.
- Example:
```
'price' => ['$lte' => 200]
```
- Generates: `price` <= 200.
$range
- Meaning: Specifies a lower and upper bound.
- Example:
```
'price' => ['$range' => [100, 200]]
```
- Generates: `price` >= 100 and `price` <= 200.
$like
- Meaning: Uses SQL’s LIKE operator for pattern matching.
- Example:
```
'name' => ['$like' => '%john%']
```
- Generates: `name` like ('%john%').
$in
- Meaning: Tests if the column value is in a set.
- Example:
```
'id' => ['$in' => '1,2,3']
```
- Generates: `id` in (1,2,3).
$nin
- Meaning: Tests if the column value is NOT in a set.
- Example:
```
'id' => ['$nin' => '4,5,6']
```
- Generates: `id` not in (4,5,6).
$not
- Meaning: Not equal to.
- Example:
```
'status' => ['$not' => 0]
```
- Generates: `status` <> 0.
$ev
- Meaning: Inserts a raw SQL expression for the value.
- Example:
```
'full_id' => ['$ev' => " > unhex('FFBBAA')"]
```
- Generates: `full_id` > unhex('FFBBAA') (the expression is appended directly).

Default Handling

Direct Equality:
If the value for a column is neither an array nor a special operator, it is handled as an equality test.
Example:
```
'status' => 1
```
Generates: `status` = 1 (or, if a string, the value will be escaped and wrapped in quotes).
Array without a Keyword:
If the value is an array that does not contain any of the special keys above, the method treats it as an IN clause.
Example:
```
'id' => [1, 2, 3]
```
Generates: `id` in (1, 2, 3) after each item is appropriately escaped.

Putting It All Together

You can mix these keywords to build complex conditions. For example:

$q = [
    'status' => 0,
    'tdt' => ['$range' => [$sdt, $edt]],
    'name' => ['$like' => '%corp%'],
    '$or' => [
        'type' => 'client',
        'type' => 'partner'
    ]
];

$where = $emps->db->where_clause($q);

This would generate a SQL WHERE clause roughly equivalent to:

(`status` = 0 and `tdt` >= $sdt and `tdt` <= $edt and `name` like ('%corp%')) and ((`type` = 'client' or `type` = 'partner'))

Note: The actual output will include the table prefix stored in $this->where_table before each column name.

This list covers all the keywords and operators supported by the where_clause function in EMPS_DB, allowing you to build a wide range of SQL conditions using structured arrays.