The Sql query builder

A plain select(['id' => 5]) is not always enough: you need ordering, a search by part of a word, data from two tables. For that laminas-db has a query builder — objects from which a query is assembled with method calls, while the package writes the final SQL. No separate files are needed: the builder is used inside the table class.

Ordering and limiting

The table gateway hands out a query stub for its own table. Add a method to module/Blog/src/Model/PostTable.php:

// module/Blog/src/Model/PostTable.php
public function fetchLatest(int $limit = 10): array
{
    $select = $this->gateway->getSql()->select();
    $select->order('id DESC')->limit($limit);
 
    return iterator_to_array($this->gateway->selectWith($select));
}

getSql()->select() creates a Select object for the posts table, and selectWith() runs it. Calling $this->posts->fetchLatest(5) in the controller returns the five newest posts as Post objects.

Conditions

Simple conditions are passed as an array, more complex ones through methods of the where object:

public function search(string $text): array
{
    $select = $this->gateway->getSql()->select();
    $select->where->like('title', '%' . $text . '%');
    $select->order('title ASC');
 
    return iterator_to_array($this->gateway->selectWith($select));
}

The method finds posts whose title contains the text. The value goes to the database as a parameter instead of being pasted into the query.

Several calls in a row are joined with AND.

Joining tables

For a query across several tables the Sql object created from the adapter is more convenient. Suppose there is a users table and posts has a user_id column:

use Laminas\Db\Sql\Sql;
 
$sql = new Sql($adapter);
 
$select = $sql->select('posts')
    ->columns(['id', 'title'])
    ->join('users', 'users.id = posts.user_id', ['author' => 'name'])
    ->where(['users.id' => 1]);
 
$rows = $sql->prepareStatementForSqlObject($select)->execute();
 
foreach ($rows as $row) {
    echo $row['title'] . ' — ' . $row['author'];
}

The third argument of join() lists the columns of the second table; ['author' => 'name'] means "the name column under the name author". Each result row is a plain array.

Counting

SQL expressions such as COUNT(*) are wrapped in Expression:

use Laminas\Db\Sql\Expression;
 
$select = $sql->select('posts')->columns(['total' => new Expression('COUNT(*)')]);
 
$row = $sql->prepareStatementForSqlObject($select)->execute()->current();
 
$total = (int) $row['total'];

$total holds the number of posts.

Transactions

A transaction groups several queries: either all of them run, or none.

$connection = $adapter->getDriver()->getConnection();
$connection->beginTransaction();
 
try {
    $this->posts->save($post);
    $this->tags->saveFor($post, $tags);
 
    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollback();
 
    throw $e;
}

If saving the tags fails, the post does not stay in the database either.

Common mistake

A string condition with a pasted value — $select->where("title = '$title'") — opens the door to SQL injection: a visitor can append their own code to the query. Pass conditions as an array ['title' => $title] or through where->equalTo(), where->like() — then the value travels to the database separately from the query.