Analysis of Yii2.0 table association query

This article mainly introduces the method of Yii2.0 table association query, and analyzes the implementation method and related usage skills of association query in Yii in the form of examples. Friends in need can refer to the following

The examples of this article are described The Yii2.0 table association query method is introduced. Share it with everyone for your reference, the details are as follows:

You can use ActiveRecord to perform related queries (for example, when reading data from table A, read the associated table B data together), in Active Record , obtaining related data can be as simple as accessing the properties of the ActiveRecord object in the main table.

For example, with the appropriate relationship declaration, you can use $customer->orders to obtain an array of Order objects representing the orders placed by this customer.

To declare a relationship, define a getter method, which returns a yii\db\ActiveQuery object with associated context information, so that only relevant data that meets the conditions will be 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']);
 }
}

The yii\db\ActiveRecord::hasMany() and yii\db\ActiveRecord::hasOne() in the above code are used to model one-to-many and one-to-one in relational databases An associated relationship. 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: The class name of the associated model.

$link: Column association between two tables. This has to be an array. The keys of the array elements are the column names of the table corresponding to $class, and the values ​​of the array elements are the column names of the currently declared class. It is good programming practice to define these relationships in terms of table foreign key associations.

After completing the above declaration, you can obtain the associated data just like accessing object properties by defining the corresponding getter method:

// 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, respectively. Corresponding 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 SQL query in the second line above will not be executed repeatedly. This query statement is only executed the first time the expression is accessed. Subsequent accesses will directly return the internal buffered data. If you want to re-execute the query, just call unset first to clear the cache:

unset($customer->orders);.

Sometimes, you may want to pass parameters to the related query to limit the query conditions. For example, you only want to read large orders exceeding a specified amount, not all orders. For this purpose, 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');
 }
}

Remember The returned object of hasMany() is a yii\db\ActiveQuery, so all ActiveQuery methods can be used to customize this related 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 associated method returns a yii\db\ActiveQuery instance. If you access it as a property (class property), the returned data is a yii\db\ActiveRecord instance, an ActiveRecord array, or empty (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 empty).

Intermediate table association query

Sometimes, some data tables are associated together through an intermediate table (pivot table). In order to declare such a relationship, we can customize the yii\db\ActiveQuery object by calling its via() or viaTable() method.

For example, if the order table order and the product table item are related through the connection table order_item, 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(), However, the first parameter is a relationship name declared in the current ActiveRecord class, not the name of the intermediate table. For example, the above items relationship can also be declared in 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');
 }
}

The above is the entire content of this article. I hope it will be helpful to everyone's learning. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

Examples of form usage in Yii

About the analysis of YII related queries

The above is the detailed content of Analysis of Yii2.0 table association query. For more information, please follow other related articles on the PHP Chinese website!