Yii operating database _ MySQL

Yii database operation reference:

Query: https://github.com/yii2-chinesization/yii2-zh-cn/blob/master/guide-zh-CN/db-query-builder.md

Active Record mode: https://github.com/yii2-chinesization/yii2-zh-cn/blob/master/guide-zh-CN/db-active-record.md

Create a database connection

Class Customer extends ActiveRecord {//... public static function getDb () {return/Yii: $ app-> db2; // The default usage of the application component named "db2" is db }}

Declare an AR class

Namespace app/models; use yii/db/ActiveRecord; class Customer extends ActiveRecord {/*** @ return string returns the name of the data table associated with the AR class */public static function tableName () {return 'customer ';}}

Query data

// Retrieve all active customers (customers in * active * status) and sort them by their ID: $ MERS = Customer: find () -> where (['status' => Customer: STATUS_ACTIVE]) // The constant defined in Customer-> orderBy ('id')-> all (); // return the customer with ID 1: $ Customer = customer: find ()-> where (['id' => 1])-> one (); // Retrieve the number of active customers: $ count = Customer: find ()-> where (['status' => Customer: STATUS_ACTIVE])-> count (); // index the result set with the Customer ID: $ MERS = Customer: find ()-> indexBy ('id')-> all (); // $ MERS array index by ID // use the native SQL statement to retrieve the customer: $ SQL = 'select * FROM customer'; $ customers = Customer: findBySql ($ SQL) -> all ();

There are two shortcuts:findOneAndfindAll()Used to return one or moreActiveRecordInstance. The former returns the first matched instance, and the latter returns all. For example:

// Return the customer whose id is 1 $ Customer = customer: findOne (1); // return the Customer whose id is 1 and the status is * active * $ customer = Customer :: findOne (['id' => 1, 'status' => Customer: STATUS_ACTIVE,]); // return a group of customers whose IDs are 1, 2, and 3 $ MERS = Customer: findAll ([1, 2, 3]); // return all customers in the "deleted" status $ customer = Customer: findAll (['status' => Customer: STATUS_DELETED,]);

Obtain data in array form

// Retrieve the Customer information in the form of an array rather than an object: $ MERS = Customer: find ()-> asArray ()-> all (); // $ every element of MERS is a key-value pair array

Batch Data acquisition

// Extract 10 Customer information at a time. foreach (Customer: find ()-> batch (10) as $ MERS) {// $ MERS is an array of 10 or fewer Customer objects} // extracts 10 customers at a time and traverses and processes foreach (Customer: find () -> each (10) as $ customer) {// $ customer is a "Customer" object} // batch query in greedy loading mode foreach (Customer: find () -> with ('orders ')-> each () as $ customer ){}

Example:

// Insert a new customer record $ Customer = new customer (); $ customer-> name = 'James '; $ customer-> email = 'James @ example.com '; $ customer-> save (); // equivalent to $ customer-> insert (); // update existing customer records $ Customer = customer: findOne ($ id ); $ customer-> email = 'James @ example.com '; $ customer-> save (); // equivalent to $ customer-> update (); // delete existing customer records $ Customer = customer: findOne ($ id); $ customer-> delete (); // delete multiple records older than 20, customer: deleteAll ('Age>: age AND gender =: gender ', [': age' => 20 ,': gender '=> 'm']); // add 1: Customer: updateAllCounters (['age' => 1]) to the age (age) field of all customers;

Query associated data

Class Customer extends/yii/db/ActiveRecord {public function getOrders () {// the Customer and Order are placed by Order. customer_id-> id join to establish a one-to-multiple relationship return $ this-> hasvalues (Order: className (), ['customer _ id' => 'id']);} class Order extends/yii/db/ActiveRecord {// Order and Customer through Customer. id-> customer_id join to establish a one-to-one relationship public function getCustomer () {return $ this-> hasOne (Customer: className (), ['id' => 'customer _ id']);}

Join query

// Search for all orders and sort them by customer ID and Order ID, and greedily load the "customer" table $ orders = Order: find ()-> joinWith ('customer ') -> orderBy ('customer. id, order. ID')-> all (); // search for all orders including books, and load the "books" table $ orders = Order :: find ()-> innerJoinWith ('books ')-> all ();