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 Op 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);

Each operator has a string and an Op case, and the two mean the same thing:

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, an expression given on its own is the whole condition, and takes ? placeholders that are still bound:

use Framework\Database\Query\Exp;

Query::select("products")
    ->where(Exp::create("price * stock > ?", 10000))
    ->where(Exp::create("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")
    ->where("productImages.productID", "=", Exp::column("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

An expression can order too, with orderByExp(), and the values it binds go after the ones of the conditions, as the order is written after them. This puts the names that start with the text first:

Query::select("products")
    ->where("name", Op::Like, $text)
    ->orderByExp(Exp::create("CASE WHEN name LIKE ? THEN 0 ELSE 1 END", "{$text}%"))
    ->orderBy("name", true);

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 Op 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\Op;
use Framework\Database\Query\Query;

Query::select("products")
    ->where(ProductColumn::Status->base(), Op::Equal, ProductStatus::Active)
    ->where(ProductColumn::Price->base(), Op::GreaterOrEqual, 100)
    ->where(ProductColumn::CategoryID->base(), Op::In, $categoryIDs)
    ->where(ProductColumn::Name->base(), Op::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", Op::NotEqual, OrderStatus::Cancelled)   // an enum case
    ->where("createdTime", Op::GreaterThan, $date->toTime())  // a Date
    ->where("orderID", Op::NotIn, $excludedIDs);              // a list

Exp — expressions the database evaluates

A string given to where() or column() is a column name, and a value beside it is bound. When either side has to be SQL instead — a function, another column, a fragment of your own — wrap it in an Exp:

use Framework\Database\Query\Exp;

Query::select("products")
    ->column(Exp::concat("firstName", "' '", "lastName"), "fullName")
    ->where(Exp::lower("name"), "=", $search)          // LOWER(name) = ?
    ->where("price", ">", Exp::column("cost"))          // price > cost
    ->where(Exp::column("publishedTime")->isNotNull()); // the whole condition

Two more are asked of an expression rather than made on their own, and each answers with a condition:

An expression with nothing to compare against is the whole condition, which is how isNull() and a create() that carries its own operator are written. One with a value on the other side is compared as usual, and both sides may be expressions.

The three parts of an if() each take an expression or a value, so a condition and both of its answers can be built out of the same pieces:

Query::select("products")
    ->column(Exp::if(Exp::column("stock")->isNull(), "unknown", "counted"), "known")
    ->column(Exp::if(Exp::jsonValid("options"), Exp::json("options", "name"), ""), "name");

// IF(stock IS NULL, ?, ?) AS known
// IF(JSON_VALID(options), JSON_UNQUOTE(JSON_EXTRACT(options, ?)), ?) AS name

The text operators put their wildcards around the expression rather than around a bound value, so a column can be matched against a part of another one. That is what asks for a tree by its paths:

// Every page under its parent, the parent itself included
Query::select("pages")
    ->where("path", Op::StartsWith, Exp::column("parentPath"));

// WHERE path LIKE CONCAT(parentPath, '%')

The first argument of a where() is read as a column, so a value that has to sit on that side is given as one too. That turns the question around — from which stored paths are under a prefix, to which stored prefixes a path is under:

// Every prefix the given path falls under
Query::select("prefixes")
    ->where(Exp::value($path), Op::StartsWith, Exp::column("prefix"));

// WHERE ? LIKE CONCAT(prefix, '%')

The null checks are asked of the expression they follow, which is what asks whether a JSON column holds a value at all — jsonSearch() answers with a path, and nothing when it found none:

Query::update("products")
    ->set("images", Assign::jsonReplace($old, $new))
    ->where(Exp::jsonValid("images"))
    ->where(Exp::jsonSearch("images", $old)->isNotNull())
    ->execute();

The jsonValid() beside it is what lets a column that holds something other than a JSON be passed over rather than error.

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.