YII2 Database Simple operation

1. Simple query:

One (): Returns the first record of a query based on the results of the query. All (): Returns all records based on the results of the query. COUNT (): Returns the number of records. SUM (): Returns the total number of columns specified. Average (): Returns the average of the specified column. MIN (): Returns the minimum value for the specified column. Max (): Returns the maximum value for the specified column. Scalar (): Returns the value of the first column in the first row of the query result. Column (): Returns the value of the first column in the query result. Exists (): Returns a value that indicates whether the query results have data. Where (): Add a query condition with (): The list of relationships that the query should perform. Indexby (): Query results based on the name of the indexed column. Asarray (): Returns each record in the form of an array.

Application Examples:

Customer::find ()->one ();    This method returns a piece of data; Customer::find ()->all ();    This method returns all data; Customer::find ()->count ();    This method returns the number of records; Customer::find ()->average ();    This method returns the average of the specified column; Customer::find ()->min ();    This method returns the minimum value of the specified column; Customer::find ()->max ();    This method returns the maximum value of the specified column; Customer::find ()->scalar ();    This method returns the query result for the first column of the first row of the value; Customer::find ()->column ();    This method returns the value of the first column in the query result; Customer::find ()->exists (); This method returns a value indicating whether the data row contains the query results;

Customer::find ()->asarray ()->one ();    Returns a piece of data as an array; Customer::find ()->asarray ()->all (); Returns all data in the form of an array;

Customer::find ()->where ($condition)->asarray ()->one ();    Returns a piece of data as an array according to the condition; Customer::find ()->where ($condition)->asarray ()->all (); Returns all data in the form of an array, based on conditions;

Customer::find ()->where ($condition)->asarray ()->orderby (' id DESC ')->all (); Returns all data as an array, based on the condition, and in reverse order by ID;

2. Related queries:

Activerecord::hasone (): Returns a single record of the corresponding relationship activerecord::hasmany (): Returns multiple records for the corresponding relationship

Application Examples:

Customer table model:customermodel //Order table model:ordersmodel//Country table model:countrysmodel//first to establish the relationship between table and table  // Add a relationship to an order in Customermodel       Class CustomerModel extends  yiidbactiverecord{    ...        public  Function getorders ()     {        // Customers and orders are a one-to-many relationship so use hasmany        // Here Ordersmodel at the top of Customermodel don't forget to add the corresponding namespace         // The ID corresponds to the ID field of the Ordersmodel, order_id corresponds to the order_id field of the Customermodel          return  $this->hasmany (Ordersmodel::classname (),  [' id ' = ' order_id ']);     }          public function getcountry ()      {        //the customer and the country are one-on-one relationships so use hasone        return  $this->hasone (Countrysmodel::classname (),  [' id ' = ' country_id ']);     }   &NBSP, ...}       //  inquire customers with their orders and country Customermodel::find ()->with (' Orders ',  ' Country ')->all ();//  inquires the customer with their order and Order's shipping Address customermodel::find ()->with (' orders.address ')->all ();//   Inquire customers with their country and status of 1 orders customermodel::find ()->with ([     ' orders '  =>  function  ($query)  {         $query->andwhere (' status  = 1 ');        },         ' Country ',])->all ();

Note: Orders in with are corresponding to getorders

Problems:

1. Add->select () to the query, as follows, to add order_id, that is, the associated field (for example: order_id), such as in select, otherwise an error: Undefined index order_id

Inquire customers with their orders and country Customermodel::find ()->select (' order_id ')->with (' orders ', ' Country ')->all ();

FindOne () and FindAll ():

Query for a customer with a key value of 10 $customer = Customer::findone (ten); $customer = Customer::find ()->where ([' id ' =])->one ();

The query age is 30 and the status value is 1 for the customer $customer = Customer::findone ([' age ' = ' + ', ' status ' = 1]); $customer = Customer::find () where ([' age ' = ', ' status ' = 1])->one ();

Query all customers with a key value of 10 $customers = Customer::findall (ten), $customers = Customer::find ()->where ([' id ' =])->all ( );

Query key value for 10,11,12 customer $customers = Customer::findall ([Ten, One,]), $customers = Customer::find ()->where ([' id ' = = [ Ten, one, []])->all ();

The query age is 30, all clients with a status of 1 are $customers = Customer::findall ([' ages ' = ' + ', ' status ' = 1]); $customers = Customer::find ()-& Gt;where ([' age ' = +, ' status ' = 1])->all ();

WHERE () Condition:

$customers = Customer::find ()->where ($cond)->all ();

Examples of $cond wording:

SQL: (type = 1) and (status = 2). $cond = [' type ' = = 1, ' status ' = 2]//sql: (ID in (1, 2, 3)) and (status = 2) $c ond = [' id ' = = [1, 2, 3], ' status ' = = 2]//sql:status is null$cond = [' status ' + NULL]

[[and]]: combination of different conditions, examples of usage:

SQL: ' Id=1 and id=2 ' $cond = [' and ', ' id=1 ', ' id=2 ']//sql: ' Type=1 and (id=1 OR id=2) ' $cond = [' and ', ' type=1 ', [' OR ', ' id= ' 1 ', ' id=2 ']

[[or]]:

SQL: ' (Type in (7, 8, 9) OR (ID in (1, 2, 3)) ' $cond = [' OR ', [' type ' = = = [7, 8, 9]], [' id ' = = [1, 2, 3]]

[[NOT]]:

SQL: ' Not (attribute are NULL) ' $cond = [' not ', [' attribute ' + null]]

[[between]]: not between usage same

SQL: ' ID between 1 and ' $cond = [' Between ', ' id ', 1, 10]

[[in]]: No in usage similar

SQL: ' ID in (1, 2, 3) ' $cond = [' in ', ' id ', [1, 2, 3]]//in conditions also apply to multi-field $cond = [' in ', [' id ', ' name '], [[' id ' = ' = ', ' ' name ' = > ' foo '], [' id ' = 2, ' name ' = ' + ' Bar ']]]//also applies to inline SQL statements $cond = [' in ', ' user_id ', (New Query ())->select (' id ')-> ; From (' Users ')->where ([' active ' = 1])]

[[like]]:

SQL: ' Name like '%tester% ' $cond = [' i ', ' name ', ' Tester ']//sql: ' name like '%test% ' and name '%sample% ' $cond = [ ' Like ', ' name ', [' Test ', ' sample ']]//sql: ' name like '%tester ' $cond = [' like ', ' name ', '%tester ', false]

[[exists]]: NOT EXISTS usage similar

Sql:exists (select "id" from "Users" WHERE "active" =1) $cond = [' EXISTS ', (New Query ())->select (' id ')->from (' Users ')->where ([' active ' = 1])]

In addition, you can specify any operator as follows

SQL: ' id >= ' $cond = [' >= ', ' id ', 10]//sql: ' id! = Ten ' $cond = ['! = ', ' id ', 10]

Common query:

WHERE admin_id >= LIMIT 0,10 user::find ()->select (' * ')->where ([' >= ', ' admin_id ', Ten])->offset (0)-& Gt;limit (Ten)->all ()

Select ' ID ', (SELECT COUNT (*) from ' user ') as ' count ' from ' post ' $subQuery = (new Query ())->select (' Count (*) ')-&     Gt;from (' user '); $query = (new query ())->select ([' id ', ' count ' = $subQuery])->from (' Post ');

SELECT DISTINCT ' user_id ' ... User::find ()->select (' user_id ')->distinct ();

Update:

Update ();//runvalidation Boolen whether the validate () check character defaults think true//attributenames array needs to update the field $model->update ($  Runvalidation, $attributeNames); UpdateAll ();//update Customer Set status = 1 WHERE status = 2customer::updateall ([' status ' = = 1], ' status = 2 '); Update customer Set status = 1 where status = 2 and uid = 1; Customer::updateall ([' Status ' = 1], [' status ' = ' 2 ', ' uid ' = ' 1 ']);

Delete:

$model = Customer::findone ($id); $model->delete (); $model->deleteall ([' ID ' =>1]);

BULK INSERT:

Yii:: $app->db->createcommand ()->batchinsert (Usermodel::tablename (), [' user_id ', ' username '], [[' 1 ', ' Test1 '], [' 2 ', ' test2 '], [' 3 ', ' test3 '],])->execute ();

View Execution sql

Usermodel $query = Usermodel::find ()->where ([' Status ' =>1]); echo $query->createcommand ()->getrawsql ();

YII2 Database Simple operation