Yii2.0 table join query instance analysis, yii2.0 instance analysis

Yii2.0 table join query instance analysis, yii2.0 instance analysis

This example describes how to associate a table in Yii2.0. We will share this with you for your reference. The details are as follows:

You can use ActiveRecord for associated queries (for example, when reading data from Table A, the associated table B data is also read together). In Active Record, retrieving associated data can be as simple as accessing the attributes of the primary table's ActiveRecord object.

For example, you can use $ customer-> orders to obtain an array of Order objects, representing the orders placed by the customer through appropriate relational declarations.

To declare a relation and define a getter method, this method returns a yii \ db \ ActiveQuery object with associated context information. In this way, only the matching data is queried. For example:

class Customer extends \yii\db\ActiveRecord{ public function getOrders() {  // Customer has_many Order via Order.customer_id -> id  return $this->hasMany(Order::className(), ['customer_id' => 'id']); }}class Order extends \yii\db\ActiveRecord{ // Order has_one Customer via Customer.id -> customer_id public function getCustomer() {  return $this->hasOne(Customer::className(), ['id' => 'customer_id']); }}

In the above Code, yii \ db \ ActiveRecord: hashas () and yii \ db \ ActiveRecord: hasOne () are used to model one-to-many and one-to-one associations in relational databases. For example, a customer has multiple orders, and an order owns or belongs to one user. Both methods receive two parameters and return a yii \ db \ ActiveQuery object:

$ Class: name of the class associated with the model.

$ Link: column association between two tables. This must be an array. The key of the array element is the column name of the table corresponding to $ class, and the value of the array element is the column name of the currently declared class. Defining these relationships based on foreign key associations is a good programming practice.

After completing the preceding Declaration, you can define the corresponding getter method to obtain associated data like accessing object attributes:

// get the orders of a customer$customer = Customer::findOne(1);$orders = $customer->orders; // $orders is an array of Order objects

The above Code actually executes the following two SQL queries behind the scenes, which correspond to the above two lines of code:

SELECT * FROM customer WHERE id=1;SELECT * FROM order WHERE customer_id=1;

Tip: If you access $ customer-> orders again, the above 2nd rows of SQL queries are not repeated. This query statement is executed only when the expression is accessed for the first time. Subsequent accesses will directly return internal buffer data. If you want to re-execute the query, you only need to call unset to clear the cache:

unset($customer->orders);.

Sometimes you may want to pass parameters to associated queries to limit the query conditions. For example, you only want to read large orders that exceed the specified amount, rather than all orders. To resolve this issue, you can use the following getter method to declare a bigOrders relationship:

class Customer extends \yii\db\ActiveRecord{ public function getBigOrders($threshold = 100) {  return $this->hasMany(Order::className(), ['customer_id' => 'id'])   ->where('subtotal > :threshold', [':threshold' => $threshold])   ->orderBy('id'); }}

RememberHasMany () returns an yii \ db \ ActiveQuery object. Therefore, ActiveQuery methods can be used to customize the associated query.

With the above statement, if you access $ customer-> bigOrders, it will only return orders with an amount greater than 100. If you want to specify a different limit value, use the following code:

$orders = $customer->getBigOrders(200)->all();

Note:The association method returns an yii \ db \ ActiveQuery instance. If you access it as a property, the returned data is a yii \ db \ ActiveRecord instance, an ActiveRecord array, or null ). For example, $ customer-> getOrders () returns an ActiveQuery instance, while $ customer-> orders returns an array of Order objects (or an empty array if the query result is null ).

Intermediate table join query

Sometimes, some data tables are associated with each other through an intermediate table. To declare such a relationship, we can customize the yii \ db \ ActiveQuery object by calling its via () or viaTable () method.

For example, if order and item are associated with order_item by connecting the Order table, we can declare the items relationship in the order class as follows:

class Order extends \yii\db\ActiveRecord{ public function getItems() {  return $this->hasMany(Item::className(), ['id' => 'item_id'])   ->viaTable('order_item', ['order_id' => 'id']); }}

The via () method is similar to viaTable (), but the first parameter is a relation name declared in the current ActiveRecord class, rather than the name of the intermediate table. For example, the above items relationship can also be declared using the following method:

class Order extends \yii\db\ActiveRecord{ public function getOrderItems() {  return $this->hasMany(OrderItem::className(), ['order_id' => 'id']); } public function getItems() {  return $this->hasMany(Item::className(), ['id' => 'item_id'])   ->via('orderItems'); }}