# Querying with WDB

https://dev.wisecp.com/es/querying-with-wdb

One query builder, two entry points, and a chain that changes nothing until it is finished.

## Overview

Queries are built by chaining calls rather than by writing SQL into a string. The chain reads in the order the statement is written. Every value you pass as an argument is bound instead of pasted.

`WDB` is a static facade. Each of its methods forwards to the one `Database` object the model layer holds. The static form and a model's `$this->db` are the same connection and the same builder state.

## Reference

### Entry Points

- **WDB::**: The static facade, for helpers, operations, modules and hooks. Every call forwards to the object below.
- **$this->db**: The same object inside a model, built once per process by the lazy getter on `Models`. A chain may be started on one form and continued on the other.
- **Database**: The builder itself. Methods with no static wrapper (`whereGroup`, `getCount`, `pushState`) are reached off the object a chain returns.

### Reading

```php
// Name the columns. "*" hands your callers whatever the table grows into later.
public static function select($arg = "*");              // a falsy $arg becomes "*"
public static function from($arg = '');                 // "users_products" | "users_products AS t"
public static function join($type, $table, $where);     // $type: "LEFT" | "INNER" | "RIGHT" - pasted before " JOIN "
public static function where($column, $mark = '', $value = '', $logical = '');
public static function group_by($arg = '');             // raw expression, not bound
public static function order_by($arg);                  // raw expression, not bound
public static function limit($arg1, $arg2 = null);      // limit(25) -> LIMIT 0,25 · limit(50, 25) -> LIMIT 50,25
public static function build($isthis = false);          // bool: matched rows > 0 · $isthis = true returns the Database object

// Fetchers read the statement build() left behind, or the one you hand them.
public static function fetch_assoc($statement = false); // list of rows, each column => value; [] when none matched
public static function getAssoc($statement = false);    // one row as column => value; false when there is no row
public static function fetch_object($statement = false);// the same list as stdClass objects
public static function getObject($statement = false);   // one row as stdClass; false when there is no row
public static function rowCounter($statement = false);  // int: rows the last statement touched
```

### What where() Accepts

The second argument is written into the statement as-is. It is an SQL operator, not a keyword from a list the builder owns. Three shapes are special-cased.

| $mark | $value | Produced SQL |
| --- | --- | --- |
| `'='` `'!='` | scalar; an empty string is still bound | `col = ?` |
| `'>'` `'<'` `'>='` `'<='` | scalar | `col > ?` |
| `'LIKE'` `'NOT LIKE'` | scalar carrying its own `%` | `col LIKE ?` |
| `'IN'` `'NOT IN'` | **an array**, one placeholder per element | `col IN (?,?,?)` |
| `'IS'` `'IS NOT'` | `'NULL'`, pasted rather than bound | `col IS NULL` |
| `'IS NOT NULL'` | `''`, the whole test is the operator | `col IS NOT NULL` |
| omitted (`''`) | a non-empty scalar | `col = ?` |

```php
// $logical accepts exactly 'AND', '&&', 'OR', '||'. It joins THIS condition to the NEXT
// one; a condition left without it is given AND automatically once the next one arrives.
public static function where($column, $mark = '', $value = '', $logical = '');

// Parentheses: no static wrapper, so it is called on the object the chain returns.
// $logical joins the whole group to the condition that FOLLOWS it, exactly like the
// fourth argument of where(). Leaving it out is safe: the group is recorded as a
// joinable predicate, so the next condition supplies AND by itself. Pass it for OR.
public function whereGroup(callable $callback, string $logical = ''): self;
```

### Writing

```php
public static function insert($table, $data);             // int affected rows; throws on an SQL error
public static function lastID();                          // int identifier the insert produced; 0 with no connection
public static function update($table = '', $data = []);   // a non-empty $data calls set() for you
public static function set($data = [], $special = false); // $special = true pastes the value RAW: ['uses' => 'uses+1']
public static function save($isthis = false);             // bool: no error - TRUE also when zero rows matched
public static function delete($arg = '', $arg2 = '');     // delete("t") | delete("a", "a INNER JOIN b ON ...")
public static function run($isthis = false);              // bool: deleted rows > 0

// $data for insert() and update()/set() is column => value. A null value is bound as
// SQL NULL, an int as an integer, everything else as a string.
// limit() applies to save() and run() too, as a row count with no offset: limit(5000).
// order_by() picks which rows a limited write takes. Two chains throw instead of
// running, because MySQL rejects them: update()->join(), and a multi-table
// delete("t", "table t") carrying order_by() or limit().
```

### Raw Statements

```php
public static function query($statement, $isthis = false); // PDOStatement, or false - the error is swallowed
public static function exec($arg = '');                    // int affected rows; 0 on error - the error is swallowed
public static function hasTable($table = '');              // bool, via SHOW TABLES LIKE - the name is pasted, no prefix
public static function getPrefix(): string;                // the schema prefix from the database configuration
public static function getErrorMessage();                  // text of the last error, including the swallowed ones
```

## Example

```php
// A page of rows.
WDB::select('t.id, t.name, t.status, u.full_name')
    ->from('users_products AS t')
    ->join('LEFT', 'users AS u', 'u.id = t.owner_id')
    ->where('t.status', 'IN', ['active', 'inprocess'])
    ->where('t.type', '=', 'hosting')
    ->order_by('t.id DESC')
    ->limit(0, 25)
    ->build();

$rows = WDB::fetch_assoc();                 // [] when nothing matched

// One row. build() is false when nothing matched, which is the guard to write.
$stmt = WDB::select('id, name, status')->from('users_products')->where('id', '=', $id)->limit(1);
$row  = $stmt->build() ? $stmt->getAssoc() : [];

// A bound OR group, isolated from the conditions around it.
$search = WDB::select('id')->from('users_products');
$search->where('owner_id', '=', $userId);
$search->whereGroup(function ($q) use ($word) {
    $q->where('name', 'LIKE', '%' . $word . '%', '||');
    $q->where('status', 'LIKE', '%' . $word . '%');
}, '&&');
$search->where('type', '=', 'hosting');
$found = $search->build() ? $search->fetch_assoc() : [];
```

```php
// Insert returns the number of affected rows, and the identifier is read afterwards.
$affected = WDB::insert('users_products', [
    'owner_id'   => $userId,
    'product_id' => $productId,
    'name'       => $name,
    'status'     => 'waiting',
    'notes'      => null,                   // bound as SQL NULL
    'cdate'      => DateManager::Now(),
]);
$newId = $affected ? (int) WDB::lastID() : 0;

// Update: the chain does nothing until save().
WDB::update('users_products', ['status' => 'active'])->where('id', '=', $newId)->save();

// The one value that is deliberately not bound: an expression evaluated by the server.
WDB::update('coupons')->set(['uses' => 'uses+1'], true)->where('id', '=', $couponId)->save();

// Read the write back, from the other entry point - it is the same connection.
$check = WDB::select('status')->from('users_products')->where('id', '=', $newId);
$saved = $check->build() ? ($check->getAssoc()['status'] ?? '') : '';

// Delete: run(), not save().
WDB::delete('users_products')->where('id', '=', $newId)->run();
```

## Pitfalls

> **The value argument is the safety**
> 
> Passing the value as an argument is what makes it bound. Building it into the column expression instead moves it into the statement. The first quote in a user's input then becomes an injection. Three places are unbound on purpose and must never receive user input. They are `order_by` and `group_by`, `set($data, true)`, and the table name given to `hasTable`.

> **Two arguments that are read by position**
> 
> `limit(25)` is the first 25 rows, `limit(50, 25)` is 25 rows from offset 50. The count moves to the second slot as soon as there is an offset. The fourth argument of `where()` joins that condition to the **next** one, not to the previous one. It belongs on the condition before the alternative, never on the last one in a group.

> **A write is not finished until it is committed, and a commit is not a match**
> 
> An update needs `save()` and a delete needs `run()`; a chain that stops at the conditions changes nothing and reports nothing. Their return values then disagree on purpose. `save()` answers "no error", so it is true even when the condition matched no row. `run()` and `build()` answer "rows > 0". Checking that a record really changed means reading it back, not trusting the boolean.

> **The builder throws, the raw statements do not**
> 
> `build()`, `insert()`, `save()` and `run()` turn a failed statement into an exception, which an operation converts into an error response for you. `query()` and `exec()` swallow it instead and return `false` or `0`, so a schema statement can fail in complete silence. When you reach for them, read `getErrorMessage()`.

## Related Articles

- [The Model Layer](https://dev.wisecp.com/en/the-model-layer)
- [Changing the Database Schema](https://dev.wisecp.com/en/changing-the-database-schema)
- [Domain Helpers](https://dev.wisecp.com/en/domain-helpers)
- [Caching](https://dev.wisecp.com/en/caching)