You can read how to work with Database using manually written queries here.
DBAL component includes a set of query builders used to unify the way of working with different databases and simplify migration to different DBMS over the lifetime of the application.
To demonstrate query building abilities let's declare sample table in our default database first:
use Cycle\Database\Database;
$schema = $dbal->database()->table('test')->getSchema();
$schema->primary('id');
$schema->datetime('time_created');
$schema->enum('status', ['active', 'disabled'])->defaultValue('active');
$schema->string('name', 64);
$schema->string('email');
$schema->double('balance');
$schema->save();
You can read more about declaring database schemas here.
To get an instance of InsertBuilder (responsible for insertions), we have to execute following code:
$insert = $db->insert('test');
Now we can add some values to our builder to be inserted into related table:
$insert = $db->insert('test');
$insert->values([
'time_created' => new \DateTime(),
'name' => 'Anton',
'email' => 'test@email.com',
'balance' => 800.90
]);
To run InsertQuery we should only execute method run()
which will return last inserted id as result:
var_dump($db->run());
You can also use fluent syntax:
$database->insert('table')->values(...)->run()
.
You add as many values into insert builder as your database can support:
$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();
You can skip InsertQuery creation by talking to your table directly:
$table = $db->table('test');
var_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 can be retrieved two very similar ways, you can either get it from database or from table instances:
$select = $db->table('test')->select();
// alternative
$select = $db->select()->from('test');
// alternative
$select = $db->test->select();
By default, SelectQuery selects every column (*
) from its related table. We can always change the set of requested columns using the columns
method.
$db->users->select()
->columns('name')
->fetchAll();
You can use your select query as proper iterator or use run
method which will return instance of Cycle\Database\Statement
:
foreach($select->getIterator() as $row) {
var_dump($row);
}
To select all values as array use fetchAll
:
foreach($select->fetchAll() as $row) {
var_dump($row);
}
You can always view what SQL is generated by your query by dumping it or via method sqlStatement
:
var_dump(
$db->users->select()
->columns('name')
->sqlStatement()
);
Add WHERE conditions to your query using where
, andWhere
, orWhere
methods.
Let's add simple condition on status
column of our table:
$select = $db->select()
->from('test')
->columns(['id', 'status', 'name']);
$select->where('status', '=', 'active');
foreach ($select as $row) {
var_dump($row);
}
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE `status` = 'active'
Note that prepared statements used behind the scenes.
You can skip '=' in your conditions:
$select->where('status', 'active');
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
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'
Group multiple where conditions using Closure as your first argument:
$select
->where('id', 1)
->where(
static 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(
static 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.
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(
static 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.
Spiral mocks all given values using Parameter
class internally, in some cases (array) you might need to pass Parameter
directly. You can alter the parameter value at any moment, but before the query run
method:
use Cycle\Database\Injection\Parameter;
// ...
$select = $db->select()
->from('test')
->columns(['id', 'status', 'name']);
$select->where('id', $id = new Parameter(null));
//Bind 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 thewhere
method is going to be wrapped using the Parameter class.
You can implement ParameterInterface if you want to declare your parameter wrappers with custom logic.
QueryBuilders allow you to replace some of where statements with custom SQL code or expression. Use Cycle\Database\Injections\Fragment
and Cycle\Database\Injections\Expression
for such purposes.
Use fragment to include SQL code into your query bypassing escaping:
use Cycle\Database\Injection\Fragment;
//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 the column with the expression:
use Cycle\Database\Injection\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 \Cycle\Database\Injection\Expression("CONCAT(id, '-', status)"), '1-active');
SELECT
`id`, `status`, `name`
FROM `primary_test`
WHERE CONCAT(`id`, '-', `status`) = '1-active'
Expressions are handy when your Database has a non-empty prefix:
$select->where(
new \Cycle\Database\Injection\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 possible.
QueryBuilders support user defined table and column aliases:
$select = $db->select()
->from('test as abc')
->columns([
'id',
'status',
'name'
]);
$select->where('abc.id', '>', 10);
foreach ($select as $row) {
var_dump($row);
}
SELECT
`id`, `status`, `name`
FROM `primary_test` as `abc`
WHERE `abc`.`id` > 10
Columns:
$select = $db->select()
->from('test')
->columns([
'id',
'status as st',
'name',
"CONCAT(test.name, ' ', test.status) as name_and_status"
]);
foreach ($select as $row) {
var_dump($row);
}
SQL:
SELECT
`id`,
`status` as `st`,
`name`,
CONCAT(`primary_test`.`name`, ' ',`primary_test`.`status`) as `name_and_status`
FROM `primary_test`
Every spiral QueryBuilder is as instance of FragmentInterface
, this makes you able to create complex nested queries when you need them:
$select = $db->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) {
var_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(
$db->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 = $db->select()
->from('test')
->columns(['id', 'status', 'name']);
$select->where(
$db->select('name')
->from('users')
->where('id', '=', new \Cycle\Database\Injection\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 the nested query belongs to the same database as a primary builder.
Use methods having
, orHaving
, and andHaving
methods to define HAVING conditions. The syntax is identical to the WHERE statement.
Yep, it was quick.
You can join any desired table to your query using leftJoin
, join
, rightJoin
, fullJoin
and innerJoin
methods:
$select = $db->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 are 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`)
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'
You may also specify more advanced join statements. To get started, pass a closure or array as the fourth argument to the join method.
Example 1
$select->join('LEFT', 'photos', 'pht', ['pht.user_id', 'users.id']);
SELECT *
FROM `users`
LEFT JOIN `photos` AS `pht`
ON `pht`.`user_id` = `users`.`id`
Example 2
You may use grouped statements.
$select->join('LEFT', 'photos', 'pht', [
'@or' => [
['pht.user_id' => 'users.id'],
['users.is_admin' => new Parameter(true)]
],
]);
SELECT *
FROM `users`
LEFT JOIN `photos` AS `pht`
ON (
`pht`.`user_id` = `users`.`id`
OR
`users`.`is_admin` = true
)
Example 3
You may use grouped statements with sub grouped statements.
$select->join('LEFT', 'photos', 'pht', [
[
'@or' => [
[
'pht.user_id' => 'users.id',
'users.is_admin' => new Parameter(true),
],
[
'@or' => [
['pht.user_id' => 'users.parent_id'],
['users.is_admin' => new Parameter(false)],
],
],
],
],
]);
SELECT *
FROM `users`
LEFT JOIN `photos` AS `pht`
ON (
(`pht`.`user_id` = `users`.`id` AND `users`.`is_admin` = true)
OR
(`pht`.`user_id` = `users`.`parent_id` OR `users`.`is_admin` = false)
)
Example 4
You may combine simple statements with grouped statements.
$select->join('LEFT', 'photos', 'pht', [
'pht.user_id' => 'admins.id',
'users.is_admin' => 'pht.is_admin',
'@or' => [
[
'users.name' => new Parameter('Anton'),
'users.is_admin' => 'pht.is_admin',
],
[
'users.status' => new Parameter('disabled'),
],
],
]);
SELECT *
FROM `users`
LEFT JOIN `photos` AS `pht`
ON (
`pht`.`user_id` = `users`.`id`
AND
`users`.`is_admin` = true
AND (
(`users`.`name` = "Anton" AND `users`.`is_admin` = `pht`.`is_admin`)
OR
`users`.`status` = "disabled"
)
)
Example 5
You may use closure as a fourth argument.
$select->join('LEFT', 'photos', 'pht', static function (
\Cycle\Database\Query\SelectQuery $select,
string $boolean,
callable $wrapper
): void {
$select
->on('photos.user_id', 'users.id')
->onWhere('photos.type', 'avatar');
});
SELECT *
FROM `users`
LEFT JOIN `photos` AS `pht`
ON (
`photos`.`user_id` = `users`.`id`
AND
`photos`.`type` = "avatar"
)
Example 6
You may use closure statement inside array.
$select->join('LEFT', 'photos', 'pht', [
'pht.user_id' => 'users.id',
'users.is_admin' => 'pht.is_admin',
static function (
\Cycle\Database\Query\SelectQuery $select,
string $boolean,
callable $wrapper
): void {
$select
->on('photos.user_id', 'users.id')
->onWhere('photos.type', 'avatar');
}
]);
SELECT *
FROM `users`
LEFT JOIN `photos` AS `pht`
ON (
`pht`.`user_id` = `users`.`id`
AND
`users`.`is_admin` = `pht`.`is_admin`
AND
(`photos`.`user_id` = `users`.`id` AND `photos`.`type` = "avatar")
)
Second parameter in join methods are dedicated to table alias, feel free to use it in on
and where
statements of your query:
$select = $db->table('test')
->select(['test.*', 'uu.name as user_name'])
->innerJoin('users', 'uu')
->onWhere('uu.name', 'Anton');
Alternatively:
$select = $db->table('test')
->select(['test.*', 'uu.name as user_name'])
->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'
User orderBy
to specify sort direction:
//We have a join, so table name is mandatory
$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).
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 = $db->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`
Since you can manipulate with selected columns including COUNT and other aggregation functions into your query might look like:
$select = $db->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 = $db->table('test')
->select(['id', 'name', 'status']);
var_dump($select->count());
var_dump($select->sum('balance'));
SELECT
COUNT(*)
FROM `primary_test`;
SELECT
SUM(`balance`)
FROM `primary_test`;
You can paginate your query using methods limit
and offset
:
$select = $db->table('test')
->select(['id', 'name', 'status'])
->limit(10)
->offset(1);
foreach ($select as $row) {
var_dump($row);
}
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 = $db->table('test')
->update(['name' => 'Abc'])
->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 = $db->table('test')
->update([
'name' => new \Cycle\Database\Injection\Expression('UPPER(test.name)')
])
->where('id', '<', 10)
->run();
UPDATE `primary_test`
SET `name` = UPPER(`primary_test`.`name`)
WHERE `id` < 10
Nested queries are also supported:
$update = $db->table('test')
->update([
'name' => $database
->table('users')
->select('name')
->where('id', 1)
])
->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.
Delete queries are represent by DeleteQuery accessible via "delete" method:
$db->table('test')
->delete()
->where('id', '<', 1000)
->run();
You can also specify where statement in Table delete
method in a form of where array:
$db->table('test')
->delete([
'id' => ['>' => 1000]
])
->run();
You can use Expression object to create complex, driver-specific, SQL injections with included parameters.
$db->table('test')
->select()
->where(new \Cycle\Database\Injection\Expression('SUM(column) = ?', $value))
->run();