YII2.0 Table Association Query instance Analysis _php instance

The example of this article describes the method of Yii2.0 Table Association query. Share to everyone for your reference, specific as follows:

You can use ActiveRecord to correlate queries (for example, to read the associated B-table data together when reading data from Table A), in an active record, Getting the associated data can be as simple as accessing the properties of the primary table ActiveRecord object.

For example, by using the appropriate relationship statement, you can use $customer->orders to get an array of order objects that represent the orders under that customer.

To declare a relationship (relation), define a getter method that returns a Yii\db\activequery object with associated context information, which will only query for relevant data that meets the criteria. Like what:

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 the 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 preceding code are used to model one-to-many and one-to-one relationships in relational databases. For example, a client customer has multiple order orders, and an order owns or belongs to a user. All two methods receive two parameters and return a Yii\db\activequery object:

$class: The class name of the associated model.

$link: A column association between two tables. It's got to be an array. The key of an array element is the column name of the table corresponding to the $class, and the value of the array element is the column name of the current declaring class. It is a good programming practice to define these relationships based on table foreign key associations.

Once you have completed the declaration, you can obtain the associated data as you would access an object property by defining the appropriate getter method:

Get the orders of a customer
$customer = Customer::findone (1);
$orders = $customer->orders; $orders is a array of order objects

The above code actually executes the following two SQL queries behind the scenes, corresponding to the two lines of code, respectively:

SELECT * from Customer WHERE id=1;
SELECT * from order WHERE customer_id=1;

Tip: If you visit $customer->orders again, the 2nd line of SQL query above will not be repeated. This query statement is executed only when the expression is first accessed. Subsequent accesses return directly to the internal buffered data. If you want to rerun the query, just call the unset first to clear the cache:

unset ($customer->orders);

Sometimes you might want to pass parameters to the associated query to qualify the query. For example, just want to read more than the specified amount of large orders, not all orders. To do this, you can declare a bigorders relationship by using the following getter method:

Class Customer extends \yii\db\activerecord
{public
 function getbigorders ($threshold = m)
 {
  return $this->hasmany (Order::classname (), [' customer_id ' => ' id '])
   ->where (' Subtotal >: Threshold ', [':] Threshold ' => $threshold])
   ->orderby (' id ');
 }

remember that the Hasmany () return object is a yii\db\activequery, so the Activequery method can be used to customize the associated query.

With the above statement, if you visit $customer->bigorders, it will only return orders that are larger than 100. If you want to specify a different qualifying value, use the following code:

$orders = $customer->getbigorders ()->all ();

Note: The association method returns a Yii\db\activequery instance. If you access it in the form of an attribute (class property), the return data is a Yii\db\activerecord instance, or a ActiveRecord array or null (NULL). For example, $customer->getorders () returns an Activequery instance, and $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 with an intermediate table (pivot table). To declare such a relationship, we can customize the Yii\db\activequery object by invoking its via () or viatable () method.

For example, if the order form and the Item table item are associated through a join table Order_item, we can declare the items relationship in the ordering 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 relationship (relation) 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 ways:

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 ');
 }

For more information on YII-related content, readers who are interested in this site can view the topics: Introduction to YII Framework and summary of common skills, "Summary of PHP Excellent development framework", "Smarty Template Introductory Course", "Introduction to PHP object-oriented programming", "PHP string" Summary of Usage , "Php+mysql Database operation Introduction Tutorial" and "PHP common database Operation Skills Summary"

I hope this article will help you with the PHP program design based on the YII framework.