Yii Query Builder (Yii Lookup builder) Official guide translation

/**** query Builder translated by PHP siege Http://blog.csdn.net/phpgcs preparing query Builder preparation Qu ery Builder Building Data retrieval Queries build up a search query building and data manipulation Queries to build a database operation query building  
Schema manipulation queries constructs a data structure operation query ****/Yii Query Builder provides a way to write SQL expressions in an object-oriented way. Developers are allowed to use class methods and properties to define a separate unit in an SQL expression.  
The different units are then assembled into a legitimate SQL expression, which allows the DAO method to be invoked and executed. Here's a typical example of using Yii Query Builder to create 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 (' I  
  
D=:id ', Array (': Id ' => $id))->queryrow ();  
  
Using Yii Query Builder is best when you need to programmatically assemble an SQL statement, or based on some additional logic in your application.  
The main benefits are: 1, allowing programmatic creation of a complex SQL expression 2, automatically referencing and column names to prevent conflicts with SQL reserved keywords and special characters 3, reference parameter values when possible, and use parameter bindings to reduce the risk of SQL injection attacks.  
  
4, to provide a degree of DB Abstraction, which simplifies the migration to different DB platforms. It is not mandatory to use query Builder, in fact, if your query is simple, or directly write SQL statements to the shortcut sideWill Note: Query Builder cannot be used to modify a SQL expression query that has been customized.  
The following code does not work: $command = Yii::app ()->db->createcommand (' SELECT * from Tbl_user ');  
  
The following line won't append WHERE clause to the above SQL $command->where (' Id=:id ', Array (': Id ' => $id));  
  
  
In other words, don't mix normal SQL with Query Builder! /***** 1.  Preparing query Builder translated by PHP siege Http://blog.csdn.net/phpgcs *****/query Builder is with Cdbcommand  
Associated, the primary DB query class is defined in the DAO.  
  
To start using Query Builder, we create a Cdbcommand instance as follows: $command = Yii::app ()->db->createcommand ();  
  
We use Yii::app ()->db to get the DB connection and then create the instance with Cdbconnection::createcommand ().  
Note: Here we don't want to give CreateCommand () an entire SQL statement in DAO, but leave it blank.  
  
  
This is because we'll use the Query Builder method later to build different parts of this SQL expression. /***** 2. Building data retrieval Queries translated by PHP Siege Division Http://blog.csdn.net/phpgcs *****/Data Retrieval Quer   
  
IES refers to the SELECT SQL statements. Query Builder provides a series of methods to create a SELCET statement withoutThe same part. Because all of these methods return the Cdbcommand instance, we can invoke them by using the method chain. as follows: Select (): Specifies the select part of the Query selectdistinct (): Specifies the select part of the query and T  
Urns 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 in the query with and operator Orwhere (): Appends condition to the whe RE part of the query with OR operator join (): Appends a INNER join query fragment Leftjoin (): Appends a left outer Jo In Query Fragment Rightjoin (): Appends a right outer join query fragment CrossJoin (): Appends a cross join query fragm  ENT naturaljoin (): Appends a natural join query fragment group (): Specifies the group by part of the query has ():  Specifies the have 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 you to use this query builder methods. For simplicity, we are assume the underlying database is MySQL.  
  
Note this if you are using a other DBMS, the Table/column/value quoting shown in the examples.  
Select () function Select ($columns = ' * ') This method customizes the select part of the query.  
The parameter $columns customizes the column to be selected, either as a comma-delimited column or as an array of column names.  
Column names can contain table prefixes and/or column aliases. This method will automatically reference the column name unless a column contains the caret (meaning that the column is provided by a DB expression) look at the example://select * Select ()//select ' ID ', ' username ' Select (' I D, 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 ')) The From () function from ($tables) looks at the example://From ' Tbl_user ' (' tbl_user ')//From ' tbl_user ', ' u ', ' Publi  C '. ' 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 ', ') (sel ECT * from Tbl_profile) the Where () function where ($conditions, $params =array ()) $conditions parameter can be either one (  
 e.g. id=1) can also be a array:array in the following format (operator, OPERAND1, Operand2, ...)   
     Operator have the following: And:array (' and ', ' id=1 ', ' id=2 ') =====> id=1 and id=2.   
    Array (' and ', ' type=1 ', Array (' or ', ' id=1 ', ' id=2 ')) =====> type=1 and (id=1 or id=2).  
  
This method does not do any quoting or escaping. Or: Similar to and In:array (' in ', ' IDs ', Array (1,2,3)) =====> ID in (1,2,3).  
The method would properly quote the column name and escape values in the range.   
      
    Not In:like:operand 1 ====> a column or DB expression operand 2 ====> a string or a array Array (' Like ', ' name ', '%tester% ') =====> name like '%tester% ' array (' like ', ' name ', Array ('%test% ', '%samp Le% ')) =====> name like '%test% ' and name like '%s 'Ample% '.  
The method would properly quote the column name and escape values in the range. Not Like:or Like:or isn't like: see Example://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 ', ' I D=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 ', ' IDs ', Array (1, 2))//where ' id ' not in (1, 2) where (array (' not ', ' id ', AR Ray (1,2))//where ' name ' like '%qiang% ' where (array ("Like", ' name ', '%qiang% '))//where ' name ' like '%qiang ' and ' Name ' like '%xue ' where (Array (' as ', ' name ', Array ('%qiang ', '%xue ')))//where ' name ' like '%qiang ' or ' name ' like '%xue ' where (array (' Or like ', ' name ', Array ('%qiang ', '%xue ')))//Where ' name ' is not like '%qiang% ' where (Array (' "not Like ', ' name ', '%qiang% ')]//WHERE ' name ' not LIKE '%qiang% ' or ' name '%xue% ' where (array (' OR not ', ' name ', Array ('%qiang% ', '%xue% '))   
, we need to determine% and _.  
If the input from the user, we should also use the following code to filter out special characters to prevent them from being used as wildcards (wildcards) $keyword =$_get[' Q '];  
Escape% and _ characters $keyword =strtr ($keyword, Array ('% ' => ' \% ', ' _ ' => ' \_ ')); $command->where (' Like ', ' title ', '% '. $keyword.  
  
  
%')); Andwhere () function Andwhere ($conditions, $params =array ()) Orwhere () function Orwhere ($conditions, $params =array ( ) Order () function order ($columns) See Example://By ' name ', ' ID ' DESC (' name, id DESC ')//Orde R by ' tbl_profile '. ' Name ', ' ID ' DESC order (Array (' Tbl_profile.name ', ' ID DESC ')) limit () and offset () function Li  MIT ($limit, $offset =null) function offset ($offset) Note that some DBMS does not support limit and offset, but our Query Builder will rewrite the entire SQL  
  
Statement to simulate the functionality of limit and offset. See Example://LIMIT LIMIT//LIMIT offset LIMIT (a)/offset The () join () and its variants function join ($table, $conditions, $params =array ()) function Leftjoin ($t Able, $conditions, $params =array ()) function Rightjoin ($table, $conditions, $params =array ()) function CrossJoin ($table  
  
function Naturaljoin ($table) Note: Unlike the other query builder methods, each call join is added to the previous join. See example://Join ' Tbl_profile ' on User_id=id join (' tbl_profile ', ' user_id=id ')//left JOIN ' pub '. ' Tbl_profile ' ' P ' O N P.user_id=id and Type=1 leftjoin (' Pub.tbl_profile p ', ' P.user_id=id ' and Type=:type ', Array (': Type ' =>1)) Gro The Up () function group ($columns) looks at the example://GROUP BY ' name ', ' ID ' group (' name, id ')//GROUP BY ' Tbl_profile '. ' N  
  
Ame ', ' ID ' Group (array (' Tbl_profile.name ', ' id ')) having () a function having ($conditions, $params =array ())  
  
  
See example://Having id=1 or id=2 have (' id=1 or id=2 ')/having id=1 or id=2 have (array (' or ', ' id=1 ', ' id=2 '))  
Union () function Union ($SQL) See example:  
Union (SELECT * FROM Tbl_profile) union (' SELECT * tbl_profile ')/***** executing Queries Execute Query * * *  
/by invoking the Query builder method above, we can execute the query using the DAO method. For example: $users = Yii::app ()->db->createcommand ()->select (' * ')->from (' Tbl_user ')->queryal  
  
L ();  
  
/**** Restoration Sqls ****/cdbcommand::gettext ().  
  
$sql = Yii::app ()->db->createcommand ()->select ("*")->from (' Tbl_user ')->text;  
  
If the binding has parameters, we can also get the parameters by Cdbcommand::p Arams property.   
Other syntax building Queries sometimes building queries through a method chain is not the best choice.  
For example, the following two expressions are equivalent, assuming that the $command represents a Cdbcommand object: $command->select (Array (' ID ', ' username '));  
  
$command->select = array (' ID ', ' username ');   
  
Further Cdbconnection::createcommand () method can be used as an array parameter. as follows: $row = Yii::app ()->db->createcommand (Array (' SELECT ' => array (' ID ', ' username '), ' from ' =&gt ; ' Tbl_user ', ' where ' => ' id=:id ', ' params ' => arRay (': Id ' =>1),)->queryrow ();  
Building multiple Queries build multiple queries a Cdbcommand instance can be used several times to build several queries.  
  
Before building a new query, use the Reset () method to clear the previous settings.  
$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 translated by the PHP siege Http://blog.csdn.net/phpgcs *****/operation refers to a DB table in  
  
serting, updating and deleting data.  
  
Insert (): Inserts a row into a table update (): Updates the "Data in a" table Delete (): Deletes the data from a table Insert () function Insert ($table, $columns) See Example://Build and execute the following SQL://INSERT INTO ' TB  
    L_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 ()) See Example://Build and Exec  
    Ute 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 ()) See Example://Build and execute the following S  
  
  
QL://DELETE from ' Tbl_user ' WHERE id=:id $command->delete (' Tbl_user ', ' Id=:id ', Array (': Id ' =>1)); /******* 4. Building Schema manipulation Queries translated by PHP siege Http://blog.csdn.net/phpgcs ******/In addition to regular lookup queries and operation checks  
  
  
, there are a number of ways to build and execute SQL queries that can manipulate the structure of the database. 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 ():  
  
Drops a table column CreateIndex (): Creates a index Dropindex (): Drops an index abstract data Types type of abstraction  
  
  
Essentially a data type that is built to be compatible with different DBMS, a generic interface.  
PK:A generic primary key type, 'll is converted into int (one) not NULL auto_increment key for MySQL;  
String:string type, would be converted to varchar (255) for MySQL;  
Text:text type (long string), 'll is converted into the text for MySQL;  
Integer:integer type, 'll is converted into int (one) for MySQL;  
Float:floating number type, would be converted to float for MySQL;  
Decimal:decimal number type, would be converted to decimal for MySQL;  
Datetime:datetime type, 'll is converted into the datetime for MySQL;  
Timestamp:timestamp type, would be converted to timestamp for MySQL;  
Time:time type, would be converted to time for MySQL; Date:date type, 'll is converted into DAte for MySQL;  
Binary:binary data type, 'll is converted into a blob for MySQL;  
Boolean:boolean type, would be converted to tinyint (1) for MySQL; Money:money/currency type, 'll is converted into decimal (19,4) for MySQL.  
  
  
This type has been available since version 1.1.8. CreateTable () function createtable ($table, $columns, $options =null)//CREATE table ' Tbl_user ' (//' id ' int ( Not NULL auto_increment PRIMARY KEY,//' username ' varchar (255) NOT NULL,//' location ' point//) Engin E=innodb createtable (' Tbl_user ', array (' ID ' => ' pk ', ' username ' => ' string not NULL ', ' location  ' => ' point ',), ' Engine=innodb ') renametable () function renametable ($table, $newName)//RENAME table  ' Tbl_users ' to ' Tbl_user ' renametable (' tbl_users ', ' Tbl_user ') droptable () function droptable ($table)// The DROP TABLE ' Tbl_user ' droptable (' Tbl_user ') truncatetable () function truncatetable ($table//TRUNCATE TABLE ' Tbl_user ' truncatetable (' Tbl_user ') AddColumn () function AddColumn ($table, $column, $type)//ALTER TABLE ' tbl_user ' ADD ' email ' varchar (255) NOT null addcolumn (' tbl_user ', ' email ', ' string not NULL ' Dropcolumn () function Dropcolumn ($table, $column)//ALTER table ' tbl_user ' drop column ' location ' drop  Column (' Tbl_user ', ' Location ') renamecolumn () function Renamecolumn ($table, $name, $newName)/ALTER table ' tbl_users ' change ' name ' username ' varchar (255) Not NULL renamecolumn (' Tbl_user ', ' name ', ' username ') Alterco Lumn ()//ALTER TABLE ' tbl_user ' change ' username ' username ' varchar (255) is not NULL altercolumn (' Tbl_user ', ' Userna Me ', ' string not NULL ') Addforeignkey () function Addforeignkey ($name, $table, $columns, $refTable, $refCol Umns, $delete =null, $update =null)//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)/ALTER TABLE ' tbl_profile ' DROP FOREIGN KEY ' fk_profile_user_id ' Dropforeignkey (' fk_profile_user_id ', ' tbl_profile ') CR Eateindex () function CreateIndex ($name, $table, $column, $unique =false)//CREATE INDEX ' idx_username ' on ' Tbl_user ' (  
' username ') createindex (' Idx_username ', ' tbl_user ', ' username ') Dropindex () function Dropindex ($name, $table)  
 DROP INDEX ' idx_username ' on ' Tbl_user ' Dropindex (' idx_username ', ' Tbl_user ')