Yii Query Builder - Yii

What is Yii Query Builder?

Query builder facilitates in the creation of SQL queries in a more programmatic way and helps in writing more readable SQL-related code.

How to use Yii Query Builder?

Yii query builder is used by following some of the steps like:

  • Build an yii\db\Query object.
  • Execute a query method.

To build an yii\db\Query object, adifferent query builder functions is called to define different parts of an SQL query.

Step 1 − To show a typical usage of the query builder, the actionTestDb method is modified by the code:

Step 2 − Visit http://localhost:8080/index.php?r=site/test-db, and the output appears as:

Query Builder

Where() function

The WHERE fragment of the query is defined by the where() function. The three different formats used to specify a WHERE condition are:

  • string format − 'name = User10'
  • hash format − ['name' => 'User10', 'email => user10@gmail.com']
  • operator format − ['like', 'name', 'User']

Example of String format

The output appears as:

String Format Output Example

Example of Hash format

The output appears as:

Hash Format Output Example

By the Operator format the arbitrary conditions are defined in the following format −

The operator can be −

  • and − ['and', 'id = 1', 'id = 2'] will generate id = 1 AND id = 2 or: similar to the and operator
  • between − ['between', 'id', 1, 15] will generate id BETWEEN 1 AND 15
  • not between − similar to the between operator, but BETWEEN is replaced with NOT BETWEEN
  • in − ['in', 'id', [5,10,15]] will generate id IN (5,10,15)
  • not in − similar to the in operator, but IN is replaced with NOT IN
  • like − ['like', 'name', 'user'] will generate name LIKE '%user%'
  • or like − similar to the like operator, but OR is used to split the LIKE predicates
  • not like − similar to the like operator, but LIKE is replaced with NOT LIKE
  • or not like − similar to the not like operator, but OR is used to concatenate the NOT LIKE predicates
  • exists − requires one operand which must be an instance of the yii\db\Query class
  • not exists − similar to the exists operator, but builds a NOT EXISTS (subquery) expression
  • <, <=, >, >=, or any other DB operator: ['<', 'id', 10] will generate id<10

Example of Operator format

The output appears as:

Operator Format Output Example

OrderBy() Function

The ORDER fragment is defined by the orderBy() function.

For instance,

The output appears as:

OrderBy Function Output Example

groupBy() Function

The GROUP BY fragment is defined by the groupBy() function and the HAVING fragment specifies the having() method.

For instance,

The output appears as:

GroupBy Function Output Example

LIMIT and OFFSET fragments are defined by the limit() and offset() methods.

For instance −

The output appears as:

Limit Offset Fragments

What are the different methods provided by Yii Query?

The yii\db\Query class provides a set of methods for different purposes

  • all() − Returns an array of rows of name-value pairs.
  • one() − Returns the first row.
  • column() − Returns the first column.
  • scalar() − Returns a scalar value from the first row and first column of the result.
  • exists() − Returns a value indicating whether the query contains any result
  • count() - Returns the result of a COUNT query
  • other aggregation query methods − Includes sum($q), average($q), max($q), min($q). The $q parameter can be either a column name or a DB expression.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Yii Topics