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:
[php]
$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:
[php] $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.
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,
[php] $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.
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.
AND operatorOR operatorIn 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.
[php] function select($columns='*')
The [select()|CDbCommand::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:
[php]
// 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'))[php] function selectDistinct($columns)
The [selectDistinct()|CDbCommand::selectDistinct] method is similar
as [select()|CDbCommand::select() ] except that it turns on the DISTINCT flag. For example, selectDistinct('id, username') will generate the following SQL:
SELECT DISTINCT `id`, `username`
[php] function from($tables)
The [from()|CDbCommand::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:
[php]
// 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'))[php] function where($conditions, $params=array())
The [where()|CDbCommand::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:
[php] 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:
[php]
// 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:
[php]
$keyword=$_GET['q'];
// escape % and _ characters
$keyword=strtr($keyword, array('%'=>'\%', '_'=>'\_'));
$command->where(array('like', 'title', '%'.$keyword.'%'));[php] function andWhere($conditions, $params=array())
The [andWhere()|CDbCommand::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()|CDbCommand::where() ] except it appends condition not replaces
it. Refer to the [where()|CDbCommand::where() ] documentation for more
information on parameters of this method.
[php] function orWhere($conditions, $params=array())
The [orWhere()|CDbCommand::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()|CDbCommand::where() ] except it appends condition not replaces
it. Refer to the [where()|CDbCommand::where() ] documentation for more
information on parameters of this method.
[php] function order($columns)
The [order()|CDbCommand::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:
[php]
// ORDER BY `name`, `id` DESC
order('name, id desc')
// ORDER BY `tbl_profile`.`name`, `id` DESC
order(array('tbl_profile.name', 'id desc'))[php] function limit($limit, $offset=null) function offset($offset)
The [limit()|CDbCommand::limit() ] and [offset()|CDbCommand::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:
[php] // LIMIT 10 limit(10) // LIMIT 10 OFFSET 20 limit(10, 20) // OFFSET 20 offset(20)
[php] 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()|CDbCommand::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()|CDbCommand::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:
[php]
// 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))[php] function group($columns)
The [group()|CDbCommand::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:
[php]
// GROUP BY `name`, `id`
group('name, id')
// GROUP BY `tbl_profile`.`name`, `id`
group(array('tbl_profile.name', 'id'))[php] function having($conditions, $params=array())
The [having()|CDbCommand::having() ] method specifies the HAVING part of a query. Its usage is the same as [where()|CDbCommand::where() ].
Below are some examples:
[php]
// 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'))[php] function union($sql)
The [union()|CDbCommand::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:
[php]
// UNION (select * from tbl_profile)
union('select * from tbl_profile')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:
[php]
$users = Yii::app()->db->createCommand()
->select('*')
->from('tbl_user')
->queryAll();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()].
[php]
$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.
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:
[php]
$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:
[php]
$row = Yii::app()->db->createCommand(array(
'select' => array('id', 'username'),
'from' => 'tbl_user',
'where' => 'id=:id',
'params' => array(':id'=>1),
))->queryRow();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:
[php]
$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();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.
Below we describe these data manipulation query methods.
[php] function insert($table, $columns)
The [insert()|CDbCommand::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:
[php]
// 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',
));[php] function update($table, $columns, $conditions='', $params=array())
The [update()|CDbCommand::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()|CDbCommand::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:
[php]
// 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));[php] function delete($table, $conditions='', $params=array())
The [delete()|CDbCommand::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()|CDbCommand::where() ], which specify the WHERE clause in the DELETE statement. The method will quote the table name properly.
Below is an example:
[php]
// build and execute the following SQL:
// DELETE FROM `tbl_user` WHERE id=:id
$command->delete('tbl_user', 'id=:id', array(':id'=>1));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:
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.
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.[php] function createTable($table, $columns, $options=null)
The [createTable()|CDbCommand::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:
[php]
// 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')[php] function renameTable($table, $newName)
The [renameTable()|CDbCommand::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:
[php]
// RENAME TABLE `tbl_users` TO `tbl_user`
renameTable('tbl_users', 'tbl_user')[php] function dropTable($table)
The [dropTable()|CDbCommand::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:
[php]// DROP TABLE `tbl_user`dropTable('tbl_user')[php] function truncateTable($table)
The [truncateTable()|CDbCommand::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:
[php]
// TRUNCATE TABLE `tbl_user`
truncateTable('tbl_user')[php] function addColumn($table, $column, $type)
The [addColumn()|CDbCommand::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:
[php]
// ALTER TABLE `tbl_user` ADD `email` varchar(255) NOT NULL
addColumn('tbl_user', 'email', 'string NOT NULL')[php] function dropColumn($table, $column)
The [dropColumn()|CDbCommand::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:
[php]
// ALTER TABLE `tbl_user` DROP COLUMN `location`
dropColumn('tbl_user', 'location')[php] function renameColumn($table, $name, $newName)
The [renameColumn()|CDbCommand::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:
[php]
// ALTER TABLE `tbl_users` CHANGE `name` `username` varchar(255) NOT NULL
renameColumn('tbl_user', 'name', 'username')[php] function alterColumn($table, $column, $type)
The [alterColumn()|CDbCommand::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:
[php]
// ALTER TABLE `tbl_user` CHANGE `username` `username` varchar(255) NOT NULL
alterColumn('tbl_user', 'username', 'string NOT NULL')[php] function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete=null, $update=null)
The [addForeignKey()|CDbCommand::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,
[php]
// 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')[php] function dropForeignKey($name, $table)
The [dropForeignKey()|CDbCommand::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:
[php]
// ALTER TABLE `tbl_profile` DROP FOREIGN KEY `fk_profile_user_id`
dropForeignKey('fk_profile_user_id', 'tbl_profile')[php] function createIndex($name, $table, $column, $unique=false)
The [createIndex()|CDbCommand::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:
[php]
// CREATE INDEX `idx_username` ON `tbl_user` (`username`)
createIndex('idx_username', 'tbl_user', 'username')[php] function dropIndex($name, $table)
The [dropIndex()|CDbCommand::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:
[php]
// DROP INDEX `idx_username` ON `tbl_user`
dropIndex('idx_username', 'tbl_user')