Extended Query Builder Usage

Spiral Database provides query builders for Insert, Update, Delete and Select operations, and multiple shortcuts for common functionality.

Before we start

Make sure to get access to the desired database using the DatabaseManager or via the entity source:

$db = $dbal->database('default');

$db = $orm->getSource(User::class)->getDatabase();

Insert Builder

To get an instance of InsertBuilder (responsible for insertions), we have to execute following code:

$insert = $database->insert('test');

Now we can add some values to our builder to be inserted into the related table:

$insert = $database->insert('test');

$insert->values([
    'time_created' => new \DateTime(),
    'name'         => 'Anton',
    'email'        => 'test@email.com',
    'balance'      => 800.90
]);

To run the InsertQuery we can execute the method run(), which will return last inserted id as result:

dump($insert->run());

You can also use fluent syntax: $database->insert('table')->values(...)->run().

Batch Insert

You can add as many values into insert builder as your database supports:

$insert->columns(['time_created', 'name', 'email', 'balance']);
for ($i = 0; $i < 20; $i++) {
    //We don't need to specify key names in this case
    $insert->values([
        new \DateTime(),
        $this->faker->randomNumber(2),
        $this->faker->email,
        $this->faker->randomFloat(2)
    ]);
}

$insert->run();

Quick Inserts

You can skip InsertQuery creation by talking to your table directly:

$table = $database->table('test');

dump($table->insertOne([
    'time_created' => new \DateTime(),
    'name'         => 'Anton',
    'email'        => 'test@email.com',
    'balance'      => 800.90
]));

Table class will automatically run a query and return the last inserted id. You can also check the insertMultiple method of Table.

SelectQuery Builder

SelectQuery builder can be retrieved in two very similar ways. You can either get it from the database instance or from the table instance:

protected function indexAction(Database $database)
{
    $select = $database->table('test')->select();
    $select = $database->select()->from('test');

    //Alternative
    $select = $database->test->select();
}

Select Columns

By default, SelectQuery selects every column (*) from its related table. We can always change the set of requested columns using the columns method.

$database->users->select()->columns('name')->fetchAll();

You can use your select query as proper iterator or use the run method, which will return an instance of PDOStatement:

foreach($select->getIterator() as $row) {
    dump($row);
}

To select all values as an array use fetchAll:

foreach($select->fetchAll() as $row) {
    dump($row);
}

You can always view the SQL that is being generated by your query by dumping it, or via the method sqlStatement:

dump($database->users->select()->columns('name')->sqlStatement());

Where Statements

Add WHERE conditions to your query using the where, andWhere, orWhere methods.

Basics

Let's add a simple condition on the status column of our table:

protected function indexAction(Database $database)
{
    $select = $database->select()->from('test')->columns(['id', 'status', 'name']);

    $select->where('status', '=', 'active');

    foreach ($select as $row) {
        dump($row);
    }
}
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE `status` = 'active'

Note, that prepared statement will be used behind the scenes.

You can skip '=' in your conditions:

$select->where('status', 'active');
Where Operators

Second argument can be used to declare operator:

$select->where('id', '>', 10);
$select->where('status', 'like', 'active');

For between and not between conditions you can also use forth argument of where method:

$select->where('id', 'between', 10, 20);

Resulted SQL:

SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE `id` BETWEEN 10 AND 20
Multiple Where Conditions

Chain multiple where conditions using fluent calls:

$select->where('id', 1)->where('status', 'active');

Method andWhere is an alias for where, so we can rewrite such condition to make it more readable:

$select->where('id', 1)->andWhere('status', 'active');

Resulted SQL:

SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE `id` = 1 AND `status` = 'active'

SelectQuery will generate SQL based respecting your operator order and boolean operators:

$select->where('id', 1)->orWhere('id', 2)->orWhere('status', 'active');
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE `id` = 1 OR `id` = 2 OR `status` = 'active'
Complex/Group Where Conditions

Group multiple where conditions using Closure as your first argument:

$select->where('id', 1)->where(function (SelectQuery $select) {
    $select->where('status', 'active')->orWhere('id', 10);
});
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE `id` = 1 AND (`status` = 'active' OR `id` = 10)

Boolean joiners are respected:

$select->where('id', 1)->orWhere(function (SelectQuery $select) {
    $select->where('status', 'active')->andWhere('id', 10);
});

Result:

SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE `id` = 1 OR (`status` = 'active' AND `id` = 10)

You can nest as many conditions as you want.

Simplified/array Where Conditions

Alternatively you can use MongoDB style to build your where conditions:

$select->where([
    'id'     => 1,
    'status' => 'active'
]);

Such code is identical to two where method calls and generates such sql:

SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE (`id` = 1 AND `status` = 'active')

You can also specify custom comparison operators using nested arrays:

$select->where([
    'id'     => ['in' => new Parameter([1, 2, 3])],
    'status' => ['like' => 'active']
]);

Attention, you have to wrap all array arguments using Parameter class, scalar arguments will be wrapped automatically.

Resulted SQL:

SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE (`id` IN (1, 2, 3) AND `status` LIKE 'active')

Multiple conditions per field are supported:

$select->where([
    'id' => [
        'in' => [1, 2, 3],
        '<'  => 100
    ]
]);

Use @or and @and groups to create where groups:

$select->where(function (SelectQuery $select) {
    $select->where('id', 'between', 10, 100)->andWhere('name', 'Anton');
})->orWhere('status', 'disabled');

Using short syntax:

$select->where([
    '@or' => [
        [
            'id'   => ['between' => [10, 100]],
            'name' => 'Anton'
        ],
        ['status' => 'disabled']
    ]
]);

In both cases resulted SQL will look like:

SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE ((`id` BETWEEN 10 AND 100 AND `name` = 'Anton') OR `status` = 'disabled')

You can experiment with both ways to declare where conditions and pick the one you like more.

Parameters

Spiral mocks all given values using Parameter class internally, in some cases (array) you might need to pass Parameter directly. You can alter parameter value at any moment, but before query run method:

$select = $database->select()->from('test')->columns(['id', 'status', 'name']);

$select->where('id', $id = new Parameter(null));

//Bing new parameter value
$id->setValue(15);

foreach ($select as $row) {
    dump($row);
}

You can also pass requested PDO parameter type as second argument: new Parameter(1, PDO::PARAM_INT). Internally every value passed into where the method is going to be wrapped using Parameter class.

You can implement ParameterInterface if you want to declare your parameter wrappers with custom logic.

SQL Fragments and Expressions

QueryBuilders allow you to replace some of where statements with custom SQL code or expression. Use Spiral\Database\Injection\Fragment and Spiral\Database\Injection\Expression for such purposes.

Use fragment to include SQL code into your query bypassing escaping:

//255
$select->where('id', '=', new Fragment("DAYOFYEAR('2015-09-12')"));
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE `id` = DAYOFYEAR('2015-09-12')

If you wish to compare complex value to user parameter replace where column with expression:

$select->where(new Expression("DAYOFYEAR(concat('2015-09-', id))"), 255);
SELECT
*
FROM `x_users`
WHERE DAYOFYEAR(concat('2015-09-', `id`)) = 255

Note that all column identifiers in Expressions will be quoted.

Join multiple columns same way:

$select->where(new Expression("CONCAT(id, '-', status)"), '1-active');
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE CONCAT(`id`, '-', `status`) = '1-active'

Expressions are extremely useful when you Database have non empty prefix:

$select->where(new Expression("CONCAT(test.id, '-', test.status)"), '1-active');
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE CONCAT(`primary_test`.`id`, '-', `primary_test`.`status`) = '1-active'

You can also use expressions and fragments as column values in the insert and update statements.

Please keep client data as far from Expressions and Fragments as far as it is possible.

Table and Column aliases

QueryBuilders support user defined table and column aliases:

$select = $database->select()->from('test as abc')->columns([
    'id',
    'status',
    'name'
]);

$select->where('abc.id', '>', 10);

foreach ($select as $row) {
    dump($row);
}
SELECT
`id`, `status`, `name`
FROM `primary_test` as `abc`
WHERE `abc`.`id` > 10

Columns:

$select = $database->select()->from('test')->columns([
    'id',
    'status as st',
    'name',
    "CONCAT(test.name, ' ', test.status) as name_and_status"
]);

foreach ($select as $row) {
    dump($row);
}

SQL:

SELECT
`id`, `status` as `st`, `name`, CONCAT(`primary_test`.`name`, ' ',`primary_test`.`status`) as `name_and_status`
FROM `primary_test`
Sub/Nested Queries

Every spiral QueryBuilder is as instance of FragmentInterface, this makes you able to create complex nested queries when you need them:

$select = $database->select()->from('test')->columns(['id', 'status', 'name']);

$select->where(
    'id',
    'IN',
    $database->select('id')->from('test')->where('id', 'BETWEEN', 10, 100)
);

foreach ($select as $row) {
    dump($row);
}
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE `id` IN  (SELECT
`id`
FROM `primary_test`
WHERE `id` BETWEEN 10 AND 100)

You can compare nested query return value in where statements:

$select->where(
    $database->select('COUNT(*)')->from('test')->where('id', 'BETWEEN', 10, 100), '>', 1
);
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE (SELECT
COUNT(*)
FROM `primary_test`
WHERE `id` BETWEEN 10 AND 100) > 1

You can exchange column identifiers between parent and nested query using Expression class:

$select = $database->select()->from('test')->columns(['id', 'status', 'name']);

$select->where(
    $database->select('name')->from('users')->where(
        'id', '=', new Expression('test.id')
    )->where('id', '!=', 100),
    'Anton'
);
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE (SELECT
`name`
FROM `primary_users`
WHERE `id` = `primary_test`.`id` AND `id` != 100) = 'Anton'

Nested queries will only work when nested query belongs to the same database as a primary builder.

Having

Use methods having, orHaving and andHaving methods to define HAVING conditions. rSyntax is identical to WHERE statement.

Yep, it was quick.

Joins

You can join any desired table to your query using leftJoin, join, rightJoin, fullJoin and innerJoin methods:

$select = $database->table('test')->select(['test.*', 'u.name as u']);

$select->leftJoin('users', 'u')->on('users.id', 'test.id');
 SELECT
`x_test`.*, `u`.`name` AS `u`
FROM `x_test`
LEFT JOIN `x_users` AS `u`
    ON `x_users`.`id` = `x_test`.`id`

Method on works exactly as where except provided values treated as identifier and not as user value. Chain on, andOn and orOn methods to create more complex joins:

$select->leftJoin('users')->on('users.id', 'test.id')->orOn('users.id', 'test.balance');

Array based where conditions is also supported:

$select->leftJoin('users', 'u')->on([
    '@or' => [
        ['u.id' => 'test.id'],
        ['u.id' => 'test.balance']
    ]
]);

Generated SQL:

SELECT
`primary_test`.*, `primary_users`.`name` as `user_name`
FROM `primary_test`
LEFT JOIN `primary_users`
    ON (`primary_users`.`id` = `primary_test`.`id` OR `primary_users`.`id` = `primary_test`.`balance`)
On Where statement

To include user value into ON statement, use methods onWhere, orOnWhere and andOnWhere:

$select->innerJoin('users')->on(['users.id' => 'test.id'])->onWhere('users.name', 'Anton');
SELECT
`primary_test`.*, `primary_users`.`name` as `user_name`
FROM `primary_test`
INNER JOIN `primary_users`
    ON `primary_users`.`id` = `primary_test`.`id` AND `primary_users`.`name` = 'Anton'
Aliases

Second parameter in join methods are dedicated to table alias, feel free to use it in on and where statements of your query:

$select = $database->table('test')->select(['test.*', 'uu.name as user_name']);
$select->innerJoin('users', 'uu')->onWhere('uu.name', 'Anton');

Alternatively:

$select = $database->table('test')->select(['test.*', 'uu.name as user_name']);
$select->innerJoin('users as uu')->onWhere('uu.name', 'Anton');
SELECT
`primary_test`.*, `uu`.`name` as `user_name`
FROM `primary_test`
INNER JOIN `primary_users` as `uu`
    ON `uu`.`id` = `primary_test`.`id` AND `uu`.`name` = 'Anton'

OrderBy

User orderBy to specify sort direction:

//We have a join, so table name is mandratory
$select->orderBy('test.id', SelectQuery::SORT_DESC);

Multiple orderBy calls are allowed:

$select->orderBy(
    'test.name', SelectQuery::SORT_DESC
)->orderBy(
    'test.id', SelectQuery::SORT_ASC
);

Alternatively:

$select->orderBy([
    'test.name' => SelectQuery::SORT_DESC,
    'test.id'   => SelectQuery::SORT_ASC
]);

Both ways will produce such SQL:

SELECT
`primary_test`.*, `uu`.`name` as `user_name`
FROM `primary_test`
INNER JOIN `primary_users` as `uu`
    ON `uu`.`id` = `primary_test`.`id` AND `uu`.`name` = 'Anton'
ORDER BY `primary_test`.`name` DESC, `primary_test`.`id` ASC

You can also use Fragments instead of sorting identifiers (by default identifiers are treated as column name or expression).

GroupBy and Distinct

If you wish to select unique results from your selection use method distinct (always use distinct while loading HAS_MANY/MANY_TO_MANY relations in ORM).

$select->distinct();

Result grouping is available using groupBy method:

$select = $database->table('test')->select(['status', 'count(*) as count'])->groupBy('status');

As you might expect produced SQL looks like:

SELECT
`status`, count(*) as `count`
FROM `primary_test`
GROUP BY `status`

Aggregations and Count

Since you can manipulate with selected columns including COUNT and other aggregation functions into your query might look like:

$select = $database->table('test')->select(['COUNT(*)']);

Though, in many cases you want to get query count or summarize results without column manipulations, use count, avg, sum, max and min methods to do that:

$select = $database->table('test')->select(['id', 'name', 'status']);

dump($select->count());
dump($select->sum('balance'));
SELECT
COUNT(*)
FROM `primary_test`;

SELECT
SUM(`balance`)
FROM `primary_test`;

UpdateQuery Builder

Use "update" method of your table or database instance to get access to UpdateQuery builder, call run method of such query to execute result:

$update = $database->table('test')->update([
    'name' => 'Abc'
]);

$update->where('id', '<', 10)->run();
UPDATE `primary_test`
SET `name` = 'Abc'
WHERE `id` < 10

You can use Expression and Fragment instances in your update values:

$update = $database->table('test')->update([
    'name' => new Expression('UPPER(test.name)')
]);

$update->where('id', '<', 10)->run();
UPDATE `primary_test`
SET `name` = UPPER(`primary_test`.`name`)
WHERE `id` < 10

Nested queries are also supported:

$update = $database->table('test')->update([
    'name' => $database->table('users')->select('name')->where('id', 1)
]);

$update->where('id', '<', 10)->run();
UPDATE `primary_test`
SET `name` = (SELECT
`name`
FROM `primary_users`
WHERE `id` = 1)
WHERE `id` < 10

Where methods work identically as in SelectQuery.

DeleteQuery Builders

Delete queries are represent by DeleteQuery accessible via "delete" method:

$database->table('test')->delete()->where('id', '<', 1000)->run();

You can also specify where statement in Table delete method in a form of where array:

$database->table('test')->delete([
    'id' => ['>' => 1000]
])->run();
Edit this page