The Query Builder

The Query Builder assembles an SQL query from method calls and escapes the values itself. It is needed where model methods are not enough: joining tables, counting, bulk changes. It has no file of its own — the builder is called in a controller or in a model method.

The first select

// app/Controllers/Posts.php
public function titles()
{
    $db = db_connect();
 
    $rows = $db->table('posts')
        ->select('id, title')
        ->get()
        ->getResultArray();
 
    return $this->response->setJSON($rows);
}

table() starts a query to a table, select() lists the columns, get() runs the query and getResultArray() returns the rows as arrays. With the route $routes->get('titles', 'Posts::titles'); the address /titles shows a list of identifiers and titles as JSON.

Conditions and sorting

$rows = $db->table('posts')
    ->where('status', 1)
    ->like('title', 'php')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get()
    ->getResultArray();

The query returns the ten latest published posts whose title contains "php". Several where() calls are joined with AND.

Joining tables

Suppose there is a comments table with a post_id column. One query counts the comments of each post:

$rows = $db->table('posts')
    ->select('posts.id, posts.title, COUNT(comments.id) AS total')
    ->join('comments', 'comments.post_id = posts.id', 'left')
    ->groupBy('posts.id')
    ->get()
    ->getResultArray();

The third argument of join() is the join type: with left posts without comments are included too.

The same methods on a model

A model is built on the builder, so its methods are called directly on the model and finished with the familiar findAll():

$posts = model(PostModel::class)
    ->where('status', 1)
    ->orderBy('created_at', 'DESC')
    ->findAll(10);

For queries to one table this way is shorter.

Insert, update, delete

$db->table('posts')->insert(['title' => 'Post', 'body' => 'Text']);
 
$db->table('posts')->where('id', 5)->update(['status' => 1]);
 
$db->table('posts')->where('status', 0)->delete();

Unlike a model, the builder does not check $allowedFields and does not fill in dates.

An SQL query with parameters

$rows = $db->query('SELECT * FROM posts WHERE status = ? AND id > ?', [1, 10])
    ->getResultArray();

The values are passed as the second argument and inserted in place of the question marks already escaped.

Common mistake

A value glued into the query text — $db->query("SELECT * FROM posts WHERE title = '$title'") — opens the way to SQL injection: a visitor can append their own code to the query. Use the builder methods or question marks with an array of values.