Yii AR (Active Record) database operations tutorial Detailed

Active Record (AR) is a popular Object relational mapping (ORM) technology. Each AR class represents a datasheet (or view) whose field is the property of the Ar class, and an AR instance represents a row in the table. Common CRUD operations are performed as methods of the AR class. So we can work with our data in a more object-oriented way. For example, we can insert a new row in the Tbl_post table using the following code:

The code is as follows	Copy Code
$post =new Post; $post->title= ' sample post '; $post->content= ' post body content '; $post->save ();

Below we will describe how to set up AR and use it to perform crud operations. In the next section we will show how to use AR to process relationships in a database. For the sake of simplicity, we use the database table below this section as an example. Please note that if you use the MySQL database, in the following SQL you should replace AutoIncrement for auto_increment.

The code is as follows	Copy Code
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 to resolve all database-related tasks. It is best used to model data tables and execute uncomplicated SQL statements in the PHP structure. And the Yii DAO should be used in complex situations. Establishing a database connection AR requires a database connection to perform database-related operations. By default, the DB component in the application provides a cdbconnection instance as the database connection we need. The following application configuration provides an example:

The

code is as follows	copy code
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 an active record requires table metadata to determine the field information for a datasheet,  it takes time to read and parse the metadata. If your database structure is relatively fixed, you should open the cache. The   open method is to configure the Cdbconnection::schemacachingduration property to a value greater than 0. The support of AR is limited by the database management system. Currently, only the following database management system support: MySQL 4.1 or later version PostgreSQL 7.3 or later version SQLite 2 and 3 Microsoft SQL Server 2000 or later Oracle Note: Microsoft S QL server is supported from the 1.0.4 version and is supported for Oracle 1.0.5 versions. If you want to use other components instead of db, or if you use AR to access multiple databases, you should rewrite Cactiverecord::getdbconnection (). The Cactiverecord class is the base class for all AR classes. Tip: There are two ways to use a variety of database systems in AR mode. If the schema of the database is different, you can create different AR classes with different getdbconnection (). Otherwise, dynamically changing the static variable Cactiverecord::d B is a better idea. Define AR class in order to use a data table, we first need to extend the Cactiverecord to define an AR class. Each AR class represents a database table, and each AR instance represents a row in the datasheet. The following code describes the minimum code needed to create an AR class that corresponds to a tbl_post table.

code is as follows	copy code
Class Post extends Cactiverecord {         public static function model ($c lassname=__class__)         {         Return Parent::model ($className);        }         Public Function tablename ()         {         return ' tbl_post ';        } }

Tip: Because AR classes are referenced in many places, we can import an entire directory containing AR classes, rather than introducing them individually. For example, if all our AR class files are located in Protected/models, we can configure the following:

The code is as follows	Copy Code
Return Array ( ' Import ' =>array ( ' Application.models.* ', ), );

By default, the name of the AR class is the same as the name of the datasheet. If they are different, rewrite 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,

The code is as follows	Copy Code
Public Function TableName () { Return ' {{post}} '; }

This way, instead of returning a full table name, we return the name of the table with the prefix removed and enclose it in a double bend bracket. A field of data can be accessed as a property of the corresponding AR instance. For example, the following code sets the Title field (properties):

The code is as follows	Copy Code
$post =new Post; $post->title= ' a sample post ';

Although we do not explicitly declare the title attribute in the Post class, we can still access it in the above code. This is because title is a field in table Tbl_post, and Cactiverecord can be accessed as a property with the help of the PHP __get () Magic method. If you try to access a field that does not exist in the same way, an exception is thrown. Information: In this guide, we use lowercase formatting for all data tables and fields. This is because in different DBMS, the sensitivity to the case is different. For example, PostgreSQL default is insensitive to field names, and we must quote a column in a Qu ery condition If the column contains mixed-case letters. Use lowercase format to avoid this problem. AR relies on a well defined primary key for the datasheet. If a table does not have a primary key, the corresponding AR class is required to specify which fields should be the primary key, by overriding the PrimaryKey () method,

The code is as follows	Copy Code
Public Function PrimaryKey () { return ' ID '; For composite primary key, return a array like the following Return Array (' PK1 ', ' pk2 '); }

Create a record to insert a new row into the datasheet, we create a new corresponding AR class instance, set the value of the corresponding property for the field, and call the Save () method to complete the insertion.

The code is as follows	Copy Code
$post =new Post; $post->title= ' sample post '; $post->content= ' content for the ' sample post '; $post->create time=time (); $post->save ();

If the primary key of the table is self increasing, the AR instance after insertion will contain an updated primary key. In the example above, the property ID is mapped to the newly inserted primary key value, even if we do not explicitly change it.

In a table schema, a field is defined as a static default value (such as a string, a number), and the corresponding property in the instance automatically has the corresponding default value when the AR instance is created. One way to change this default value is to explicitly declare this property in the AR class:

The code is as follows	Copy Code
Class Post extends Cactiverecord { Public $title = ' Please enter a title '; ...... } $post =new Post; Echo $post->title; This would display:please enter a title

Starting with version 1.0.2, a property can be assigned a value of 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:

The code is as follows	Copy Code
$post =new Post; $post->create time=new cdbexpression (' Now () '); $post->create time= ' Now () '; would not work because ' Now () ' would be treated as a string $post->save ();

Tip: AR allows us to perform database operations without having to write troublesome SQL statements, and we often want to know what SQL statements are executed by AR below. This can be achieved by opening the records (logging) feature of Yii. For example, we can open Cweblogroute in the application configuration and we will see that the executed SQL statement is displayed at the bottom of each page. Starting with version 1.0.5, we can also record parameter values that apply the configuration settings cdbconnection::enableparamlogging to True to bind to SQL statements. Read a record to read data from a datasheet, we can call one of the following find methods:

code is as follows

copy code

//Find the The "the" satisfying the specified condition $post =post::model ()->find ($condition, $params); br>//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 The "the" specified SQL statement $post =post::model ()->findbysql ($sql, $params);

On top, 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 that is used to access a class-level method (similar to a static class method).

If the Find method finds a row of records that satisfies the query criteria, it returns a Post instance whose properties contain the field values for the table record. Then we can read the loaded value as we access the properties of the normal object, for example, Echo $post->title;. The Find method returns null if a record is not found in the database that satisfies the condition.

When we call find, we use $condition and $params to specify the query criteria. Here $condition can be a string representing a WHERE child statement in an SQL statement, $params is an array of arguments, where the values should be bound to placeholders for the $condition. For example,

The code is as follows	Copy Code
Find the row with postid=10 $post =post::model ()->find (' postid=:p ostid ', Array (':p ostid ' =>10));

Note: In the above example, we may need to escape the reference to the PostID field for some DBMS. For example, if we use PostgreSQL, we need to write condition as "postid" =:p Ostid, because PostgreSQL treats field names as case-insensitive by default. We can also use $condition to specify more complex query criteria. Instead of using strings, we let $condition be a Cdbcriteria instance, allowing us to specify conditions without being limited to WHERE child statements. For example

The code is as follows	Copy Code
$criteria =new Cdbcriteria; $criteria->select= ' title '; Only select the ' title ' column $criteria->condition= ' postid=:p ostid '; $criteria->params=array (':p ostid ' =>10); $post =post::model ()->find ($criteria); $params is not needed

Note that when using Cdbcriteria as a query condition, the parameter $params is no longer required because it can be specified in Cdbcriteria as shown above.

An optional Cdbcriteria way is to pass an array to the Find method. The key and value of the array correspond to the property name and value of the criteria, respectively. The above example can be rewritten as follows,

The code is as follows	Copy Code
$post =post::model ()->find Array ( ' Select ' => ' title ', ' Condition ' => ' postid=:p ostid ', ' Params ' =>array (':p ostid ' =>10), ));

Information: When a query condition is about matching some fields with the specified value, we can use Findbyattributes (). We let the argument $attributes an array whose value is indexed by the field name. In some frameworks, this task can be implemented by invoking a method similar to Findbynameandtitle. While this approach may seem appealing, it often causes confusion and conflict, such as the case sensitivity of field names. When multiline records meet the specified query criteria, we can use the FindAll method below to aggregate them together, each with their own copy find method.

code is as follows

copy code

// 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); // 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 there are no records that match the criteria, FindAll returns an empty array. The Find method returns null if it is different from the Get method.

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

The code is as follows	Copy Code
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 When an AR instance is populated with field values, we can change them and save them back to the datasheet.

The code is as follows	Copy Code
$post =post::model ()->FINDBYPK (10); $post->title= ' new post title '; $post->save (); Save the change to database

If we see it, we use the same save () method to perform the insert and update operations. If an AR instance is created using the new operator, call Save () inserts a new row into the datasheet; If this AR instance is the result of some find or FindAll method calls, the call Save () updates the records that already exist in the table. In fact, we can use Cactiverecord::isnewrecord to check whether an AR instance is new.

It is also possible to update records in one or more rows of tables without loading them beforehand. AR provides the following convenient class-level (Class-level) method to implement it:

The code is as follows	Copy Code
Update the rows matching the specified condition Post::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 conditions Post::model ()->updatecounters ($counters, $condition, $params);

On top, $attributes is an array of values indexed by the field name; $counters is an array that adds a value to the index of the field name; $condition and $params have been described before. Delete a record we can also delete a row of records if an AR instance has been populated with this row record.

The code is as follows	Copy Code
$post =post::model ()->FINDBYPK (10); Assuming there is a post whose ID is 10 $post->delete (); Delete the row from the database table

Note that after the deletion, this AR instance remains unchanged, but the corresponding table record is no longer present.

The following class horizontal (Class-level) methods are used to delete records without having to load them in advance:

The code is as follows	Copy Code
Delete the rows matching the specified condition Post::model ()->deleteall ($condition, $params); Delete the rows matching the specified condition and primary key (s) Post::model ()->deletebypk ($PK, $condition, $params);

Data validation when you insert or update a row of records, we often need to check that the value of the field conforms to the specified rule. This is especially important if the value of the field comes from the user. Usually we never trust the data that the user submits.

AR automatically performs data validation when Save () is invoked. Validates the rules specified in the rules () method in the AR class. How to specify more information about validation rules, reference Declaration validation Rule Section. Here is a typical workflow for saving a record:

The code is as follows	Copy Code
if ($post->save ()) { Data is valid and is successfully inserted/updated } Else { The data is invalid. Call GetErrors () to retrieve error messages }

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

The code is as follows	Copy Code
$post->title=$_post[' title ']; $post->content=$_post[' content ']; $post->save ();

If there are a lot of fields, we can see a very long assignment list. You can use the following attributes property to mitigate. More details can be found in the Securing Attribute Assignments section and the Creating Action section.

code is as follows	copy code
//Assume $_ Post[' Post '] is a array of column values indexed by column names $post->attributes=$_post[' post '; $post->s Ave ();

Contrast records are similar to table records, and AR instances are recognized by their primary key values. So, to compare two AR instances, we just need to compare their primary key values, assuming they belong to the same AR class. However, a simpler way is to call Cactiverecord::equals (). Information: Unlike AR's execution in other frameworks, YII supports multiple primary keys in its AR. A composite primary key is composed of two or more fields. Correspondingly, the primary key value is represented as an array in Yii. The PrimaryKey property gives the primary key value of an AR instance. Customization Cactiverecord provides a number of placeholder (placeholder) methods that can be used to override the workflow in a subclass to customize it. Beforevalidate and Aftervalidate: They are invoked before/after the validation is performed. BeforeSave and Aftersave: They are invoked before/after an AR instance is saved. BeforeDelete and AfterDelete: They are invoked before/after an AR instance is deleted. Afterconstruct: This will be invoked after each AR instance is created using the new operator. Beforefind: It is invoked before an AR finder is used to execute a query (for example, find (), FindAll ()). Available from version 1.0.9. Afterfind: It is invoked after each AR instance is created as a result of a query. Using transactions in AR each AR instance contains a property named DbConnection, which is a cdbconnection instance. This allows us to use the transaction characteristics provided by the Yii DAO when using AR:

code is as follows

copy code

$model = Post::model (); $transaction = $model->dbconnection->begintransaction (); Try {    //Find and save are two steps which may is 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: Support for namespaces starts with version 1.0.5. The idea comes from Ruby on Rails. A named scope represents a named query criteria that can is combined with other named scopes and applied to an active re Cord query.

Namespaces are declared primarily in the Cactiverecord::scopes () method, and the format is Name-criteria pairs. The following code declares two namespaces in the Post model class, published and recently:

The code is as follows	Copy Code
Class Post extends Cactiverecord { ...... Public Function scopes () { Return Array ( ' Published ' =>array ( ' Condition ' => ' Status=1 ', ), ' Recently ' =>array ( ' Order ' => ' Create Time DESC ', ' Limit ' =>5, ), ); } }

Each namespace is declared as an array that is used to initialize a Cdbcriteria instance. For example, the namespace recently specifies that the order attribute is Create_time DESC, and the Limit property is 5, and the translation to a query condition is that the 5 posts that were recently published should be returned.

Namespaces are mostly used as modi?er to find methods. Several namespaces can be joined together, and the sample can get a more restrictive set of query results. For example, to find a recently published post, we can use the following code:

The code is as follows	Copy Code
$posts =post::model ()->published ()->recently ()->findall ();

Typically, namespaces must appear to the left of a Find method. Each of them provides a query criteria, which are combined with other criterias, including the one passed to the find met Hod call. The net E?ect is-like adding a list of "lters to a" query.

Starting with version 1.0.6, namespaces can also use the update and delete methods. For example, the following code deletes all recently posted posts:

The code is as follows	Copy Code
Post::model ()->published ()->recently ()->delete ();

Note: Namespaces can only be used as a class-level method. In other words, this method must use Classname::model () to invoke it. parameterized namespaces (parameterized Named scopes) namespaces can be parameterized. For example, we want to customize the number of posts specified by the namespace recently. To do this, rather than declaring the namespace in the Cactiverecord::scopes method, we need to define a new method with the same name as the space:

The code is as follows	Copy Code
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 3 recently published posts:

$posts =post::model ()->published ()->recently ()->findall () () ()) (3);
If we do not use the above parameter 3, by default we will retrieve 5 recently published content. Default namespaces a model class can have a default namespace that is applied to all queries in this model (including   relational ones). For example, a Web site that supports multiple languages only displays content in the language specified by the current user.   Because there are a lot of queries about site content, we can define a default namespace to solve this problem. To do this, we rewrite   Cactiverecord::d Efaultscope method is as follows,

The code is as follows	Copy Code
Class Content extends Cactiverecord { Public Function Defaultscope () { Return Array ( ' Condition ' => ' language= '. Yii::app ()->language. "'", ); } }

Now, calling the following method will automatically use the query criteria defined above:

The code is as follows	Copy Code
$contents =content::model ()->findall ();

Note The default namespace applies only to SELECT queries. It ignores insert,update and DELETE queries.