The Query builder

Below the generated queries sits a general Query — a builder for any statement against any table, with the values always bound rather than interpolated. It is what you reach for when there is no model behind the data.

Starting a query

A query begins with one of the six static factories, each naming the statement it will produce:

use Framework\Database\Query\Query;

Query::select("products");     // SELECT … FROM products
Query::insert("products");     // INSERT INTO products
Query::replace("products");    // REPLACE INTO products
Query::update("products");     // UPDATE products
Query::delete("products");     // DELETE FROM products
Query::truncate("products");   // TRUNCATE products

Each takes an optional alias, and the whole thing chains:

$query = Query::select("products", "p")
    ->columns("p.productID", "p.name", "p.price")
    ->where("p.status", "=", "Active")
    ->orderBy("p.name", true)
    ->limit(20);

Reading the results

Nothing runs until you ask for the result. The read methods return a Dictionary, so the values come back typed:

// Every row
$rows = Query::select("products")
    ->where("status", "=", "Active")
    ->getAll();

foreach ($rows as $row) {
    $row->getInt("productID");
    $row->getString("name");
    $row->getFloat("price");
}

// A single row
$row = Query::select("products")
    ->where("productID", "=", $productID)
    ->getOne();

if ($row->isEmpty()) {
    return;
}

// A single value — no Dictionary needed
$name  = Query::select("products")
    ->where("productID", "=", $productID)
    ->getString("name");

$total = Query::select("products")
    ->column("COUNT(*)", "total")
    ->where("status", "=", "Active")
    ->getInt("total");

A write returns the number of affected rows through execute():

$amount = Query::update("products")
    ->set("status", "Paused")
    ->where("categoryID", "=", $categoryID)
    ->execute();

Conditions

where() takes a column, an operator and a value. The operator can be a string or an Operator case — the value is always bound, never pasted into the SQL:

Query::select("products")
    ->where("status", "=", "Active")
    ->where("price", ">", 100)
    ->where("categoryID", "IN", [ 1, 2, 3 ])
    ->where("name", "LIKE", $search)
    ->where("deletedTime", "<>", 0);

whereIf() only adds the condition when the value is not empty, which is what makes a filter read straight down with no if around each line:

$query = Query::select("products")
    ->whereIf("categoryID", "=", $categoryID)
    ->whereIf("name", "LIKE", $search)
    ->whereIf("status", "=", $status);

For anything the operators cannot express, whereExp() takes a fragment with ? placeholders, still bound:

Query::select("products")
    ->whereExp("price * stock > ?", 10000)
    ->whereExp("DATE(createdTime) = ?", $date);

And, or and parentheses

Conditions are joined with AND. orWhere() adds one with OR, and the startOr() / endOr() and startAnd() / endAnd() pairs group them with the parentheses written for you:

// status = "Active" AND (name LIKE ? OR code LIKE ?)
Query::select("products")
    ->where("status", "=", "Active")
    ->startOr()
        ->where("name", "LIKE", $search)
        ->where("code", "LIKE", $search)
    ->endOr();

// (categoryID = 3 AND price < 100) OR (categoryID = 7 AND price < 50)
Query::select("products")
    ->startOr()
        ->startAnd()
            ->where("categoryID", "=", 3)
            ->where("price", "<", 100)
        ->endAnd()
        ->startAnd()
            ->where("categoryID", "=", 7)
            ->where("price", "<", 50)
        ->endAnd()
    ->endOr();

startParen() / endParen() wrap a group without changing how it is joined, and search() builds the multi-column search a search box needs — optionally splitting the text into words and matching any of them:

Query::select("products")
    ->search([ "name", "code", "description" ], $search, splitValue: true);

Joins

join() takes the table, an alias and the condition. It defaults to a LEFT join, and the type is the fourth argument:

Query::select("products", "p")
    ->columns("p.productID", "p.name", "c.name AS categoryName")
    ->join("categories", "c", "c.categoryID = p.categoryID")
    ->where("p.status", "=", "Active");

// An inner join, and a second table
Query::select("orders", "o")
    ->columns("o.orderID", "o.total", "u.email", "s.name AS statusName")
    ->join("users", "u", "u.userID = o.userID", "INNER")
    ->join("statuses", "s", "s.statusID = o.statusID")
    ->where("o.createdTime", ">", $fromTime);

For a condition on another table without joining it, whereExists() and whereNotExists() take a sub-query:

// Products that have at least one image
$images = Query::select("productImages")
    ->whereExp("productImages.productID = products.productID");

Query::select("products")
    ->whereExists($images);

Ordering, grouping & paging

Query::select("products")
    ->orderBy("name", true)      // ASC
    ->orderBy("price", false)    // then DESC
    ->limit(10)                  // the first 10
    ->limit(20, 10)              // 10 rows from the 20th
    ->paginate($page, $amount);  // the page a listing asked for

Grouping with an aggregate is the usual reporting shape — the columns carry the expressions, and groupBy() the keys:

$rows = Query::select("orders")
    ->columns("categoryID", "COUNT(*) AS total", "SUM(amount) AS amount")
    ->where("createdTime", ">", $fromTime)
    ->groupBy("categoryID")
    ->orderBy("amount", false)
    ->getAll();

foreach ($rows as $row) {
    $row->getInt("categoryID");
    $row->getInt("total");
    $row->getFloat("amount");
}

Inserting, updating & deleting

Writes are built the same way. set() assigns a bound value, fields() assigns several at once, and setExp() assigns a SQL expression:

// Insert one row
$query = Query::insert("products")
    ->fields([
        "name"       => $name,
        "price"      => $price,
        "categoryID" => $categoryID,
        "createdTime" => time(),
    ]);
$query->execute();

// Update the rows that match
Query::update("products")
    ->set("status", "Paused")
    ->set("modifiedTime", time())
    ->where("categoryID", "=", $categoryID)
    ->execute();

// An expression, computed by the database
Query::update("products")
    ->setExp("stock", "stock - 1")
    ->setExp("modifiedTime", "UNIX_TIMESTAMP()")
    ->where("productID", "=", $productID)
    ->execute();

// Delete, and empty a whole table
Query::delete("products")
    ->where("status", "=", "Draft")
    ->where("createdTime", "<", $oldTime)
    ->execute();

Query::truncate("productCache")->execute();

Combining queries

A query is not only a statement — it is a value you can pass to another one. Anywhere a table name is accepted, a query works too, because both satisfy QueryLike. That is what lets a complex read be assembled from parts instead of written as one string.

Selecting from another query makes it a derived table:

// The totals per category…
$totals = Query::select("orders")
    ->columns("categoryID", "SUM(amount) AS amount")
    ->where("createdTime", ">", $fromTime)
    ->groupBy("categoryID");

// …then filtered as if it were a table
$rows = Query::select($totals, "t")
    ->columns("t.categoryID", "t.amount")
    ->where("t.amount", ">", 10000)
    ->orderBy("t.amount", false)
    ->getAll();

Joining a query works the same way, so an aggregate can be attached to a listing without a correlated sub-select per row:

$counts = Query::select("productImages")
    ->columns("productID", "COUNT(*) AS total")
    ->groupBy("productID");

$rows = Query::select("products", "p")
    ->columns("p.productID", "p.name", "i.total AS imageCount")
    ->join($counts, "i", "i.productID = p.productID")
    ->where("p.status", "=", "Active")
    ->getAll();

And a query can be the condition of another through whereExists() / whereNotExists(), or the source of an insert:

// Copy the matching rows into another table
$source = Query::select("products")
    ->columns("name", "price")
    ->where("status", "=", "Draft");

Query::insert("productArchive")
    ->from($source)
    ->execute();

Typed values

Conditions do not have to be written with strings. The Operator enum names each comparison, and the generated column enums name each column, so a complex condition can be built without a literal anywhere:

use Framework\Database\Query\Operator;
use Framework\Database\Query\Query;

Query::select("products")
    ->where(ProductColumn::Status->base(), Operator::Equal, ProductStatus::Active)
    ->where(ProductColumn::Price->base(), Operator::GreaterOrEqual, 100)
    ->where(ProductColumn::CategoryID->base(), Operator::In, $categoryIDs)
    ->where(ProductColumn::Name->base(), Operator::Like, $search);

The values may be typed as well — an enum case, a Date or a list are all bound correctly, so nothing has to be converted before it goes in:

Query::select("orders")
    ->where("status", Operator::NotEqual, OrderStatus::Cancelled)   // an enum case
    ->where("createdTime", Operator::GreaterThan, $date->toTime())  // a Date
    ->where("orderID", Operator::NotIn, $excludedIDs);              // a list

Assign — values the database computes

Normally set() writes a bound value. When the new value depends on the current one, or on a SQL function, wrap it in an Assign instead — the column is updated in place, with no read first and no race between two requests:

use Framework\Database\Query\Assign;

Query::update("products")
    ->set("stock", Assign::decrease(1))       // stock = stock - 1
    ->set("views", Assign::increase())        // views = views + 1
    ->where("productID", "=", $productID)
    ->execute();

Query::update("products")
    ->set("code", Assign::uuid())
    ->set("name", Assign::upperCaseFirst())
    ->set("slug", Assign::replace(" ", "-"))
    ->set("highPrice", Assign::greatest(ProductColumn::Price))
    ->set("total", Assign::exp("price * ?", [ $quantity ]))
    ->where("productID", "=", $productID)
    ->execute();

Inside an expression __FIELD__ stands for the column being written, which is how replace() and the case helpers refer to the current value.

Debugging

Two methods show what a query will run, which is quicker than reading the builder back:

$query = Query::select("products")
    ->where("status", "=", "Active")
    ->orderBy("name", true);

$query->toSQL();        // the statement, with ? placeholders
$query->getBindings();  // the values that go with it
$query->toDebugSQL();   // the statement with the values inlined, to paste elsewhere

Slow statements are recorded on their own — anything past DB_LOG_TIME lands in the query log, with the timings and how often it ran.

The Database wrapper

Under the builder, Database is the thin MySQLi wrapper. You rarely touch it directly, but it is there for a statement the builder does not cover:

use Framework\Database\Database;

$db = Database::getInstance();

$rows = $db->getData("SELECT * FROM products WHERE status = ?", [ "Active" ]);
$db->execute("UPDATE products SET stock = stock - 1 WHERE productID = ?", [ $productID ]);
$db->getInsertID();

It also owns the structural operations the migration runs — createTable, addColumn, updateColumn, renameTable, renameColumn, createIndex, tableExists and the rest — which is how the schema is kept in step with the models without anyone writing DDL by hand.