Skip to content

Query Builder Integration

Jump to bottom
Muhammet Şafak edited this page May 24, 2026 · 1 revision

Query Builder Integration

A Model is a thin wrapper around a DatabaseInterface. Every call you make to the model first checks for a method on the model itself; anything else flows downward through __call to the Database and then to the underlying query builder.

The net effect: every method on DatabaseInterface and on QueryBuilderInterface is callable directly on a model, and fluent chains stay rooted in the model.

A full chain

$posts = new Posts();

$rows = $posts
    ->select('id', 'title')              // QueryBuilder
    ->where('status', '=', 'published')  // QueryBuilder
    ->andWhere('author_id', '=', 7)       // QueryBuilder
    ->orderBy('id', 'DESC')               // QueryBuilder
    ->limit(10)                           // QueryBuilder
    ->offset(0)                           // QueryBuilder
    ->read()                              // Model — terminates the chain
    ->rows();                             // DataMapper

Every builder call is forwarded through Database::__call and re-wrapped to return the model, so the chain reads naturally from top to bottom.

The forwarded surface

The query builder ships ~100 fluent methods. Below is a cheat-sheet of the most useful families — every signature is the same as on InitORM\Database\Facade\DB, which mirrors the full builder interface.

Projection

$model->select('id', 'name');                                  // SELECT id, name
$model->clearSelect();                                         // wipe a prior select chain
$model->selectAs('user.name', 'author_name');                  // user.name AS author_name
$model->selectDistinct('email');                               // SELECT DISTINCT email
$model->selectCount('id', 'total');                            // COUNT(id) AS total
$model->selectCountDistinct('email');                          // COUNT(DISTINCT email)
$model->selectMax('score');         $model->selectMin('score');
$model->selectAvg('score');         $model->selectSum('score');
$model->selectUpper('name');        $model->selectLower('name');
$model->selectLength('body');
$model->selectMid('body', 1, 100);  $model->selectLeft('body', 50);  $model->selectRight('body', 50);
$model->selectCoalesce('nickname', 'unknown', 'display');
$model->selectConcat(['first', 'last'], 'fullname');

Tables and joins

$model->from('users');                              // FROM users
$model->from('users', 'u');                         // FROM users AS u
$model->addFrom('posts', 'p');                      // , posts AS p
$model->table('users');                             // alias of from()

$model->join('posts', 'posts.user_id = users.id', 'INNER');
$model->innerJoin('posts',  'posts.user_id = users.id');
$model->leftJoin('posts',   'posts.user_id = users.id');
$model->rightJoin('posts',  'posts.user_id = users.id');
$model->leftOuterJoin('posts',  'posts.user_id = users.id');
$model->rightOuterJoin('posts', 'posts.user_id = users.id');
$model->selfJoin('posts',   'posts.user_id = users.id');
$model->naturalJoin('posts');

WHERE

$model->where('status', '=', 'published');
$model->andWhere('author_id', '=', 7);
$model->orWhere('featured', '=', 1);

$model->whereIn('id', [1, 2, 3]);
$model->whereNotIn('id', [4, 5]);
$model->orWhereIn(...);  $model->andWhereIn(...);
$model->orWhereNotIn(...);  $model->andWhereNotIn(...);

$model->whereIsNull('deleted_at');
$model->whereIsNotNull('published_at');
$model->orWhereIsNull(...);    $model->andWhereIsNull(...);
$model->orWhereIsNotNull(...); $model->andWhereIsNotNull(...);

$model->between('score', 0, 100);
$model->notBetween('score', 0, 100);
$model->orBetween(...);     $model->andBetween(...);
$model->orNotBetween(...);  $model->andNotBetween(...);

$model->like('title', 'hello');               // LIKE %hello%
$model->startLike('title', 'hello');          // LIKE hello%
$model->endLike('title', 'hello');            // LIKE %hello
$model->notLike(...);
$model->orLike(...);       $model->andLike(...);
$model->orNotLike(...);    $model->andNotLike(...);

$model->regexp('username', '^[a-z]+$');
$model->soundex('name', 'Smith');
$model->findInSet('tags', 'php');
$model->notFindInSet('tags', 'spam');

Grouping, sorting, paging

$model->groupBy('status');
$model->groupBy('status', 'author_id');
$model->having('count', '>', 10);

$model->orderBy('id', 'DESC');
$model->orderBy('created_at', 'ASC');

$model->limit(10);
$model->offset(20);

Sub-queries and grouped predicates

// Sub-query as a value:
$model->whereIn('author_id', $model->subQuery(function ($sub) {
    $sub->select('id')->from('users')->where('active', '=', 1);
}));

// Grouped WHERE:
$model->where('status', '=', 'published')
      ->group(function ($g) {
          $g->where('author_id', '=', 7)
            ->orWhere('featured', '=', 1);
      });
// WHERE status = :status AND (author_id = :author_id OR featured = :featured)

Raw SQL escape hatch

use InitORM\QueryBuilder\RawQuery;

$model->select($model->raw("CONCAT(name, ' ', surname) AS fullname"))
      ->where($model->raw('status = 1 OR status = 0'))
      ->read()
      ->rows();

Never embed unsanitised user input into a raw fragment. raw() opts out of every safety net the builder provides.

Mixing builder calls and CRUD

Builder methods accumulate state on the underlying query builder. CRUD calls (read, update, delete, etc.) consume that state and then reset it — so the next CRUD call starts clean:

$posts->where('id', '=', 5)->update(['title' => 'X']);     // uses the WHERE
$posts->update(['title' => 'Y'], ['id' => 6]);             // clean slate; explicit conditions

If you need two independent queries against the same connection (e.g. a parent query and a sub-query that are not chained), spin off a fresh builder via the Database:

$reports = $posts->getDatabase()->withFreshBuilder();
$reports->read('events')->rows();
// $posts is unaffected.

Worked example: feed query

A "latest 10 published posts from active authors, with the author's name and post count" query:

$rows = $posts
    ->select('p.id', 'p.title', 'p.created_at')
    ->selectAs('u.name', 'author_name')
    ->from('posts', 'p')
    ->innerJoin('users AS u', 'p.author_id = u.id')
    ->where('p.status', '=', 'published')
    ->andWhere('u.active', '=', 1)
    ->orderBy('p.created_at', 'DESC')
    ->limit(10)
    ->read()
    ->rows();

Notice that read() did not need a $selectors or $conditions argument — the builder chain already configured everything. read() just compiles, executes, and resets.

Building entities yourself

The model hydrates read() into $entity instances. To opt out of that and get raw data:

$rawRows = $posts->read()->asAssoc()->rows();           // array<string, mixed>[]
$rawObjs = $posts->read()->asObject()->rows();           // stdClass[]
$rawIter = $posts->read()->asLazy();                     // PDOStatement-backed iterator

asAssoc() / asObject() / asLazy() are methods on the DataMapper and override the fetch mode set by the model.

When forwarding fails

Calling a method that doesn't exist anywhere in the chain raises BadMethodCallException:

try {
    $posts->thisIsNotARealMethod();
} catch (\BadMethodCallException $e) {
    echo $e->getMessage();              // 'Method "App\Model\Posts::thisIsNotARealMethod" does not exist.'
    echo $e->getPrevious()::class;      // 'InitORM\Database\Exceptions\DatabaseException'
}

The previous exception is the DatabaseException the underlying Database layer raised — useful for debugging when you mistype a builder method.

Read also

InitORM ORM · MIT · maintained by Muhammet ŞAFAK · part of the InitORM stack

InitORM ORM

Getting Started

Models

Entities

Cross-Cutting

Reference

Upgrading

Project

Clone this wiki locally