A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder below:

Working with Databases: Query Builder | The Definitive Guide to Yii 2.0

Query Builder

Built on top of Database Access Objects, query builder allows you to construct a SQL query in a programmatic and DBMS-agnostic way. Compared to writing raw SQL statements, using query builder will help you write more readable SQL-related code and generate more secure SQL statements.

Using query builder usually involves two steps:

  1. Build a yii\db\Query object to represent different parts (e.g. SELECT, FROM) of a SELECT SQL statement.
  2. Execute a query method (e.g. all()) of yii\db\Query to retrieve data from the database.

The following code shows a typical way of using query builder:

$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->all();

The above code generates and executes the following SQL query, where the :last_name parameter is bound with the string 'Smith'.

SELECT `id`, `email` 
FROM `user`
WHERE `last_name` = :last_name
LIMIT 10

Info: You usually mainly work with yii\db\Query instead of yii\db\QueryBuilder. The latter is invoked by the former implicitly when you call one of the query methods. yii\db\QueryBuilder is the class responsible for generating DBMS-dependent SQL statements (e.g. quoting table/column names differently) from DBMS-independent yii\db\Query objects.

Building Queries

To build a yii\db\Query object, you call different query building methods to specify different parts of a SQL query. The names of these methods resemble the SQL keywords used in the corresponding parts of the SQL statement. For example, to specify the FROM part of a SQL query, you would call the from() method. All the query building methods return the query object itself, which allows you to chain multiple calls together.

In the following, we will describe the usage of each query building method.

select()

The select() method specifies the SELECT fragment of a SQL statement. You can specify columns to be selected in either an array or a string, like the following. The column names being selected will be automatically quoted when the SQL statement is being generated from a query object.

$query->select(['id', 'email']);



$query->select('id, email');

The column names being selected may include table prefixes and/or column aliases, like you do when writing raw SQL queries. For example,

$query->select(['user.id AS user_id', 'email']);



$query->select('user.id AS user_id, email');

If you are using the array format to specify columns, you can also use the array keys to specify the column aliases. For example, the above code can be rewritten as follows,

$query->select(['user_id' => 'user.id', 'email']);

If you do not call the select() method when building a query, * will be selected, which means selecting all columns.

Besides column names, you can also select DB expressions. You must use the array format when selecting a DB expression that contains commas to avoid incorrect automatic name quoting. For example,

$query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']);

As with all places where raw SQL is involved, you may use the DBMS agnostic quoting syntax for table and column names when writing DB expressions in select.

Starting from version 2.0.1, you may also select sub-queries. You should specify each sub-query in terms of a yii\db\Query object. For example,

$subQuery = (new Query())->select('COUNT(*)')->from('user');


$query = (new Query())->select(['id', 'count' => $subQuery])->from('post');

To select distinct rows, you may call distinct(), like the following:


$query->select('user_id')->distinct();

You can call addSelect() to select additional columns. For example,

$query->select(['id', 'username'])
    ->addSelect(['email']);
from()

The from() method specifies the FROM fragment of a SQL statement. For example,


$query->from('user');

You can specify the table(s) being selected from in either a string or an array. The table names may contain schema prefixes and/or table aliases, like you do when writing raw SQL statements. For example,

$query->from(['public.user u', 'public.post p']);



$query->from('public.user u, public.post p');

If you are using the array format, you can also use the array keys to specify the table aliases, like the following:

$query->from(['u' => 'public.user', 'p' => 'public.post']);

Besides table names, you can also select from sub-queries by specifying them in terms of yii\db\Query objects. For example,

$subQuery = (new Query())->select('id')->from('user')->where('status=1');


$query->from(['u' => $subQuery]);
Prefixes

Also a default tablePrefix can be applied. Implementation instructions are in the "Quoting Tables" section of the "Database Access Objects" guide.

where()

The where() method specifies the WHERE fragment of a SQL query. You can use one of the four formats to specify a WHERE condition:

String Format

String format is best used to specify very simple conditions or if you need to use built-in functions of the DBMS. It works as if you are writing a raw SQL. For example,

$query->where('status=1');


$query->where('status=:status', [':status' => $status]);


$query->where('YEAR(somedate) = 2015');

Do NOT embed variables directly in the condition like the following, especially if the variable values come from end user inputs, because this will make your application subject to SQL injection attacks.


$query->where("status=$status");

When using parameter binding, you may call params() or addParams() to specify parameters separately.

$query->where('status=:status')
    ->addParams([':status' => $status]);

As with all places where raw SQL is involved, you may use the DBMS agnostic quoting syntax for table and column names when writing conditions in string format.

Hash Format

Hash format is best used to specify multiple AND-concatenated sub-conditions each being a simple equality assertion. It is written as an array whose keys are column names and values the corresponding values that the columns should be. For example,


$query->where([
    'status' => 10,
    'type' => null,
    'id' => [4, 8, 15],
]);

As you can see, the query builder is intelligent enough to properly handle values that are nulls or arrays.

You can also use sub-queries with hash format like the following:

$userQuery = (new Query())->select('id')->from('user');


$query->where(['id' => $userQuery]);

Using the Hash Format, Yii internally applies parameter binding for values, so in contrast to the string format, here you do not have to add parameters manually. However, note that Yii never escapes column names, so if you pass a variable obtained from user side as a column name without any additional checks, the application will become vulnerable to SQL injection attack. In order to keep the application secure, either do not use variables as column names or filter variable against allowlist. In case you need to get column name from user, read the Filtering Data guide article. For example the following code is vulnerable:


$column = $request->get('column');
$value = $request->get('value');
$query->where([$column => $value]);
Operator Format

Operator format allows you to specify arbitrary conditions in a programmatic way. It takes the following format:

[operator, operand1, operand2, ...]

where the operands can each be specified in string format, hash format or operator format recursively, while the operator can be one of the following:

Using the Operator Format, Yii internally uses parameter binding for values, so in contrast to the string format, here you do not have to add parameters manually. However, note that Yii never escapes column names, so if you pass a variable as a column name, the application will likely become vulnerable to SQL injection attack. In order to keep application secure, either do not use variables as column names or filter variable against allowlist. In case you need to get column name from user, read the Filtering Data guide article. For example the following code is vulnerable:


$column = $request->get('column');
$value = $request->get('value');
$query->where(['=', $column, $value]);
Object Format

Object Form is available since 2.0.14 and is both most powerful and most complex way to define conditions. You need to follow it either if you want to build your own abstraction over query builder or if you want to implement your own complex conditions.

Instances of condition classes are immutable. Their only purpose is to store condition data and provide getters for condition builders. Condition builder is a class that holds the logic that transforms data stored in condition into the SQL expression.

Internally the formats described above are implicitly converted to object format prior to building raw SQL, so it is possible to combine formats in a single condition:

$query->andWhere(new OrCondition([
    new InCondition('type', 'in', $types),
    ['like', 'name', '%good%'],
    'disabled=false'
]))

Conversion from operator format into object format is performed according to QueryBuilder::conditionClasses property, that maps operators names to representative class names:

And so on.

Using the object format makes it possible to create your own conditions or to change the way default ones are built. See Adding Custom Conditions and Expressions chapter to learn more.

Appending Conditions

You can use andWhere() or orWhere() to append additional conditions to an existing one. You can call them multiple times to append multiple conditions separately. For example,

$status = 10;
$search = 'yii';

$query->where(['status' => $status]);

if (!empty($search)) {
    $query->andWhere(['like', 'title', $search]);
}

If $search is not empty, the following WHERE condition will be generated:

WHERE (`status` = 10) AND (`title` LIKE '%yii%')
Filter Conditions

When building WHERE conditions based on input from end users, you usually want to ignore those input values, that are empty. For example, in a search form that allows you to search by username and email, you would like to ignore the username/email condition if the user does not enter anything in the username/email input field. You can achieve this goal by using the filterWhere() method:


$query->filterWhere([
    'username' => $username,
    'email' => $email,
]);

The only difference between filterWhere() and where() is that the former will ignore empty values provided in the condition in hash format. So if $email is empty while $username is not, the above code will result in the SQL condition WHERE username=:username.

Info: A value is considered empty if it is null, an empty array, an empty string or a string consisting of whitespaces only.

Like andWhere() and orWhere(), you can use andFilterWhere() and orFilterWhere() to append additional filter conditions to the existing one.

Additionally, there is yii\db\Query::andFilterCompare() that can intelligently determine operator based on what's in the value:

$query->andFilterCompare('name', 'John Doe');
$query->andFilterCompare('rating', '>9');
$query->andFilterCompare('value', '<=100');

You can also specify operator explicitly:

$query->andFilterCompare('name', 'Doe', 'like');

Since Yii 2.0.11 there are similar methods for HAVING condition:

orderBy()

The orderBy() method specifies the ORDER BY fragment of a SQL query. For example,


$query->orderBy([
    'id' => SORT_ASC,
    'name' => SORT_DESC,
]);

In the above code, the array keys are column names while the array values are the corresponding order by directions. The PHP constant SORT_ASC specifies ascending sort and SORT_DESC descending sort.

If ORDER BY only involves simple column names, you can specify it using a string, just like you do when writing raw SQL statements. For example,

$query->orderBy('id ASC, name DESC');

Note: You should use the array format if ORDER BY involves some DB expression.

You can call addOrderBy() to add additional columns to the ORDER BY fragment. For example,

$query->orderBy('id ASC')
    ->addOrderBy('name DESC');
groupBy()

The groupBy() method specifies the GROUP BY fragment of a SQL query. For example,


$query->groupBy(['id', 'status']);

If GROUP BY only involves simple column names, you can specify it using a string, just like you do when writing raw SQL statements. For example,

$query->groupBy('id, status');

Note: You should use the array format if GROUP BY involves some DB expression.

You can call addGroupBy() to add additional columns to the GROUP BY fragment. For example,

$query->groupBy(['id', 'status'])
    ->addGroupBy('age');
having()

The having() method specifies the HAVING fragment of a SQL query. It takes a condition which can be specified in the same way as that for where(). For example,


$query->having(['status' => 1]);

Please refer to the documentation for where() for more details about how to specify a condition.

You can call andHaving() or orHaving() to append additional conditions to the HAVING fragment. For example,


$query->having(['status' => 1])
    ->andHaving(['>', 'age', 30]);
limit() and offset()

The limit() and offset() methods specify the LIMIT and OFFSET fragments of a SQL query. For example,


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

If you specify an invalid limit or offset (e.g. a negative value), it will be ignored.

Info: For DBMS that do not support LIMIT and OFFSET (e.g. MSSQL), query builder will generate a SQL statement that emulates the LIMIT/OFFSET behavior.

join()

The join() method specifies the JOIN fragment of a SQL query. For example,


$query->join('LEFT JOIN', 'post', 'post.user_id = user.id');

The join() method takes four parameters:

You can use the following shortcut methods to specify INNER JOIN, LEFT JOIN and RIGHT JOIN, respectively.

For example,

$query->leftJoin('post', 'post.user_id = user.id');

To join with multiple tables, call the above join methods multiple times, once for each table.

Besides joining with tables, you can also join with sub-queries. To do so, specify the sub-queries to be joined as yii\db\Query objects. For example,

$subQuery = (new \yii\db\Query())->from('post');
$query->leftJoin(['u' => $subQuery], 'u.id = author_id');

In this case, you should put the sub-query in an array and use the array key to specify the alias.

union()

The union() method specifies the UNION fragment of a SQL query. For example,

$query1 = (new \yii\db\Query())
    ->select("id, category_id AS type, name")
    ->from('post')
    ->limit(10);

$query2 = (new \yii\db\Query())
    ->select('id, type, name')
    ->from('user')
    ->limit(10);

$query1->union($query2);

You can call union() multiple times to append more UNION fragments.

withQuery()

The withQuery() method specifies the WITH prefix of a SQL query. You can use it instead of subquery for more readability and some unique features (recursive CTE). Read more at modern-sql. For example, this query will select all nested permissions of admin with their children recursively,

$initialQuery = (new \yii\db\Query())
    ->select(['parent', 'child'])
    ->from(['aic' => 'auth_item_child'])
    ->where(['parent' => 'admin']);

$recursiveQuery = (new \yii\db\Query())
    ->select(['aic.parent', 'aic.child'])
    ->from(['aic' => 'auth_item_child'])
    ->innerJoin('t1', 't1.child = aic.parent');

$mainQuery = (new \yii\db\Query())
    ->select(['parent', 'child'])
    ->from('t1')
    ->withQuery($initialQuery->union($recursiveQuery), 't1', true);

withQuery() can be called multiple times to prepend more CTE's to main query. Queries will be prepend in same order as they attached. If one of query is recursive then whole CTE become recursive.

Query Methods

yii\db\Query provides a whole set of methods for different query purposes:

For example,


$rows = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->all();
    

$row = (new \yii\db\Query())
    ->from('user')
    ->where(['like', 'username', 'test'])
    ->one();

Note: The one() method only returns the first row of the query result. It does NOT add LIMIT 1 to the generated SQL statement. This is fine and preferred if you know the query will return only one or a few rows of data (e.g. if you are querying with some primary keys). However, if the query may potentially result in many rows of data, you should call limit(1) explicitly to improve the performance, e.g., (new \yii\db\Query())->from('user')->limit(1)->one().

All these query methods take an optional $db parameter representing the DB connection that should be used to perform a DB query. If you omit this parameter, the db application component will be used as the DB connection. Below is another example using the count() query method:


$count = (new \yii\db\Query())
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->count();

When you call a query method of yii\db\Query, it actually does the following work internally:

Sometimes, you may want to examine or use the SQL statement built from a yii\db\Query object. You can achieve this goal with the following code:

$command = (new \yii\db\Query())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)
    ->createCommand();
    

echo $command->sql;

print_r($command->params);


$rows = $command->queryAll();
Indexing Query Results

When you call all(), it will return an array of rows which are indexed by consecutive integers. Sometimes you may want to index them differently, such as indexing by a particular column or expression values. You can achieve this goal by calling indexBy() before all(). For example,


$query = (new \yii\db\Query())
    ->from('user')
    ->limit(10)
    ->indexBy('id')
    ->all();

The column which name is passed into indexBy() method must be present in the result set in order for indexing to work - it is up to the developer to take care of it.

To index by expression values, pass an anonymous function to the indexBy() method:

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy(function ($row) {
        return $row['id'] . $row['username'];
    })->all();

The anonymous function takes a parameter $row which contains the current row data and should return a scalar value which will be used as the index value for the current row.

Note: In contrast to query methods like groupBy() or orderBy() which are converted to SQL and are part of the query, this method works after the data has been fetched from the database. That means that only those column names can be used that have been part of SELECT in your query. Also if you selected a column with table prefix, e.g. customer.id, the result set will only contain id so you have to call ->indexBy('id') without table prefix.

Batch Query

When working with large amounts of data, methods such as yii\db\Query::all() are not suitable because they require loading the whole query result into the client's memory. To solve this issue Yii provides batch query support. The server holds the query result, and the client uses a cursor to iterate over the result set one batch at a time.

Warning: There are known limitations and workarounds for the MySQL implementation of batch queries. See below.

Batch query can be used like the following:

use yii\db\Query;

$query = (new Query())
    ->from('user')
    ->orderBy('id');

foreach ($query->batch() as $users) {
    
}


foreach ($query->each() as $user) {
    
    
}

The method yii\db\Query::batch() and yii\db\Query::each() return an yii\db\BatchQueryResult object which implements the Iterator interface and thus can be used in the foreach construct. During the first iteration, a SQL query is made to the database. Data is then fetched in batches in the remaining iterations. By default, the batch size is 100, meaning 100 rows of data are being fetched in each batch. You can change the batch size by passing the first parameter to the batch() or each() method.

Compared to the yii\db\Query::all(), the batch query only loads 100 rows of data at a time into the memory.

If you specify the query result to be indexed by some column via yii\db\Query::indexBy(), the batch query will still keep the proper index.

For example:

$query = (new \yii\db\Query())
    ->from('user')
    ->indexBy('username');

foreach ($query->batch() as $users) {
    
}

foreach ($query->each() as $username => $user) {
    
}
Limitations of batch query in MySQL

MySQL implementation of batch queries relies on the PDO driver library. By default, MySQL queries are buffered. This defeats the purpose of using the cursor to get the data, because it doesn't prevent the whole result set from being loaded into the client's memory by the driver.

Note: When libmysqlclient is used (typical of PHP5), PHP's memory limit won't count the memory used for result sets. It may seem that batch queries work correctly, but in reality the whole dataset is loaded into client's memory, and has the potential of using it up.

To disable buffering and reduce client memory requirements, PDO connection property PDO::MYSQL_ATTR_USE_BUFFERED_QUERY must be set to false. However, until the whole dataset has been retrieved, no other query can be made through the same connection. This may prevent ActiveRecord from making a query to get the table schema when it needs to. If this is not a problem (the table schema is cached already), it is possible to switch the original connection into unbuffered mode, and then roll back when the batch query is done.

Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);



Yii::$app->db->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

Note: In the case of MyISAM, for the duration of the batch query, the table may become locked, delaying or denying write access for other connections. When using unbuffered queries, try to keep the cursor open for as little time as possible.

If the schema is not cached, or it is necessary to run other queries while the batch query is being processed, you can create a separate unbuffered connection to the database:

$unbufferedDb = new \yii\db\Connection([
    'dsn' => Yii::$app->db->dsn,
    'username' => Yii::$app->db->username,
    'password' => Yii::$app->db->password,
    'charset' => Yii::$app->db->charset,
]);
$unbufferedDb->open();
$unbufferedDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

If you want to ensure that the $unbufferedDb has exactly the same PDO attributes like the original buffered $db but the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is false, consider a deep copy of $db, set it to false manually.

Then, queries are created normally. The new connection is used to run batch queries and retrieve results either in batches or one by one:


foreach ($query->batch(1000, $unbufferedDb) as $users) {
    
}



foreach ($query->each(1000, $unbufferedDb) as $user) {
    
}

When the connection is no longer necessary and the result set has been retrieved, it can be closed:

$unbufferedDb->close();

Note: unbuffered query uses less memory on the PHP-side, but can increase the load on the MySQL server. It is recommended to design your own code with your production practice for extra massive data, for example, divide the range for integer keys, loop them with Unbuffered Queries.

Adding custom Conditions and Expressions

As it was mentioned in Conditions – Object Format chapter, it is possible to create custom condition classes. For example, let's create a condition that will check that specific columns are less than some value. Using the operator format, it would look like the following:

[
    'and',
    ['>', 'posts', $minLimit],
    ['>', 'comments', $minLimit],
    ['>', 'reactions', $minLimit],
    ['>', 'subscriptions', $minLimit]
]

When such condition applied once, it is fine. In case it is used multiple times in a single query it can be optimized a lot. Let's create a custom condition object to demonstrate it.

Yii has a ConditionInterface, that must be used to mark classes, that represent a condition. It requires fromArrayDefinition() method implementation, in order to make possible to create condition from array format. In case you don't need it, you can implement this method with exception throwing.

Since we create our custom condition class, we can build API that suits our task the most.

namespace app\db\conditions;

class AllGreaterCondition implements \yii\db\conditions\ConditionInterface
{
    private $columns;
    private $value;

    
    public function __construct(array $columns, $value)
    {
        $this->columns = $columns;
        $this->value = $value;
    }
    
    public static function fromArrayDefinition($operator, $operands)
    {
        throw new InvalidArgumentException('Not implemented yet, but we will do it later');
    }
    
    public function getColumns() { return $this->columns; }
    public function getValue() { return $this->vaule; }
}

So we can create a condition object:

$condition = new AllGreaterCondition(['col1', 'col2'], 42);

But QueryBuilder still does not know, to make an SQL condition out of this object. Now we need to create a builder for this condition. It must implement yii\db\ExpressionBuilderInterface that requires us to implement a build() method.

namespace app\db\conditions;

class AllGreaterConditionBuilder implements \yii\db\ExpressionBuilderInterface
{
    use \yii\db\ExpressionBuilderTrait; 

     
    public function build(ExpressionInterface $expression, array &$params = [])
    {
        $value = $condition->getValue();
        
        $conditions = [];
        foreach ($expression->getColumns() as $column) {
            $conditions[] = new SimpleCondition($column, '>', $value);
        }

        return $this->queryBuilder->buildCondition(new AndCondition($conditions), $params);
    }
}

Then simple let QueryBuilder know about our new condition – add a mapping for it to the expressionBuilders array. It could be done right from the application configuration:

'db' => [
    'class' => 'yii\db\mysql\Connection',
    
    'queryBuilder' => [
        'expressionBuilders' => [
            'app\db\conditions\AllGreaterCondition' => 'app\db\conditions\AllGreaterConditionBuilder',
        ],
    ],
],

Now we can use our condition in where():

$query->andWhere(new AllGreaterCondition(['posts', 'comments', 'reactions', 'subscriptions'], $minValue));

If we want to make it possible to create our custom condition using operator format, we should declare it in QueryBuilder::conditionClasses:

'db' => [
    'class' => 'yii\db\mysql\Connection',
    
    'queryBuilder' => [
        'expressionBuilders' => [
            'app\db\conditions\AllGreaterCondition' => 'app\db\conditions\AllGreaterConditionBuilder',
        ],
        'conditionClasses' => [
            'ALL>' => 'app\db\conditions\AllGreaterCondition',
        ],
    ],
],

And create a real implementation of AllGreaterCondition::fromArrayDefinition() method in app\db\conditions\AllGreaterCondition:

namespace app\db\conditions;

class AllGreaterCondition implements \yii\db\conditions\ConditionInterface
{
    
     
    public static function fromArrayDefinition($operator, $operands)
    {
        return new static($operands[0], $operands[1]);
    }
}

After that, we can create our custom condition using shorter operator format:

$query->andWhere(['ALL>', ['posts', 'comments', 'reactions', 'subscriptions'], $minValue]);

You might notice, that there was two concepts used: Expressions and Conditions. There is a yii\db\ExpressionInterface that should be used to mark objects, that require an Expression Builder class, that implements yii\db\ExpressionBuilderInterface to be built. Also there is a yii\db\condition\ConditionInterface, that extends ExpressionInterface and should be used to objects, that can be created from array definition as it was shown above, but require builder as well.

To summarise:

You can create your own classes that implement ExpressionInterface to hide the complexity of transforming data to SQL statements. You will learn more about other examples of Expressions in the next article;

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4