Yii AR (Active record)

Although yii Dao can process any database-related tasks, it is likely that we spend 90% of the time writing some common SQL statements to execute the CRUD operation (create, read, update and delete ). At the same time, it is difficult for us to maintain a mix of these PHP and SQL statements.Code. To solve these problems, we can use active record.

Active record (AR) is a popular Object relationship ing (ORM) technology. Each ar class represents a data table (or view). Its fields act as attributes of the Ar class. An Ar instance represents a row in the table. Common crud operations are executed as AR class methods. Therefore, we can use a more object-oriented method to process our data. For example, we can use the following code to Insert a new row in the tbl_post table:

 
$ Post = new post; $ post-> Title = 'sample post'; $ post-> content = 'Post body content'; $ post-> Save ();
 

The following describes how to set an AR and use it to perform the CRUD operation. In the next section, we will show you how to use AR to process the relationships in the database. For simplicity, we use the database tables below this section as an example. Note that if you use the MySQL database, replace autoincrement with auto_increment in the following SQL statement.

 
Create Table tbl_post (ID integer not null primary key autoincrement, title varchar (128) not null, content text not null, create_time integer not null );
 

Note: Ar is not a solution for all database-related tasks. It is better to model data tables in the PHP structure and execute SQL statements that are not complex. yii Dao should be used in complex situations. To establish a database connection to AR, a database connection is required to perform database-related operations. By default, the DB Component in the application provides the cdbconnection instance as the database connection we need. The following applicationsProgramConfiguration provides an example:

 
Return array ('components' => array ('db' => array ('class' => 'System. DB. cdbconnection ', 'connectionstring' => 'sqlite: path/to/dbfile ', // turn on schema caching to improve performance // 'schemacachingduration' => 3600 ,),),);
 

Tip: Because Active record requires the table metadata to determine the field information of the data table, it takes time to read and analyze the metadata. If your database structure is relatively fixed, you should open the cache. Configure cdbconnection: schemacachingduration to a value greater than 0. Ar support is limited by the database management system. Currently, only the following database management systems are supported: MySQL 4.1 or later versions of postgresql 7.3 or later versions of SQLite 2 and 3 Microsoft SQL Server 2000 or later versions of Oracle note: microsoft SQL Server version 1.0.4 supports Oracle version 1.0.5. If you want to use other components instead of databases, or you want to use AR to access multiple databases, you should rewrite cactiverecord: getdbconnection (). The cactiverecord class is the base class of all ar classes. Tip: There are two ways to use multiple database systems in Ar mode. If the database mode is different, you can use different getdbconnection () methods to create different ar classes. Otherwise, dynamically changing the static variable cactiverecord: DB is a better idea. To use a data table, we first need to extend cactiverecord to define an AR class. Each ar class represents a database table, and each ar instance represents a row in the data table. The following code describes the minimum code required to create an AR class corresponding to the tbl_post table.

 
Class Post extends cactiverecord {public static function model ($ classname =__ class _) {return parent: Model ($ classname);} public function tablename () {return 'tbl _ post ';}}
 

Tip: Because ar classes are referenced in many places, we can import the entire directory containing ar classes instead of introducing them one by one. for example, if all our ar class files are in protected/models, we can configure them as follows:

 
Return array ('import' => array ('application. Models .*',),);
 

By default, the name of the Ar class is the same as that of the data table. if they are different, override the tablename () method. method model () is declared as such for every ar class (to be explained shortly ). info: to use the table prefix feature introduced by version 1.1.0, the Ar Method tablename () can be rewritten as follows,

 
Public Function tablename () {return '{post }}';}
 

In this case, instead of returning a complete table name, we will return the table name with the prefix removed and enclose it in the double bending brackets. A data field can be accessed as an attribute of the corresponding ar instance. for example, the following code sets the title field (attribute ):

 
$ Post = new post; $ post-> Title = 'a sample post ';
 

Although we did not explicitly declare the title attribute in the post class, we can still access it in the code above. this is because title is a field in Table tbl_post. With the help of the PHP _ Get () magic method, cactiverecord can be accessed as an attribute. if you attempt to access a field that does not exist in the same way, an exception will be thrown. information: in this Guide, all data tables and fields are in lowercase. this is because the case sensitivity of different DBMS systems is different. for example, PostgreSQL is case insensitive to field names by default, and we must quote a column in a query condition if the column contains mixed-case letters. this problem can be avoided by using the lower-case format. ar depends on the well-defined primary key of the data table. if a table does not have a primary key, the corresponding ar class needs to specify which fields should be the primary key. By Rewriting the primarykey () method,

 
Public Function primarykey () {return 'id'; // For composite primary key, return an array like the following // return array ('pk1', 'pk2 ');}
 

To Insert a new row of records to the data table, create a new ar class instance, set the attribute values corresponding to the fields, and call save () method to complete the insert.

 
$ Post = new post; $ post-> Title = 'sample post'; $ post-> content = 'content for the sample post '; $ post-> Create time = Time (); $ post-> Save ();
 

If the table's primary key is auto-incrementing, the AR instance will contain an updated primary key after insertion. in the preceding example, the property ID is mapped to the newly inserted primary key value, even if it is not explicitly changed.

If a field is defined as a static default value in Table mode (for example, a string or a number), after this ar instance is created, the corresponding properties in the instance automatically have the corresponding default value. one way to change this default value is to explicitly declare this property in the AR class:

 
Class Post extends cactiverecord {public $ Title = 'Please enter a title ';......} $ post = new post; echo $ post-> title; // This wocould display: Please enter a title
 

From version 1.0.2, an attribute can be assigned a value of the cdbexpression type before the record is saved (inserted or updated. for example, to save the timestamp returned by the MySQL now () function, we can use the following code:

 
$ Post = new post; $ post-> Create time = new cdbexpression ('now () '); // $ post-> Create time = 'now ()'; will not work because // 'now () 'will be treated as a string $ post-> Save ();
 

Tip: Ar allows us to execute database operations without having to write troublesome SQL statements. We often want to know What SQL statements are executed by AR below. this can be achieved by opening the logging feature of yii. for example, you can open cweblogroute in the application configuration. The executed SQL statement is displayed at the bottom of each page. since version 1.0.5, we can set cdbconnection: enableparamlogging to true in the application configuration so that the parameter values bound to the SQL statement are also recorded. to read data from a data table, we can call one of the following find methods:

// Find the first row satisfying the specified condition $ post = post: Model ()-> Find ($ condition, $ Params ); // find the row with the specified Primary Key $ post = post: Model ()-> findbypk ($ postid, $ condition, $ Params ); // find the row with the specified attribute values $ post = post: Model ()-> findbyattributes ($ attributes, $ condition, $ Params ); // find the first row using the specified SQL statement $ post = post: Model ()-> findbysql ($ SQL, $ Params );
 

Above, we use post: Model () to call the find method. remember that the static method model () is required for each ar class. this method returns an AR instance, which is used to perform a horizontal (similar to a static class) method ).

If the find method finds a row of records that meet the query conditions, it returns a post instance. The attributes of this instance include the field values corresponding to the table records. then we can read the loaded value as if we were accessing the attributes of a common object, for example, Echo $ post-> title ;. the find method returns NULL if no matching record is found in the database.

When you call find, we use $ condition and $ Params to specify the query conditions. $ condition can be a string that represents the where sub-statement in an SQL statement, $ Params is a parameter array, and the value should be bound to the placeholder of $ condition. for example,

// Find the row with postid = 10 $ post = post: Model ()-> Find ('postid =: postid', array (': postid' => 10 ));
 

Note: In the above example, for some DBMS, we may need to escape the reference to the postid field. for example, if PostgreSQL is used, we need to set condition to "postid" =: postid, because PostgreSQL treats field names as case-insensitive by default. you can also use $ condition to specify more complex query conditions. without a string, $ condition is used as a cdbcriteria instance, which allows us to specify conditions but not limited to where substatements. For example,

 
$ Criteria = new cdbcriteria; $ criteria-> select = 'title'; // only select the 'title' column $ criteria-> condition = 'postid =: postid '; $ criteria-> Params = array (': postid' => 10); $ post = post: Model ()-> Find ($ criteria ); // $ Params is not needed
 

Note: When cdbcriteria is used as the query condition, the $ Params parameter is no longer required because it can be specified in cdbcriteria, as shown above.

An optional cdbcriteria method is to pass an array to the find method. The keys and values of the array correspond to the attribute names and values of criteria respectively. The preceding example can be rewritten as follows,

$ Post = post: Model ()-> Find (Array ('select' => 'title', 'condition '=> 'postid =: postid ', 'params' => array (': postid' => 10 ),));
 

Information: When a query condition uses the specified value for matching certain fields, we can use findbyattributes (). Let's make $ attributes an array. The value of the array is indexed by the field name. in some frameworks, this task can be implemented by calling methods similar to findbynameandtitle. although this method looks attractive, it often causes confusion and conflict, such as the Case sensitivity of field names. when multiple rows of records meet the specified query conditions, we can use the findall method below to aggregate them, each of which has its own copy find method.

 
// Find all rows satisfying the specified condition $ posts = post: Model ()-> findall ($ condition, $ Params ); // find all rows with the specified Primary keys $ posts = post: Model ()-> findallbypk ($ postids, $ condition, $ Params ); // find all rows with the specified attribute values $ posts = post: Model ()-> findallbyattributes ($ attributes, $ condition, $ Params ); // find all rows using the specified SQL statement $ posts = post: Model ()-> findallbysql ($ SQL, $ Params );
 

If no matching record exists, findall returns an empty array. Unlike the find method, the find method returns NULL.

In addition to the find and findall methods mentioned above, the following methods can also be used for convenience:

 
// Get the number of rows satisfying the specified condition $ n = post: Model ()-> count ($ condition, $ Params ); // get the number of rows using the specified SQL statement $ n = post: Model ()-> countbysql ($ SQL, $ Params ); // check if there is at least a row satisfying the specified condition $ exists = post: Model ()-> exists ($ condition, $ Params );
 

Update records after an AR instance is filled with field values, we can change them and save them back to the data table.

 

 
$ Post = post: Model ()-> findbypk (10); $ post-> Title = 'new post title'; $ post-> Save (); // Save the change to database
 

As we can see, we use the same save () method to perform insert and update operations. if an AR instance is created using the new operator, calling save () inserts a new row into the data table. If this ar instance is called by the find or findall method, calling save () updates the existing records in the table. in fact, we can use cactiverecord: isnewrecord to check whether an AR instance is created.

Updating records in one or more rows without loading them in advance is also possible. ar provides the following convenient class-level method to implement it:

 

// Update the rows matching the specified conditionpost: Model ()-> updateall ($ attributes, $ condition, $ Params ); // update the rows matching the specified condition and primary key (s) post: Model ()-> updatebypk ($ PK, $ attributes, $ condition, $ Params ); // update counter columns in the rows satisfying the specified conditionspost: Model ()-> updatecounters ($ counters, $ condition, $ Params );
 

$ Attributes is an array whose values are indexed by field names. $ Counters is an array whose values are indexed by field names. $ condition and $ Params are described earlier. you can also delete a row of records if an AR instance is filled with this row of records.

 

 
$ Post = post: Model ()-> findbypk (10); // assuming there is a post whose ID is 10 $ post-> Delete (); // Delete the row from the database table
 

Note: after deletion, the AR Instance remains unchanged, but the corresponding table record does not exist.

The following class-level method is used to delete records without loading them in advance:

 

// Delete the rows matching the specified conditionpost: Model ()-> deleteall ($ condition, $ Params); // Delete the rows matching the specified condition and primary key (s) post: Model ()-> deletebypk ($ PK, $ condition, $ Params );
 

Data verification when inserting or updating a row of records, we often need to check whether the field value complies with the specified rules. this is especially important when the field value comes from the user. generally, we never trust the data submitted by users.

Ar automatically performs data verification when Save () is called. The verification is based on the rules specified in the rules () method in the AR class. For more information about how to specify a verification rule, see declare a verification rule. Below is a typical workflow for saving a record:

 

 
If ($ post-> Save () {// data is valid and is successfully inserted/updated} else {/data is invalid. Call geterrors () to retrieve error messages}
 

When the inserted or updated data is submitted by the user in the HTML form, we need to assign values to the AR attribute of the object. We can do this:

 

 
$ Post-> title =$ _ post ['title']; $ post-> content =$ _ post ['content']; $ post-> Save ();
 

If there are many fields, we can see a very long value assignment list. You can use the attributes attribute below to alleviate this problem. For more details, refer to the securing attribute assignments and creating action sections.

 

 
// Assume $ _ post ['post'] is an array of column values indexed by column names $ post-> attributes = $ _ post ['post']; $ post-> Save ();
 

Comparison records are similar to table records. ar instances are identified by their primary key values. therefore, to compare two ar instances, we only need to compare their primary key values, assuming they belong to the same ar class. however, a simpler method is to call cactiverecord: equals (). information: Unlike ar execution in other frameworks, yii supports multiple primary keys in Ar. A composite primary key consists of two or more fields. corresponding, the primary key value is represented as an array in yii. the primarykey attribute provides the primary key value of an AR instance. Customization cactiverecord provides some placeholder (placeholder) methods that can be used to rewrite the workflow of customizing it in the subclass. beforevalidate and aftervalidate: they are called before/after verification execution. beforesave and aftersave: they are called before/after an AR instance is saved. beforedelete and afterdelete: they are called before/after an AR instance is deleted. afterconstruct: This is called after each ar instance is created using the new operator. beforefind: it is called before an AR finder is used to execute a query (such as find (), findall ()). available from version 1.0.9. afterfind: it is called after each ar instance is created as a query result. in Ar, each ar instance contains a property named dbconnection, which is a cdbconnection instance. in this way, we can use the transaction processing features provided by yii Dao when using Ar:

 

$ Model = post: Model (); $ transaction = $ model-> dbconnection-> begintransaction (); try {// find and save are two steps which may be intervened by another request // We therefore use a transaction to ensure consistency and integrity $ post = $ model-> findbypk (10); $ post-> Title = 'new post title'; $ post-> Save (); $ transaction-> commit ();} catch (exception $ E) {$ transaction-> rollback ();}
 

Namespace (new !) Note: namespace support starts from version 1.0.5. the idea comes from Ruby on Rails. A named scope represents a named query criteria that can be combined with other named scopes and applied to an active record query.

The namespace is mainly declared in the cactiverecord: scopes () method. The format is name-criteria. The following Code declares two namespaces in the post model class: published and recently:

 

Class Post extends cactiverecord {...... public Function scopes () {return array ('hhed '=> array ('condition' => 'status = 1 ',), 'refresh' => array ('order' => 'create time desc', 'limit' => 5 ,),);}}
 

Each namespace is declared as an array used to initialize a cdbcriteria instance. For example, The namespace recently specifies the order attribute as create_time DESC and the limit attribute as 5. to translate it into a query condition, it is necessary to return the five most recently published posts.

Most namespaces are Modi used as the find method? Er. Several namespaces can be connected together to obtain a more restrictive query result set. For example, to find a recent post, we can use the following code:

 

 
$ Posts = post: Model ()-> published ()-> recently ()-> findall ();
 

Usually the namespace must appear on the left of a find method. Each of them provides a query criteria, which is combined with other criterias, including the one passed to the find method call. The net e? ECT is like adding a list? Lters to a query.

From version 1.0.6, The namespace can also use the update and delete methods. For example, the following code deletes all recent posts:

 

 
Post: Model ()-> published ()-> recently ()-> Delete ();
 

Note: The namespace can only be used as a class-level method. That is to say, this method must be called using classname: Model. A parameterized namespace (parameterized named scopes) can be parameterized. For example, we want to customize the number of posts specified by the namespace recently. To do this, instead of declaring a namespace in the cactiverecord: scopes method, we need to define a new method with the same name as the space name:

 

 
Public Function recently ($ Limit = 5) {$ this-> getdbcriteria ()-> mergewith (Array ('order' => 'create time DESC ', 'limit '=> $ limit,); return $ this ;}
 

Then, we can use the following statement to retrieve three recent posts:

 

 
$ Posts = post: Model ()-> published ()-> recently (3)-> findall ();
 

If we do not use the above parameter 3, we will retrieve five recently published content by default. By default, a model class can have a default namespace, which is applied to all queries of this model (including relational ones ). For example, a website that supports multiple languages only displays content in the language specified by the current user. Because there are a lot of queries on site content, we can define a default namespace to solve this problem. To do this, we need to rewrite the cactiverecord: defaultscope method as follows,

 

 
Class content extends cactiverecord {public function defaultscope () {return array ('condition '=> "Language = '". yii: APP ()-> language. "'",);}}
 

Now, if you call the following method, the query conditions defined above will be automatically used:

 

 
$ Contents = content: Model ()-> findall ();
 

Note that the default namespace applies only to select queries. It ignores insert, update, and delete queries.