Yii CDbCommand 用法大全

The original address:

But the English, Yii website no translation of this chapter, you will try to translate it, there are many places will be according to their own understanding, not according to the original translation. . .

The Yii query builder provides write SQL statements using object oriented method. Methods and properties it allows developers to use class to specify a SQL statement part. Then, assembled into a valid SQL statement, can be described by a call to the DAO method for the further implementation of the data access object. The following shows the builder to build a select statement to query a typical use:

$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();

When you need to assemble the SQL statements in an application program, or some conditional logic based on time, it is best to use the query builder. Using the query builder benefits include:

It can build complex SQL statements programming.

It will self reference table and column names to prevent SQL reserved word and the special character of conflict.

It can also refer to the parameter values and the use of parameter bindings, which helps reduce the risk of SQL injection attacks.

It provides a certain degree of database abstraction, simplification of the migration to a different database platform of the cost.

It is not mandatory to use the query builder. In fact, if your query is simple, it is more easy and direct write SQL statements faster.

Note: the query builder cannot be used to modify the existing query is specified as the 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 with ordinary SQL and query builder.

1 Preparation of the query builder(Preparing Query Builder)

The Yii query builder is from CDbCommand to provide the main database query, describe the data access object.

Start using the query builder, we create a new instance of the CDbCommand,

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

We use the Yii:: app (-> dB) to get a database connection, and then call CDbConnection:: createCommand () to create the desired command instance.

Please note, the SQL statements in createcommand (), called data access objects, we need to set it to empty. This is because we will be explained in the following query method in various parts of the generator to add a SQL statement.

2 to establish a data retrieval(Building Data Retrieval Queries)

Data retrieval refers to the selection of the SQL statement. Each query builder provides a set of methods to establish a SELECT statement part. For instance all these methods return CDbCommand, we can use the method call their chains, as shown in the example at the beginning of this section,.

select():             The specified query selection part                       specifies the SELECT part of the query
selectDistinct(): The specified query is not repeated selection               specifies the SELECT part of the query and turns on the DISTINCT flag
from():             The specified query FROM                           specifies the FROM part of the query
where():           The specified query WHERE                       specifies the WHERE part of the query
andWhere(): With the way and is added to the WHERE appends condition to the WHERE in part of the query with AND operator
orWhere():       With the way or is added to the WHERE appends condition to the WHERE in part of the query with OR operator
join():               Add a inner join query fragment           appends an inner join query fragment
leftJoin():         Add a left outer join query fragment               appends a left outer join query fragment
rightJoin():       Additional external a right connection query fragment           appends a right outer join query fragment
crossJoin():       Add a cross join query fragment               appends a cross join query fragment
naturalJoin(): Appends a natural join query fragment               appends a natural join query fragment
group():           The specified query GROUP BY                 specifies the GROUP BY part of the query
having():           The specified query HAVING                     specifies the HAVING part of the query
order():           The specified query ORDER BY                   specifies the ORDER BY part of the query
limit():             The specified query LIMIT                         specifies the LIMIT part of the query
offset():         The specified query OFFSET                       specifies the OFFSET part of the query
union():         Add query UNION                         appends a UNION query fragment

In the following, we will explain how to use the query builder methods. For simplicity, we assume the underlying database is MySQL. Note: if you are using other database, table / column / value reference example might be different.

select()

function select($columns='*')
The select () method to specify a query selection. The $columns parameter specifies the column that you want to select, it can be a string, use a comma delimited list, or an array of column name. Column names can contain table prefix and / or a column alias. This method will automatically reference the name, unless a column that contains some brackets (which means that this column is the expression of a DB).

Here 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)
selectdistinct()Method is similar to select (), which opened the DISTINCT logo in addition. For example, selectdistinct ('id, username ") will have the following SQL:

SELECT DISTINCT `id`, `username`

from()

function from($tables)
from()Method to specify the FROM part of a query. $Specifies the choice of tables parameters. This can be a string, use a comma separated list of names, or the name of the table array. Table names can contain the schema prefix (e.g. public. tbl_user)And / or table aliases(e.g.tbl_user U). This method will automatically reference the table name, unless it contains some brackets (i.e. table is the expression of a given query or DB).

Here 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())
where()Method to specify the query WHERE. The $conditions parameter specifies the query conditions at the same time, the $params parameter is bound to the specified query. The $conditions parameter can be a string (for example, id = 1) or an array format:

array(operator, operand1, operand2, ...)
Operator can be any one of the following:

And: operands should use and together. For example, array ('and','id=1','id=2') will produce id=1 AND id=2 . If one operand is an array, it will use the same rules described here is converted to a string. For example, array ('and','type=1', array ('or','id=1','id=2')) will generate type=1 AND (id=1 OR id=2).

Or: is similar to and operation, in addition to the operands using the OR connection.

In: operand 1 is a kind of list or DB expression, while operand 2 is an array representing a range value, or DB expression should in the range of the array. For example, array ('in','id', array (1,2,3)) will generate ID IN (1,2,3).

Not in: is similar to in operation, in addition to using NOT IN instead of IN to generate SQL.

Like: operand 1 is a kind of list or DB expression, while operand 2 is an array representing a range value, or DB expression should in the range of the array. For example, array ('like','name','%tester%') will generate name LIKE '%tester%'. When the range specified value as an array, multiple LIKE generation SQL will use AND connection. For example, array ('like','name', array ('%test%','%sample%')) will generate name LIKE '%test%' AND name LIKE '%sample%'.

Not like: is similar to like operation, in addition to using NOT LIKE instead of LIKE to generate SQL.

Or like: is similar to like operation, in addition to using OR to connect multiple like generation SQL.

Or not like: is similar to not like operation, in addition to using OR to connect multiple like generation SQL.

Here are some examples, the use of local:

// 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 a like, we must specify a wildcard (e.g.% and _) mode. If the parameter is input from the user, we should use the following code to escape special characters, to prevent them from being used as a wildcard character:

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

andWhere()

function andWhere($conditions, $params=array())
With the way and is added to the WHERE conditions. The behavior of this method is almost the same where (), except that it just add a condition which cannot be replaced. In the where () with the method of parameter with information in a document.

orWhere()

function orWhere($conditions, $params=array())
With the way or is added to the WHERE conditions. The behavior of this method is almost the same where (), except that it just add a condition which cannot be replaced. In the where () with the method of parameter with information in a document.

order()

function order($columns)
order() Method to specify query ORDER BY part. $The columns parameter specifies the sort columns, this can be a comma separated list contains order and the direction (ASC or DESC) string, an array or a column and the order direction. Column names can contain table prefix. This method will automatically reference the name, unless a column that contains some brackets (this is a DB expression).

Here 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)
limit()And the offset () method to specify the query of LIMIT and OFFSET. Please note, some of the DBMS does not support LIMIT and OFFSET syntax. In this case, the query builder will rewrite the SQL statement to simulate the LIMIT and OFFSET functions.

Here are some examples.:

// LIMIT 10 from the top 10
limit(10)
// LIMIT 10 OFFSET 20 to 21~30
limit(10, 20)
// OFFSET 20 to remove the 20 remaining after data
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)
join()Method and its variants to specify how to connect with other tables, use an inner join, left outer join, right outer join, cross connection, or natural. $The table parameter to specify which table to add. Table name can contain the database prefix and / or alias. The reference table name, unless it contains an insert, which is a DB expression or sub query. $The conditions parameter specifies the connection condition. Its syntax is similar to where (the same). $The params parameter specifies the bound to the query parameters.

Notable is, unlike other query builder methods, each call to a join method will be appended to the SQL.

Here 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)
group() Method to specify the query GROUP BY. The $columns parameter specifies the column group, it can be a comma separated list of strings, or a column of the array. Column names can contain table prefix. This method will automatically reference the name, unless a column that contains some brackets (which is an DB expression).

Here 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())
having() Method to specify the query HAVING. It is used with the where (the same).

Here 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)
union() Method to specify the query UNION. It uses the UNION is added to the existing SQL. Call Union () many existing SQL add more tables.
Here are some examples.:

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

Query execution(Executing Queries)

Call it the query builder methods to build a query, we can object to access the data by calling the DAO method executes the query. For example, we can call CDbCommand:: queryRow () to obtain continuous results, or use the CDbCommand:: queryAll () get all rows immediately.
Example:

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

The retrieval table(Retrieving SQLs)
In addition to the implementation of the query builder queries, we can also obtain the corresponding SQL statement. This can be done by calling CDbCommand:: getText (obtained).

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

If any of the parameter must be bound to a query, they can pass the CDbCommand: attributes: params.

Create a query substitution grammar(Alternative Syntax for Building Queries)

Sometimes, to build a query may not be the best choice to use method of chain. The Yii Query Builder allows the use of simple as attribute assignment way to build a query. In particular, the generator for each query has an attribute with the same name. To attribute assignment is equivalent to call the appropriate method. For example, the following two statements are equivalent, if $command is the CDbCommand object:

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

In addition, CDbConnection:: createCommand () method can take an array as a parameter. An array of key value pairs will be used to initialize the CDbCommand create an instance attribute. 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();

Create multiple query(Building Multiple Queries)

A CDbCommand instance can be repeatedly used to build several query. Before the establishment of a new query, you need to call the CDbCommand:: reset () method to clean up the previous query. 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 the establishment of data query(Building Data Manipulation Queries)

Data query refers to the SQL statement to insert, update and delete data in a database table. Corresponding to these queries, the query builder respectively provides insert, update and delete method. Different from the above query method of SELECT, the data query methods will build a complete SQL statement, and executed immediately.

insert(): Inserts a row into the table
update(): Update the data in the table
delete(): Delete data from a table
The following describes these operations on the data query method.

insert()

function insert($table, $columns)
insert()Method of establishment and implementation of a INSERT SQL statement. The $table parameter specifies to insert a table, and the key to the specified array $columns to insert column values. This method will escape the table name, and it will be combined with the binding parameters.

The following 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'=>'',
));

update()

function update($table, $columns, $conditions='', $params=array())
update()Method of establishment and implementation of a SQL update. $The table parameter specifies the table to update; $columns is the key to the array, is used to specify the columns to update values; $conditions and $params (like where), specifying the WHERE clause in the UPDATE statement. This method will escape the table name, and it will be combined with the parameter values to be updated using the.

The following 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())
delete()Method of establishment and implementation of a SQL delete statement. The $table parameter specifies that you want to delete data table; $conditions and $params (like where), specifying the WHERE clause in the DELETE statement. This method will escape the table name.

The following 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 establish a structure query(Building Schema Manipulation Queries)

In addition to the normal data retrieval and query, the query builder provides a set of constructs for can manipulate the database and SQL query execution. It supports the following operations:

createTable(): Create a table
renameTable(): Rename
dropTable(): To delete a table
truncateTable(): Cut off a table, delete all the data in a table but does not delete the table itself
addColumn(): Add columns to the table
renameColumn(): Rename the column in the table
alterColumn(): To change a table column
addForeignKey(): Add a foreign key (1.1.6 available)
dropForeignKey(): Deletion of a foreign key (1.1.6 available)
dropColumn(): To delete a table column
createIndex(): Create an index
dropIndex(): Delete an index

Info: Although the SQL database statement operating in different database management systems model are very different, but the query builder attempts to provide a unified interface, used to construct the query. This simplifies database migration from a database management system to another task.

Abstract data types, Abstract Data Types

The introduction of the query builder a group can be used in the definition of abstract data types in a column of the table. Different types and physical data, physical data type has a specific and unique DBMS are very different in different DBMS, abstract data types are independent of DBMS. When the abstract data types used in the definition of the column of a table, the query builder to convert it into the corresponding physical data type.

The following abstract data types supported by the query builder.

Pk: a common key type, will be converted to int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY for MySQL;

The string: string types, will be converted to varchar(255) for MySQL;
Text: text type (string), will be converted to text for MySQL;
Integer: integer types, will be converted to int(11) for MySQL;
Float: floating point types, will be converted to float for MySQL;
Decimal: decimal type, will be converted to decimal for MySQL;
Datetime: type of datetime, will be converted to datetime for MySQL;
Timestamp: timestamp type, will be converted to timestamp for MySQL;
Time: type of time, will be converted to time for MySQL;
Date: date type, will be converted to date for MySQL;
Binary: binary data types, will be converted to blob for MySQL;
Boolean: type Boolean, will be converted to tinyint(1) for MySQL;
Money: money / currency, will be converted to decimal (19,4) for MySQL. from the 1.1.8 version of this type can be used.

createTable()

function createTable($table, $columns, $options=null)
createTable() Methods to build and execute a create table SQL statement. $The table parameter specifies the name of the table to be created. $The columns parameter specifies the columns in the table. They must be specified as name type pairs(e.g. 'username'=>'string'). $The options parameter specifies the should be attached to any additional SQL fragments generated SQL. The query builder will reference the table name right and the column name.

When specifying a column definition, abstract data types can be used as mentioned above. Query Builder based on abstract data types of database management systems currently in use are converted into the corresponding physical data type. For example, the string types will be converted to MySQL varchar(255).

A column definition can also contain non abstract data type or specifications. They will not have any change is placed in the generated SQL. For example, point is not an abstract data type, if used in the column definition, it will appear in the generated SQL, and the string NOT NULL will be converted to varchar (255) NOT NULL (i.e. only string abstract type conversion).

Here is an example, which shows 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)
renameTable() The SQL statement executes a method to create and rename the table name. The $table parameter specifies the names of the tables to rename. The new name specified in table $newName parameters. The query builder will correctly reference table name.

Here is an example, demonstrates how to rename:

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

dropTable()

function dropTable($table)
dropTable() Method to create and execute a SQL statement to delete the table. The $table parameter specifies the name you want to delete the table. The query builder will correctly referenced table name.

Here is an example that shows how to delete a table:

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

truncateTable()

function truncateTable($table)
truncateTable() Methods the establishment and implementation of an empty table data in SQL statements. The $table parameter specifies the name of the table is empty. The query builder will correctly referenced table name.

Here is a sample to show how to clear the table:

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

addColumn()

function addColumn($table, $column, $type)
addColumn() A method to create and add a new column of the table SQL statement execution. $The table parameter specifies the name of the new column will be added to the table. $The column parameter specifies the name of the new column. $The definition of type specifies the new column. In the column definition can include abstract data types, such as "createTable" in the description section. The query builder will reference the table name right and the column name.

Here is an example, demonstrates how to add a column of the table:

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

dropColumn()

function dropColumn($table, $column)
dropColumn() Method to create and execute a SQL statement to delete the table columns. The $table parameter specifies the column belongs to the deleted table name. The $column parameter specifies the column names to be removed. The query builder will correctly referenced table names and column names.

The following is an example showing how to delete a table column:

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

renameColumn()

function renameColumn($table, $name, $newName)
renameColumn() Method to create and execute a rename SQL statement. The $table parameter specifies the name of the column you want to rename the table. The old name specified by the $name parameter. $newName specify a new name. The query builder will correctly referenced table names and column names.

Here is an example, demonstrates 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)
alterColumn() Method to create and execute a SQL statement to modify the list. $The table parameter specifies the name of the column to change the table. $The column parameter specifies the name of the column is changed. $The new definition of type of the specified column. In the column definition can include abstract data types, such as "createTable" in the description section. The query builder will reference the table name right and the column name.

Here is an example, show 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)
addForeignKey() Method to create and add a foreign key constraints in a SQL statement execution. $The name parameter specifies the name of the foreign key. $Specify the foreign key table and column names table and $columns parameters. If there are multiple columns, they should be separated by a comma character. $References the refTable and the $refColumns parameter to specify the foreign key table and column names. $The delete and $update parameters specified in the SQL statement of ON DELETE and ON UPDATE options. Most of the DBMS is to support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL. The query builder will correctly reference table name, index and column names.

Here is an example, demonstrates 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)
dropForeignKey() Method to create and execute a deletion of a foreign key constraint SQL statement. The $name parameter specifies the name of the foreign key constraints to delete. The $table parameter specifies the name of the foreign key table. The query builder will correctly reference the table name and the name of the constraint.

Here is an example, shows how to delete 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)
createIndex() Methods to build and execute a SQL statement to create index. $The name parameter specifies the name to create the index. $The table parameter specifies the name of the table index. $The column parameter specifies the name of the column to be indexed. $The unique parameter specifies whether to create a unique index. If the index is composed of multiple columns, you must use a comma separated. The query builder will correctly reference table name, index and column names.

Here is an example, which shows how to create index:

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

dropIndex()

function dropIndex($name, $table)
dropIndex() Method to create and execute a SQL statement to delete the index. The $name parameter specifies the name that you want to delete the index. The $table parameter specifies the name of the table index. The query builder will correctly referenced table and index name.

The following is an example showing how to delete the index,:

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

Finally completed the translation~~~