1 follower

Query Builder

Preparing Query Builder
Building Data Retrieval Queries
Building Data Manipulation Queries
Building Schema Manipulation Queries

The Yii Query Builder provides an object-oriented way of writing SQL statements. It allows developers to use class methods and properties to specify individual parts of a SQL statement. It then assembles different parts into a valid SQL statement that can be further executed by calling the DAO methods as described in Data Access Objects. The following shows a typical usage of the Query Builder to build a SELECT SQL statement:

$user = Yii::app()->db->createCommand()
    ->select('id, username, profile')
    ->from('tbl_user u')
    ->join('tbl_profile p', 'u.id=p.user_id')
    ->where('id=:id', array(':id'=>$id))
    ->queryRow();

The Query Builder is best used when you need to assemble a SQL statement procedurally, or based on some conditional logic in your application. The main benefits of using the Query Builder include:

It allows building complex SQL statements programmatically.
It automatically quotes table names and column names to prevent conflict with SQL reserved words and special characters.
It also quotes parameter values and uses parameter binding when possible, which helps reduce risk of SQL injection attacks.
It offers certain degree of DB abstraction, which simplifies migration to different DB platforms.

It is not mandatory to use the Query Builder. In fact, if your queries are simple, it is easier and faster to directly write SQL statements.

Note: Query builder cannot be used to modify an existing query specified as a SQL statement. For example, the following code will not work:
$command = Yii::app()->db->createCommand('SELECT * FROM tbl_user');
// the following line will NOT append WHERE clause to the above SQL
$command->where('id=:id', array(':id'=>$id));
In other words, do not mix the usage of plain SQL and query builder.

1. Preparing Query Builder ¶

The Yii Query Builder is provided in terms of CDbCommand, the main DB query class described in Data Access Objects.

To start using the Query Builder, we create a new instance of CDbCommand as follows,

$command = Yii::app()->db->createCommand();

That is, we use Yii::app()->db to get the DB connection, and then call CDbConnection::createCommand() to create the needed command instance.

Note that instead of passing a whole SQL statement to the createCommand() call as we do in Data Access Objects, we leave it empty. This is because we will build individual parts of the SQL statement using the Query Builder methods explained in the following.

2. Building Data Retrieval Queries ¶

Data retrieval queries refer to SELECT SQL statements. The query builder provides a set of methods to build individual parts of a SELECT statement. Because all these methods return the CDbCommand instance, we can call them using method chaining, as shown in the example at the beginning of this section.

select(): specifies the SELECT part of the query
selectDistinct(): specifies the SELECT part of the query and turns on the DISTINCT flag
from(): specifies the FROM part of the query
where(): specifies the WHERE part of the query
andWhere(): appends condition to the WHERE part of the query with AND operator
orWhere(): appends condition to the WHERE part of the query with OR operator
join(): appends an inner join query fragment
leftJoin(): appends a left outer join query fragment
rightJoin(): appends a right outer join query fragment
crossJoin(): appends a cross join query fragment
naturalJoin(): appends a natural join query fragment
group(): specifies the GROUP BY part of the query
having(): specifies the HAVING part of the query
order(): specifies the ORDER BY part of the query
limit(): specifies the LIMIT part of the query
offset(): specifies the OFFSET part of the query
union(): appends a UNION query fragment

In the following, we explain how to use these query builder methods. For simplicity, we assume the underlying database is MySQL. Note that if you are using other DBMS, the table/column/value quoting shown in the examples may be different.

select()

function select($columns='*')

The select() method specifies the SELECT part of a query. The $columns parameter specifies the columns to be selected, which can be either a string representing comma-separated columns, or an array of column names. Column names can contain table prefixes and/or column aliases. The method will automatically quote the column names unless a column contains some parenthesis (which means the column is given as a DB expression).

Below are some examples:

// SELECT *
select()
// SELECT `id`, `username`
select('id, username')
// SELECT `tbl_user`.`id`, `username` AS `name`
select('tbl_user.id, username as name')
// SELECT `id`, `username`
select(array('id', 'username'))
// SELECT `id`, count(*) as num
select(array('id', 'count(*) as num'))

selectDistinct()

function selectDistinct($columns)

The selectDistinct() method is similar as select() except that it turns on the DISTINCT flag. For example, selectDistinct('id, username') will generate the following SQL:

SELECT DISTINCT `id`, `username`

from()

function from($tables)

The from() method specifies the FROM part of a query. The $tables parameter specifies which tables to be selected from. This can be either a string representing comma-separated table names, or an array of table names. Table names can contain schema prefixes (e.g. public.tbl_user) and/or table aliases (e.g. tbl_user u). The method will automatically quote the table names unless it contains some parenthesis (which means the table is given as a sub-query or DB expression).

Below are some examples:

// FROM `tbl_user`
from('tbl_user')
// FROM `tbl_user` `u`, `public`.`tbl_profile` `p`
from('tbl_user u, public.tbl_profile p')
// FROM `tbl_user`, `tbl_profile`
from(array('tbl_user', 'tbl_profile'))
// FROM `tbl_user`, (select * from tbl_profile) p
from(array('tbl_user', '(select * from tbl_profile) p'))

where()

function where($conditions, $params=array())

The where() method specifies the WHERE part of a query. The $conditions parameter specifies query conditions while $params specifies the parameters to be bound to the whole query. The $conditions parameter can be either a string (e.g. id=1) or an array of the format:

array(operator, operand1, operand2, ...)

where operator can be any of the following:

and: the operands should be concatenated together using AND. For example, array('and', 'id=1', 'id=2') will generate id=1 AND id=2. If an operand is an array, it will be converted into a string using the same rules described here. For example, array('and', 'type=1', array('or', 'id=1', 'id=2')) will generate type=1 AND (id=1 OR id=2). The method will NOT do any quoting or escaping.
or: similar as the and operator except that the operands are concatenated using OR.
in: operand 1 should be a column or DB expression, and operand 2 be an array representing the range of the values that the column or DB expression should be in. For example, array('in', 'id', array(1,2,3)) will generate id IN (1,2,3). The method will properly quote the column name and escape values in the range.
not in: similar as the in operator except that IN is replaced with NOT IN in the generated condition.
like: operand 1 should be a column or DB expression, and operand 2 be a string or an array representing the range of the values that the column or DB expression should be like. For example, array('like', 'name', '%tester%') will generate name LIKE '%tester%'. When the value range is given as an array, multiple LIKE predicates will be generated and concatenated using AND. For example, array('like', 'name', array('%test%', '%sample%')) will generate name LIKE '%test%' AND name LIKE '%sample%'. The method will properly quote the column name and escape values in the range.
not like: similar as the like operator except that LIKE is replaced with NOT LIKE in the generated condition.
or like: similar as the like operator except that OR is used to concatenated several LIKE predicates.
or not like: similar as the not like operator except that OR is used to concatenated several NOT LIKE predicates.

Below are some examples of using where:

// WHERE id=1 or id=2
where('id=1 or id=2')
// WHERE id=:id1 or id=:id2
where('id=:id1 or id=:id2', array(':id1'=>1, ':id2'=>2))
// WHERE id=1 OR id=2
where(array('or', 'id=1', 'id=2'))
// WHERE id=1 AND (type=2 OR type=3)
where(array('and', 'id=1', array('or', 'type=2', 'type=3')))
// WHERE `id` IN (1, 2)
where(array('in', 'id', array(1, 2))
// WHERE `id` NOT IN (1, 2)
where(array('not in', 'id', array(1,2)))
// WHERE `name` LIKE '%Qiang%'
where(array('like', 'name', '%Qiang%'))
// WHERE `name` LIKE '%Qiang' AND `name` LIKE '%Xue'
where(array('like', 'name', array('%Qiang', '%Xue')))
// WHERE `name` LIKE '%Qiang' OR `name` LIKE '%Xue'
where(array('or like', 'name', array('%Qiang', '%Xue')))
// WHERE `name` NOT LIKE '%Qiang%'
where(array('not like', 'name', '%Qiang%'))
// WHERE `name` NOT LIKE '%Qiang%' OR `name` NOT LIKE '%Xue%'
where(array('or not like', 'name', array('%Qiang%', '%Xue%')))

Please note that when the operator contains like, we have to explicitly specify the wildcard characters (such as % and _) in the patterns. If the patterns are from user input, we should also use the following code to escape the special characters to prevent them from being treated as wildcards:

$keyword=$_GET['q'];
// escape % and _ characters
$keyword=strtr($keyword, array('%'=>'\%', '_'=>'\_'));
$command->where(array('like', 'title', '%'.$keyword.'%'));

andWhere()

function andWhere($conditions, $params=array())

The andWhere() method appends given condition to the WHERE part of a query with AND operator. Behavior of this method is almost the same as where() except it appends condition not replaces it. Refer to the where() documentation for more information on parameters of this method.

orWhere()

function orWhere($conditions, $params=array())

The orWhere() method appends given condition to the WHERE part of a query with OR operator. Behavior of this method is almost the same as where() except it appends condition not replaces it. Refer to the where() documentation for more information on parameters of this method.

order()

function order($columns)

The order() method specifies the ORDER BY part of a query. The $columns parameter specifies the columns to be ordered by, which can be either a string representing comma-separated columns and order directions (ASC or DESC), or an array of columns and order directions. Column names can contain table prefixes. The method will automatically quote the column names unless a column contains some parenthesis (which means the column is given as a DB expression).

Below are some examples:

// ORDER BY `name`, `id` DESC
order('name, id desc')
// ORDER BY `tbl_profile`.`name`, `id` DESC
order(array('tbl_profile.name', 'id desc'))

limit() and offset()

function limit($limit, $offset=null)
function offset($offset)

The limit() and offset() methods specify the LIMIT and OFFSET part of a query. Note that some DBMS may not support LIMIT and OFFSET syntax. In this case, the Query Builder will rewrite the whole SQL statement to simulate the function of limit and offset.

Below are some examples:

// LIMIT 10
limit(10)
// LIMIT 10 OFFSET 20
limit(10, 20)
// OFFSET 20
offset(20)

join() and its variants

function join($table, $conditions, $params=array())
function leftJoin($table, $conditions, $params=array())
function rightJoin($table, $conditions, $params=array())
function crossJoin($table)
function naturalJoin($table)

The join() method and its variants specify how to join with other tables using INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN, or NATURAL JOIN. The $table parameter specifies which table to be joined with. The table name can contain schema prefix and/or alias. The method will quote the table name unless it contains a parenthesis meaning it is either a DB expression or sub-query. The $conditions parameter specifies the join condition. Its syntax is the same as that in where(). And $params specifies the parameters to be bound to the whole query.

Note that unlike other query builder methods, each call of a join method will be appended to the previous ones.

Below are some examples:

// JOIN `tbl_profile` ON user_id=id
join('tbl_profile', 'user_id=id')
// LEFT JOIN `pub`.`tbl_profile` `p` ON p.user_id=id AND type=1
leftJoin('pub.tbl_profile p', 'p.user_id=id AND type=:type', array(':type'=>1))

group()

function group($columns)

The group() method specifies the GROUP BY part of a query. The $columns parameter specifies the columns to be grouped by, which can be either a string representing comma-separated columns, or an array of columns. Column names can contain table prefixes. The method will automatically quote the column names unless a column contains some parenthesis (which means the column is given as a DB expression).

Below are some examples:

// GROUP BY `name`, `id`
group('name, id')
// GROUP BY `tbl_profile`.`name`, `id`
group(array('tbl_profile.name', 'id'))

having()

function having($conditions, $params=array())

The having() method specifies the HAVING part of a query. Its usage is the same as where().

Below are some examples:

// HAVING id=1 or id=2
having('id=1 or id=2')
// HAVING id=1 OR id=2
having(array('or', 'id=1', 'id=2'))

union()

function union($sql)

The union() method specifies the UNION part of a query. It appends $sql to the existing SQL using UNION operator. Calling union() multiple times will append multiple SQLs to the existing SQL.

Below are some examples:

// UNION (select * from tbl_profile)
union('select * from tbl_profile')

Executing Queries

After calling the above query builder methods to build a query, we can call the DAO methods as described in Data Access Objects to execute the query. For example, we can call CDbCommand::queryRow() to obtain a row of result, or CDbCommand::queryAll() to get all rows at once. Example:

$users = Yii::app()->db->createCommand()
    ->select('*')
    ->from('tbl_user')
    ->queryAll();

Retrieving SQLs

Besides executing a query built by the Query Builder, we can also retrieve the corresponding SQL statement. This can be done by calling CDbCommand::getText().

$sql = Yii::app()->db->createCommand()
    ->select('*')
    ->from('tbl_user')
    ->text;

If there are any parameters to be bound to the query, they can be retrieved via the CDbCommand::params property.

Alternative Syntax for Building Queries

Sometimes, using method chaining to build a query may not be the optimal choice. The Yii Query Builder allows a query to be built using simple object property assignments. In particular, for each query builder method, there is a corresponding property that has the same name. Assigning a value to the property is equivalent to calling the corresponding method. For example, the following two statements are equivalent, assuming $command represents a CDbCommand object:

$command->select(array('id', 'username'));
$command->select = array('id', 'username');

Furthermore, the CDbConnection::createCommand() method can take an array as the parameter. The name-value pairs in the array will be used to initialize the properties of the created CDbCommand instance. This means, we can use the following code to build a query:

$row = Yii::app()->db->createCommand(array(
    'select' => array('id', 'username'),
    'from' => 'tbl_user',
    'where' => 'id=:id',
    'params' => array(':id'=>1),
))->queryRow();

Building Multiple Queries

A CDbCommand instance can be reused multiple times to build several queries. Before building a new query, however, the CDbCommand::reset() method must be invoked to clean up the previous query. For example:

$command = Yii::app()->db->createCommand();
$users = $command->select('*')->from('tbl_users')->queryAll();
$command->reset();  // clean up the previous query
$posts = $command->select('*')->from('tbl_posts')->queryAll();

3. Building Data Manipulation Queries ¶

Data manipulation queries refer to SQL statements for inserting, updating and deleting data in a DB table. Corresponding to these queries, the query builder provides insert, update and delete methods, respectively. Unlike the SELECT query methods described above, each of these data manipulation query methods will build a complete SQL statement and execute it immediately.

insert(): inserts a row into a table
update(): updates the data in a table
delete(): deletes the data from a table

Below we describe these data manipulation query methods.

insert()

function insert($table, $columns)

The insert() method builds and executes an INSERT SQL statement. The $table parameter specifies which table to be inserted into, while $columns is an array of name-value pairs specifying the column values to be inserted. The method will quote the table name properly and will use parameter-binding for the values to be inserted.

Below is an example:

// build and execute the following SQL:
// INSERT INTO `tbl_user` (`name`, `email`) VALUES (:name, :email)
$command->insert('tbl_user', array(
    'name'=>'Tester',
    'email'=>'tester@example.com',
));

update()

function update($table, $columns, $conditions='', $params=array())

The update() method builds and executes an UPDATE SQL statement. The $table parameter specifies which table to be updated; $columns is an array of name-value pairs specifying the column values to be updated; $conditions and $params are like in where(), which specify the WHERE clause in the UPDATE statement. The method will quote the table name properly and will use parameter-binding for the values to be updated.

Below is an example:

// build and execute the following SQL:
// UPDATE `tbl_user` SET `name`=:name WHERE id=:id
$command->update('tbl_user', array(
    'name'=>'Tester',
), 'id=:id', array(':id'=>1));

delete()

function delete($table, $conditions='', $params=array())

The delete() method builds and executes a DELETE SQL statement. The $table parameter specifies which table to delete from; $conditions and $params are like in where(), which specify the WHERE clause in the DELETE statement. The method will quote the table name properly.

Below is an example:

// build and execute the following SQL:
// DELETE FROM `tbl_user` WHERE id=:id
$command->delete('tbl_user', 'id=:id', array(':id'=>1));

4. Building Schema Manipulation Queries ¶

Besides normal data retrieval and manipulation queries, the query builder also offers a set of methods for building and executing SQL queries that can manipulate the schema of a database. In particular, it supports the following queries:

createTable(): creates a table
renameTable(): renames a table
dropTable(): drops a table
truncateTable(): truncates a table
addColumn(): adds a table column
renameColumn(): renames a table column
alterColumn(): alters a table column
addForeignKey(): adds a foreign key (available since 1.1.6)
dropForeignKey(): drops a foreign key (available since 1.1.6)
dropColumn(): drops a table column
createIndex(): creates an index
dropIndex(): drops an index

Info: Although the actual SQL statements for manipulating database schema vary widely across different DBMS, the query builder attempts to provide a uniform interface for building these queries. This simplifies the task of migrating a database from one DBMS to another.

Abstract Data Types

The query builder introduces a set of abstract data types that can be used in defining table columns. Unlike the physical data types that are specific to particular DBMS and are quite different in different DBMS, the abstract data types are independent of DBMS. When abstract data types are used in defining table columns, the query builder will convert them into the corresponding physical data types.

The following abstract data types are supported by the query builder.

pk: a generic primary key type, will be converted into int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY for MySQL;
string: string type, will be converted into varchar(255) for MySQL;
text: text type (long string), will be converted into text for MySQL;
integer: integer type, will be converted into int(11) for MySQL;
float: floating number type, will be converted into float for MySQL;
decimal: decimal number type, will be converted into decimal for MySQL;
datetime: datetime type, will be converted into datetime for MySQL;
timestamp: timestamp type, will be converted into timestamp for MySQL;
time: time type, will be converted into time for MySQL;
date: date type, will be converted into date for MySQL;
binary: binary data type, will be converted into blob for MySQL;
boolean: boolean type, will be converted into tinyint(1) for MySQL;
money: money/currency type, will be converted into decimal(19,4) for MySQL. This type has been available since version 1.1.8.

createTable()

function createTable($table, $columns, $options=null)

The createTable() method builds and executes a SQL statement for creating a table. The $table parameter specifies the name of the table to be created. The $columns parameter specifies the columns in the new table. They must be given as name-definition pairs (e.g. 'username'=>'string'). The $options parameter specifies any extra SQL fragment that should be appended to the generated SQL. The query builder will quote the table name as well as the column names properly.

When specifying a column definition, one can use an abstract data type as described above. The query builder will convert the abstract data type into the corresponding physical data type, according to the currently used DBMS. For example, string will be converted into varchar(255) for MySQL.

A column definition can also contain non-abstract data type or specifications. They will be put in the generated SQL without any change. For example, point is not an abstract data type, and if used in a column definition, it will appear as is in the resulting SQL; and string NOT NULL will be converted into varchar(255) NOT NULL (i.e., only the abstract type string is converted).

Below is an example showing how to create a table:

// CREATE TABLE `tbl_user` (
//     `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
//     `username` varchar(255) NOT NULL,
//     `location` point
// ) ENGINE=InnoDB
createTable('tbl_user', array(
    'id' => 'pk',
    'username' => 'string NOT NULL',
    'location' => 'point',
), 'ENGINE=InnoDB')

renameTable()

function renameTable($table, $newName)

The renameTable() method builds and executes a SQL statement for renaming a table. The $table parameter specifies the name of the table to be renamed. The $newName parameter specifies the new name of the table. The query builder will quote the table names properly.

Below is an example showing how to rename a table:

// RENAME TABLE `tbl_users` TO `tbl_user`
renameTable('tbl_users', 'tbl_user')

dropTable()

function dropTable($table)

The dropTable() method builds and executes a SQL statement for dropping a table. The $table parameter specifies the name of the table to be dropped. The query builder will quote the table name properly.

Below is an example showing how to drop a table:

// DROP TABLE `tbl_user`
dropTable('tbl_user')

truncateTable()

function truncateTable($table)

The truncateTable() method builds and executes a SQL statement for truncating a table. The $table parameter specifies the name of the table to be truncated. The query builder will quote the table name properly.

Below is an example showing how to truncate a table:

// TRUNCATE TABLE `tbl_user`
truncateTable('tbl_user')

addColumn()

function addColumn($table, $column, $type)

The addColumn() method builds and executes a SQL statement for adding a new table column. The $table parameter specifies the name of the table that the new column will be added to. The $column parameter specifies the name of the new column. And $type specifies the definition of the new column. Column definition can contain abstract data type, as described in the subsection of "createTable". The query builder will quote the table name as well as the column name properly.

Below is an example showing how to add a table column:

// ALTER TABLE `tbl_user` ADD `email` varchar(255) NOT NULL
addColumn('tbl_user', 'email', 'string NOT NULL')

dropColumn()

function dropColumn($table, $column)

The dropColumn() method builds and executes a SQL statement for dropping a table column. The $table parameter specifies the name of the table whose column is to be dropped. The $column parameter specifies the name of the column to be dropped. The query builder will quote the table name as well as the column name properly.

Below is an example showing how to drop a table column:

// ALTER TABLE `tbl_user` DROP COLUMN `location`
dropColumn('tbl_user', 'location')

renameColumn()

function renameColumn($table, $name, $newName)

The renameColumn() method builds and executes a SQL statement for renaming a table column. The $table parameter specifies the name of the table whose column is to be renamed. The $name parameter specifies the old column name. And $newName specifies the new column name. The query builder will quote the table name as well as the column names properly.

Below is an example showing how to rename a table column:

// ALTER TABLE `tbl_users` CHANGE `name` `username` varchar(255) NOT NULL
renameColumn('tbl_user', 'name', 'username')

alterColumn()

function alterColumn($table, $column, $type)

The alterColumn() method builds and executes a SQL statement for altering a table column. The $table parameter specifies the name of the table whose column is to be altered. The $column parameter specifies the name of the column to be altered. And $type specifies the new definition of the column. Column definition can contain abstract data type, as described in the subsection of "createTable". The query builder will quote the table name as well as the column name properly.

Below is an example showing how to alter a table column:

// ALTER TABLE `tbl_user` CHANGE `username` `username` varchar(255) NOT NULL
alterColumn('tbl_user', 'username', 'string NOT NULL')

addForeignKey()

function addForeignKey($name, $table, $columns,
    $refTable, $refColumns, $delete=null, $update=null)

The addForeignKey() method builds and executes a SQL statement for adding a foreign key constraint to a table. The $name parameter specifies the name of the foreign key. The $table and $columns parameters specify the table name and column name that the foreign key is about. If there are multiple columns, they should be separated by comma characters. The $refTable and $refColumns parameters specify the table name and column name that the foreign key references. The $delete and $update parameters specify the ON DELETE and ON UPDATE options in the SQL statement, respectively. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL. The query builder will properly quote the table name, index name and column name(s).

Below is an example showing how to add a foreign key constraint,

// ALTER TABLE `tbl_profile` ADD CONSTRAINT `fk_profile_user_id`
// FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`)
// ON DELETE CASCADE ON UPDATE CASCADE
addForeignKey('fk_profile_user_id', 'tbl_profile', 'user_id',
    'tbl_user', 'id', 'CASCADE', 'CASCADE')

dropForeignKey()

function dropForeignKey($name, $table)

The dropForeignKey() method builds and executes a SQL statement for dropping a foreign key constraint. The $name parameter specifies the name of the foreign key constraint to be dropped. The $table parameter specifies the name of the table that the foreign key is on. The query builder will quote the table name as well as the constraint names properly.

Below is an example showing how to drop a foreign key constraint:

// ALTER TABLE `tbl_profile` DROP FOREIGN KEY `fk_profile_user_id`
dropForeignKey('fk_profile_user_id', 'tbl_profile')

createIndex()

function createIndex($name, $table, $column, $unique=false)

The createIndex() method builds and executes a SQL statement for creating an index. The $name parameter specifies the name of the index to be created. The $table parameter specifies the name of the table that the index is on. The $column parameter specifies the name of the column to be indexed. And the $unique parameter specifies whether a unique index should be created. If the index consists of multiple columns, they must be separated by commas. The query builder will properly quote the table name, index name and column name(s).

Below is an example showing how to create an index:

// CREATE INDEX `idx_username` ON `tbl_user` (`username`)
createIndex('idx_username', 'tbl_user', 'username')

dropIndex()

function dropIndex($name, $table)

The dropIndex() method builds and executes a SQL statement for dropping an index. The $name parameter specifies the name of the index to be dropped. The $table parameter specifies the name of the table that the index is on. The query builder will quote the table name as well as the index names properly.

Below is an example showing how to drop an index:

// DROP INDEX `idx_username` ON `tbl_user`
dropIndex('idx_username', 'tbl_user')

数据访问对象（DAO）

Go to Top

Active Record

Found a typo or you think this page needs improvement?
Edit it on github !