class Atk4\Data\Persistence\Sql\Query


Query class represents your SQL query in-the-making. Once you create object of the Query class, call some of the methods listed below to modify your query. To actually execute your query and start retrieving data, see fetching-result section.

You should use Connection if possible to create your query objects. All examples below are using $c->dsql() method which generates Query linked to your established database connection.

Once you have a query object you can execute modifier methods such as field() or table() which will change the way how your query will act.

Once the query is defined, you can either use it inside another query or expression or you can execute it in exchange for result set.

Quick Example:

$query = $c->dsql();

$query->where('id', 123);

$name = $query->getOne();

Method invocation principles

Methods of Query are designed to be flexible and concise. Most methods have a variable number of arguments and some arguments can be skipped:

$query->where('id', 123);
$query->where('id', '=', 123); // the same

Most methods will accept Expression or strings. Strings are escaped or quoted (depending on type of argument). By using Expression you can bypass the escaping.

There are 2 types of escaping:

In the next example $a is escaped but $b is parameterized:

$query->where('a', 'b');

// where `a` = "b"

If you want to switch places and execute where "b" = 'a', then you can resort to Expressions:

$query->where($c->expr('{} = []', ['b', 'a']));

Parameters which you specify into Expression will be preserved and linked into the $query properly.

Query Modes

When you create new Query it always start in “select” mode. You can switch query to a different mode using mode. f you don’t switch the mode, your Query remains in select mode and you can fetch results from it anytime.

The pattern of defining arguments for your Query and then executing allow you to re-use your query efficiently:

$data = ['name' => 'John', 'surname' => 'Smith']

$query = $c->dsql();
    ->where('id', 123)

$row = $query->getRow();

if ($row) {
        ->set('revision', $query->expr('revision + 1'))
} else {
        ->set('revision', 1)

The example above will perform a select query first:

  • select id from user where id = 123

If a single row can be retrieved, then the update will be performed:

  • update user set name = "John", surname = "Smith", revision = revision + 1 where id = 123

Otherwise an insert operation will be performed:

  • insert into user (name, surname, revision) values ("John", "Smith", 1)


Majority of methods return $this when called, which makes it pretty convenient for you to chain calls by using ->fx() multiple times as illustrated in last example.

You can also combine creation of the object with method chaining:

$age = $c->dsql()->table('user')->where('id', 123)->field('age')->getOne();

Using query as expression

You can use query as expression where applicable. The query will get a special treatment where it will be surrounded in parentheses. Here are few examples:

$q = $c->dsql()

$q2 = $c->dsql()


This query will perform select name from (select * from employee):

$q1 = $c->dsql()
    ->field('amount', null, 'debit');

$q2 = $c->dsql()
    ->field('amount', null, 'credit');

$u = $c->dsql('[] union []', [$q1, $q2]);

$q = $c->dsql()
    ->field('date, debit, credit')
    ->table($u, 'derivedTable');


This query will perform union between 2 table selects resulting in the following query:

select `date`, `debit`, `credit` from (
    (select `date`, `amount` `debit` from `sales`) union
    (select `date`, `amount` `credit` from `purchases`)
) `derivedTable`

Modifying Select Query

Setting Table

Atk4\Data\Persistence\Sql\Query::table($table, $alias)

Specify a table to be used in a query.

  • $table (mixed) – table such as “employees”

  • $alias (mixed) – alias of table



This method can be invoked using different combinations of arguments. Follow the principle of specifying the table first, and then optionally provide an alias. You can specify multiple tables at the same time by using comma or array (although you won’t be able to use the alias there). Using keys in your array will also specify the aliases.

Basic Examples:

    // SELECT * from `user`

$c->dsql()->table('user', 'u');
    // aliases table with "u"
    // SELECT * from `user` `u`

    // specify multiple tables. Don't forget to link them by using "where"
    // SELECT * from `user`, `salary`

$c->dsql()->table(['user', 'salary']);
    // identical to previous example
    // SELECT * from `user`, `salary`

$c->dsql()->table(['u' => 'user', 's' => 'salary']);
    // specify aliases for multiple tables
    // SELECT * from `user` `u`, `salary` `s`

Inside your query table names and aliases will always be surrounded by backticks. If you want to use a more complex expression, use Expression as table:

    $c->expr('(SELECT id FROM user UNION select id from document)'),
// SELECT * FROM (SELECT id FROM user UNION SELECT id FROM document) `tbl`

Finally, you can also specify a different query instead of table, by simply passing another Query object:

$subQuery = $c->dsql();
$subQuery->where('name', 'John');

$q = $c->dsql();
$q->table($subQuery, 'sub');

// SELECT `surname` FROM (SELECT * FROM `employee` WHERE `name` = :a) `sub`

Method can be executed several times on the same Query object.

Setting Fields

Atk4\Data\Persistence\Sql\Query::field($fields, $alias = null)

Adds additional field that you would like to query. If never called, will default to Query::$defaultField, which normally is *.

This method has several call options. $field can be array of fields and also can be an Expression or Query

  • $fields (string|array|object) – Specify list of fields to fetch

  • $alias (string) – Optionally specify alias of field in resulting query



Basic Examples:

$query = new Query();

    // SELECT `first_name` from `user`

$query->field('first_name, last_name');
    // SELECT `first_name`, `last_name` from `user`

    // SELECT `employee`.`first_name` from `user`

$query->field('first_name', 'name')
    // SELECT `first_name` `name` from `user`

$query->field(['name' => 'first_name'])
    // SELECT `first_name` `name` from `user`

$query->field(['name' => 'employee.first_name']);
    // SELECT `employee`.`first_name` `name` from `user`

If the first parameter of field() method contains non-alphanumeric character such as space or parenthesis, then field() will assume that you’re passing an expression:


$query->field('now()', 'time_now');

You may also pass array as first argument. In such case array keys will be used as aliases (if they are specified):

$query->field(['time_now' => 'now()', 'time_created']);
    // SELECT now() `time_now`, `time_created` ...

$query->field($query->dsql()->table('user')->field('max(age)'), 'max_age');
    // SELECT (SELECT max(age) from user) `max_age` ...

Method can be executed several times on the same Query object.

Setting where and having clauses

Atk4\Data\Persistence\Sql\Query::where($field, $operation, $value)

Adds WHERE condition to your query.

  • $field (mixed) – field such as “name”

  • $operation (mixed) – comparison operation such as “>” (optional)

  • $value (mixed) – value or expression



Atk4\Data\Persistence\Sql\Query::having($field, $operation, $value)

Adds HAVING condition to your query.

  • $field (mixed) – field such as “name”

  • $operation (mixed) – comparison operation such as “>” (optional)

  • $value (mixed) – value or expression



Both methods use identical call interface. They support one, two or three argument calls.

Pass string (field name), Expression or even Query as first argument.

Operator can be specified through a second parameter - $operation. If unspecified, will default to ‘=’.

Last argument is value. You can specify number, string, array, expression or even null (specifying null is not the same as omitting this argument). This argument will always be parameterized unless you pass expression. If you specify array, all elements will be parametrized individually.

Starting with the basic examples:

$q->where('id', 1);
$q->where('id', '=', 1); // same as above

$q->where('id', '>', 1);

$q->where('id', '=', null); // will render to "IS NULL" SQL
$q->where('id', null) // same as above

$q->where('now()', 1); // will not use backticks
$q->where($c->expr('now()'), 1); // same as above

$q->where('id', [1, 2]); // renders as id in (1, 2)

You may call where() multiple times, and conditions are always additive (uses AND). The easiest way to supply OR condition is to specify multiple conditions through array:

$q->where([['name', 'like', '%john%'], ['surname', 'like', '%john%']]);
    // .. WHERE `name` like '%john%' OR `surname` like '%john%'

You can also mix and match with expressions and strings:

$q->where([['name', 'like', '%john%'], 'surname is null']);
    // .. WHERE `name` like '%john%' AND `surname` is null

$q->where([['name', 'like', '%john%'], $q->expr('surname is null')]);
    // .. WHERE `name` like '%john%' AND surname is null

There is a more flexible way to use OR arguments:


Returns new Query object with method “where()”. When rendered all clauses are joined with “OR”.


Returns new Query object with method “where()”. When rendered all clauses are joined with “OR”.

Here is a sophisticated example:

$q = $c->dsql();

$q->where('deleted', 0);
        ->where('a', 1)
        ->where('b', 1)
                ->where('a', 2)
                ->where('b', 2)

The above code will result in the following query:

    deleted = 0 and
    (`a` = :a or `b` = :b or (`a` = :c and `b` = :d))

Technically orExpr() generates a yet another object that is composed and renders its calls to where() method:

        ->where('a', 1)
        ->where('b', 1)
    (`a` = :a or `b` = :b)

Grouping results by field


Group by functionality. Simply pass either field name as string or Expression object.

  • $field (mixed) – field such as “name”



The “group by” clause in SQL query accepts one or several fields. It can also accept expressions. You can call group() with one or several comma-separated fields as a parameter or you can specify them in array. Additionally you can mix that with Expression or Expressionable objects.

Few examples:


$q->group('gender, age');

$q->group(['gender', 'age']);



Method can be executed several times on the same Query object.

Concatenate group of values

Atk4\Data\Persistence\Sql\Query::groupConcat($field, $separator = ', ')

Quite often when you use group by in your queries you also would like to concatenate group of values.

  • $field (mixed) – Field name or object

  • $separator (string) – Optional separator to use. It’s comma by default

Different SQL engines have different syntax for doing this. In MySQL it’s group_concat(), in Oracle it’s listagg, but in PgSQL it’s string_agg. That’s why we have this method which will take care of this.

$q->groupConcat('phone', ';');

// group_concat('phone', ';')

If you need to add more parameters for this method, then you can extend this class and overwrite this simple method to support expressions like this, for example:

group_concat('phone' order by 'date' desc separator ';')

Joining with other tables

Atk4\Data\Persistence\Sql\Query::join($foreignTable, $masterField, $joinKind)

Join results with additional table using “JOIN” statement in your query.

  • $foreignTable (string|array) – table to join (may include field and alias)

  • $masterField (mixed) – main field (and table) to join on or Expression

  • $joinKind (string) – ‘left’ (default), ‘inner’, ‘right’ etc - which join type to use



When joining with a different table, the results will be stacked by the SQL server so that fields from both tables are available. The first argument can specify the table to join, but may contain more information:

$q->join('address'); // = address_id
    // JOIN `address` ON `address`.`id`=`address_id`

$q->join('address a'); // specifies alias for the table
    // JOIN `address` `a` ON `address`.`id`=`address_id`

$q->join('address.user_id'); // address.user_id = id
    // JOIN `address` ON `address`.`user_id`=`id`

The second argument to join specifies which existing table/field is used in on condition:

$q->table('user u');
$q->join('user boss', 'u.boss_user_id');
    // JOIN `user` `boss` ON `boss`.`id`=`u`.`boss_user_id`

By default the “on” field is defined as $table . "_id", as you have seen in the previous examples where join was done on “address_id”, and “credit_card_id”. If you have specified field explicitly in the foreign field, then the “on” field is set to “id”, like in the example above.

You can specify both fields like this:

$q->join('salaries.emp_no', 'emp_no');

If you only specify field like this, then it will be automatically prefixed with the name or alias of your main table. If you have specified multiple tables, this won’t work and you’ll have to define name of the table explicitly:

$q->table('user u');
$q->join('user boss', 'u.boss_user_id');
$q->join('user super_boss', 'boss.boss_user_id');

The third argument specifies type of join and defaults to “left” join. You can specify “inner”, “straight” or any other join type that your database support.

Method can be executed several times on the same Query object.

Joining on expression

For a more complex join conditions, you can pass second argument as expression:

$q->table('user', 'u');
$q->join('address a', $q->expr(' like u.pattern'));

Use WITH cursors

Atk4\Data\Persistence\Sql\Query::with(Query $cursor, string $alias, ?array $fields = null, bool $recursive = false)

If you want to add WITH cursor statement in your SQL, then use this method. First parameter defines sub-query to use. Second parameter defines alias of this cursor. By using third, optional argument you can set aliases for columns in cursor. And finally forth, optional argument set if cursors will be recursive or not.

You can add more than one cursor in your query.

Did you know: you can use these cursors when joining your query to other tables. Just join cursor instead.

Keep in mind that if any of cursors added in your query will be recursive, then all cursors will be set recursive. That’s how SQL want it to be.


$quotes = $q->table('quotes')
    ->field($q->expr('sum([])', ['total_net']))
$invoices = $q()->table('invoices')
    ->field($q->expr('sum([])', ['total_net']))
$employees = $q
    ->with($quotes, 'q', ['emp', 'quoted'])
    ->with($invoices, 'i', ['emp', 'invoiced'])
    ->field(['name', 'salary', 'q.quoted', 'i.invoiced']);

This generates SQL below:

    `q` (`emp`, `quoted`) as (select `emp_id`, sum(`total_net`) from `quotes` group by `emp_id`),
    `i` (`emp`, `invoiced`) as (select `emp_id`, sum(`total_net`) from `invoices` group by `emp_id`)
select `name`, `salary`, `q`.`quoted`, `i`.`invoiced`
from `employees`
    left join `q` on `q`.`emp` = `employees`.`id`
    left join `i` on `i`.`emp` = `employees`.`id`

Limiting result-set

Atk4\Data\Persistence\Sql\Query::limit($cnt, $shift)

Limit how many rows will be returned.

  • $cnt (int) – number of rows to return

  • $shift (int) – offset, how many rows to skip



Use this to limit your Query result-set:

$q->limit(5, 10);
    // .. LIMIT 10, 5

    // .. LIMIT 0, 5

Ordering result-set

Atk4\Data\Persistence\Sql\Query::order($order, $desc)

Orders query result-set in ascending or descending order by single or multiple fields.

  • $order (string) – one or more field names, expression etc.

  • $desc (int) – pass true to sort descending



Use this to order your Query result-set:

$q->order('name'); // .. order by name
$q->order('name desc'); // .. order by name desc
$q->order(['name desc', 'id asc']) // .. order by name desc, id asc
$q->order('name', true); // .. order by name desc

Method can be executed several times on the same Query object.

Insert and Replace query

Set value to a field

Atk4\Data\Persistence\Sql\Query::set($field, $value)

Assigns value to the field during insert.

  • $field (string) – name of the field

  • $value (mixed) – value or expression




$q->table('user')->set('name', 'john')->mode('insert')->executeStatement();
    // insert into user (name) values (john)

$q->table('log')->set('date', $q->expr('now()'))->mode('insert')->executeStatement();
    // insert into log (date) values (now())

Method can be executed several times on the same Query object.

Set Insert Options

Atk4\Data\Persistence\Sql\Query::option($option, $mode = 'select')

It is possible to add arbitrary options for the query. For example this will fetch unique user birthdays:

$birthdays = $q->getRows();

Other possibility is to set options for delete or insert:

$q->option('delayed', 'insert');

// or

$q->option('ignore', 'insert');

See your SQL capabilities for additional options (low_priority, delayed, high_priority, ignore)

Update Query

Set Conditions

Same syntax as for Select Query.

Set value to a field

Same syntax as for Insert Query.

Other settings

Limit and Order are normally not included to avoid side-effects, but you can modify Query::$templateUpdate to include those tags.

Delete Query

Set Conditions

Same syntax as for Select Query.

Other settings

Limit and Order are normally not included to avoid side-effects, but you can modify Query::$templateUpdate to include those tags.

Dropping attributes

If you have called where() several times, there is a way to remove all the where clauses from the query and start from beginning:

  • $tag (string) – part of the query to delete/reset.


    ->where('name', 'John');
    ->where('name', 'Peter');

// where name = 'Peter'

Other Methods


Use this instead of new Query() if you want to automatically bind query to the same connection as the parent.

Atk4\Data\Persistence\Sql\Query::expr($template, $arguments)

Method very similar to Connection::expr but will return a corresponding Expression class for this query.


Method will return current_timestamp(precision) sub-query.

Atk4\Data\Persistence\Sql\Query::option($option, $mode)

Use this to set additional options for particular query mode. For example:

    ->set('name', 'John')
    ->option('calc_found_rows') // for default select mode
    ->option('ignore', 'insert') // for insert mode;

$q->executeQuery(); // select calc_found_rows `name` from `test`
$q->mode('insert')->executeStatement(); // insert ignore into `test` (`name`) values (`name` = 'John')
Atk4\Data\Persistence\Sql\Query::_setArgs($what, $alias, $value)

Internal method which sets value in Expression::$args array. It doesn’t allow duplicate aliases and throws Exception in such case. Argument $what can be ‘table’ or ‘field’.


Returns new Query object with CASE template. You can pass operand as parameter to create SQL like

CASE <operand> WHEN <expression> THEN <expression> END type of SQL statement.
Atk4\Data\Persistence\Sql\Query::caseWhen($when, $then)

Set WHEN condition and THEN expression for CASE statement.


Set ELSE expression for CASE statement.

Few examples:

$s = $this->q()->caseExpr()
    ->caseWhen(['status', 'New'], 't2.expose_new')
    ->caseWhen(['status', 'like', '%Used%'], 't2.expose_used')
case when "status" = 'New' then "t2"."expose_new" when "status" like '%Used%' then "t2"."expose_used" else null end
$s = $this->q()->caseExpr('status')
    ->caseWhen('New', 't2.expose_new')
    ->caseWhen('Used', 't2.expose_used')
case "status" when 'New' then "t2"."expose_new" when 'Used' then "t2"."expose_used" else null end


property Atk4\Data\Persistence\Sql\Query::$mode

Query will use one of the predefined “templates”. The mode will contain name of template used. Basically it’s array key of $templates property. See Query Modes.

property Atk4\Data\Persistence\Sql\Query::$defaultField

If no fields are defined, this field is used.

property Atk4\Data\Persistence\Sql\Query::$templateSelect

Template for SELECT query. See Query Modes.

property Atk4\Data\Persistence\Sql\Query::$templateInsert

Template for INSERT query. See Query Modes.

property Atk4\Data\Persistence\Sql\Query::$templateReplace

Template for REPLACE query. See Query Modes.

property Atk4\Data\Persistence\Sql\Query::$templateUpdate

Template for UPDATE query. See Query Modes.

property Atk4\Data\Persistence\Sql\Query::$templateDelete

Template for DELETE query. See Query Modes.

property Atk4\Data\Persistence\Sql\Query::$templateTruncate

Template for TRUNCATE query. See Query Modes.