PHP development framework YiiFramework tutorial (27) database-example of association with ActiveRecord

PHP development framework YiiFramework tutorial (27) database-example of association with ActiveRecord

We have learned how to use Active Record (AR) to obtain data from a single data table. This section describes how to use AR to connect multiple related data tables and retrieve the joined dataset.

To use relational AR, we recommend that you define the primary key-foreign key constraint in the table to be associated. These conventions can help ensure the consistency and integrity of relevant data.

This example describes how to use Active Record for multiple tables by modifying the Yii Framework Development Tutorial (25) database-Query Builder example.

Before using AR to perform join queries, we need to let AR know how an AR class is associated with another one.

The relationship between two AR classes is directly related to the relationship between the data tables represented by the AR class. From the database perspective, there are three relationships between table A and Table B: one-to-many (one-to-many, such as tbl_user and tbl_post ), one-to-one (one-to-one such as tbl_user and tbl_profile) and multiple-to-many (such as tbl_category and tbl_post ). There are four relationships in AR:

BELONGS_TO (of): if the relationship between table A and Table B is one-to-many, Table B belongs to table A (for example, Post belongs to User );

HAS_MANY (multiple tables): if the relationship between table A and Table B is one-to-many, table A has multiple B (for example, User has multiple posts );

HAS_ONE (with one): This is A special case of has_detail. A can have at most one B (for example, A User can have at most one Profile );

Many_: this corresponds to the many-to-many relationship in the database. Because most DBMS does not directly support multiple-to-multiple relationships, an association table is required to split multiple-to-multiple relationships into one-to-multiple relationships. In our sample data structure, tbl_post_category is used for this purpose. In AR terminology, many_to is a combination of BELONGS_TO and has_to. For example, a Post belongs to multiple (belongs to publish) Category, and a Category has multiple (has Publish) Post.

The relations () method in CActiveRecord must be overwritten to define the link in AR. This method returns an array of link configurations. Each array element represents a single relationship in the following format.

The following SQL Query statement is used in Query Builder.

SELECT c. FirstName, c. LastName, c. Address, c. Email
FROM customer c
INNER JOIN
Employee e
ON c. SupportRepId = e. EmployeeId
WHERE e. EmployeeId = 4 involves two tables: Employee and Customer. there is a one-to-many relationship between Employee and Customer. that is to say, an Employee can take charge of multiple customers. The relationship between Employee and Customer is has_region, and the relationship between Customer and Employee is HAS_ONE. Therefore, the following attributes can be defined:

// Customer. php
Class Customer extends CActiveRecord
{
Public static function model ($ className =__ CLASS __)
{
Return parent: model ($ className );
}

Public function tableName ()
{
Return 'customer ';
}

}

// Employee. php
Class Employee extends CActiveRecord
{
Public static function model ($ className =__ CLASS __)
{
Return parent: model ($ className );
}

Public function tableName ()
{
Return 'employee ';
}

Public function relations ()
{
Return array (
'Customer' => array (self: has_counter, 'customer', 'orortrepid '),

);
}
}

In this example, only the corresponding Customer queried by the Employee is used. Therefore, only the relations method is defined for the class. The corresponding table and foreign keys are Customer and SupportRepId.
Then modify the indexAction method of SiteController:

Public function actionIndex ()
{

$ Employee = Employee: model ()-> findByPk (4 );

$ This-> render ('index', array (
'Model' => $ employee-> MERS,

));
}

The link definition in the AR class is that each link implicitly adds an attribute to the class. After an associated query is executed, the corresponding attributes will be filled with the associated AR instance. Therefore, from $ employee-> MERS, you can query the customers record corresponding to the Employee.
The simplest way to execute an association query is to read the association attributes in an AR instance. If this attribute has not been accessed before, an association query is initialized. it associates two tables and uses the primary key of the current AR instance to filter. The query result is saved to the attribute as an associated AR class instance. This is the legendary lazy loading method, which can also be translated as delayed loading. for example, the association query is executed only when the associated object is accessed for the first time.
This example uses delayed loading, which is not efficient in some cases. If we want to obtain the authors of N posts, using this delayed loading will lead to the execution of N join queries. In this case, we should use the eager loading method instead.
The eager loading method obtains the associated AR instance while obtaining the primary AR instance. This is done by using the with method when using the find or findAll method in AR. For example:

$ Employee = Post: model ()-> with ('customer')-> findAll ();

Finally, modify the View code of the displayed result:

$ Customer)
{

Echo 'first Name: '. $ customer-> FirstName .'
';
Echo 'last Name: '. $ customer-> LastName .'
';
Echo 'address: '. $ customer-> Address .'
';
Echo 'email: '. $ customer-> Email .'
';
Echo '----------------------
';
}

?>

The case sensitivity of different data types varies with the column name. some databases are case sensitive. for the sake of security, the attributes of Customer and column definitions are case sensitive.

This example describes the most basic usage of associating Active Record. for other functions and attributes, see the Yii Chinese document. if you use a tool like CodeSmith, if the ActiveRecord code defined by the database can be automatically generated, the coding workload of programmers can be greatly reduced.

In addition, the convenience of using Active Record is at the performance cost. Generally, the performance of using Active Record is worse than that of using DAO to read and write databases. The following table shows a reference value for 200 actors and 1000 movies.