Yii Framework Official Guide Series 25--use database: Active Record

While Yii DAO can handle almost any database-related task, it is likely that we will spend 90% of our time writing SQL statements that perform common CRUD (create, read, update, and delete) operations. And it becomes difficult to maintain when we have mixed SQL statements in our code. To resolve these issues, we can use the Active Record.

Active Record (AR) is a popular object-relational mapping (ORM) technology. Each AR class represents a data table (or view), the columns of the data table (or view) are reflected in the AR class as attributes of the class, and an AR instance represents a row in the table. Common CRUD operations are implemented as an AR method. Therefore, we can access the data in a more object-oriented way. For example, we can use the following code to insert a new row into the Tbl_post table.

$post =new post; $post->title= ' sample post '; $post->content= ' post body content '; $post->save ();

Below we explain how to set up AR and perform CRUD operations through it. We'll show you how to use AR to process database relationships in the next section. For the sake of simplicity, we use the following data table as an example in this section. Note that if you use the MySQL database, you should replace AutoIncrement in the following SQL with Auto_increment.

CREATE TABLE tbl_post (    ID INTEGER not NULL PRIMARY KEY AutoIncrement,    title VARCHAR (+) not NULL,    content TE XT NOT NULL,    create_time INTEGER not NULL);

Note: AR is not intended to resolve all database-related tasks. Its best application is to model data tables for PHP structures and execute queries that do not contain complex SQL statements. For scenes of complex queries, you should use Yii DAO.

1. Establishing a database connection

AR relies on a database connection to perform database-related operations. By default, it assumes that the DB application component provides the required Cdbconnection database connection instance. The following application configuration provides an example:

Return Array (' Components    ' =>array ('        db ' =>array ('            class ' = ' system.db.CDbConnection ',            ' ConnectionString ' + ' sqlite:path/to/dbfile ',            //Open Table structure cache (schema caching) Improve performance            //' schemacachingduration ' = >3600,         ),)    ,);

Tip: Because the Active Record relies on the table's metadata (metadata) to determine column information, reading the metadata and parsing takes time. If the table structure of your database is rarely changed, you should open the table structure cache by configuring the value of the Cdbconnection::schemacachingduration property to a value greater than 0.

Support for AR is limited by the DBMS and currently supports only the following DBMS:

MySQL 4.1 or later version PostgreSQL 7.3 or later versions of SQLite 2 and 3Microsoft SQL Server 2000 or later Oracle

Note: Version 1.0.4 starts to support Microsoft SQL Server, and Oracle is supported from version 1.0.5.

If you want to use an application component that is not a db, or if you want to process multiple databases using AR, you should overwrite Cactiverecord::getdbconnection (). The Cactiverecord class is the base class for all AR classes.

Tip: There are two ways to use multiple databases through AR. If the structure of the database is different, you can create different AR base classes to implement different getdbconnection (). Otherwise, dynamic change of static variable Cactiverecord::d B is a good idea.

2. Define AR Class

To access a data table, we first need to define an AR class with an integrated Cactiverecord. Each AR class represents a single data table, and an AR instance represents a row in that table. The following example shows the simplest code for the AR class representing 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: Since the AR class is often referenced in multiple places, we can import the entire directory containing the AR class instead of importing it individually. For example, if all of our AR class files are in the Protected/models directory, we can configure the application as follows:

Return Array (    ' Import ' =>array (        ' application.models.* ',    ),);

By default, the name of the AR class is the same as the name of the data table. If different, overwrite the TableName () method. The model () method declares this for each AR class (explained later).

Info: To use the table prefix feature introduced in the 1.1.0 version the TableName () method of the AR class can be overridden by the following methods

Public Function TableName () {    return ' {{post}} ';} (then specify the prefix attribute of the DB component in the configuration file under Congfig)

This means that we will return a table name that is not prefixed by double curly braces, not the name of the complete table.

The values of columns in a data table row can be accessed as properties of the corresponding AR instance. For example, the following code sets the title column (property):

$post =new post; $post->title= ' a sample post ';

Although we have never explicitly defined property title in the Post class, we can still access it through the code above. This is because title is a column in the Tbl_post table, and Cactiverecord makes it an accessible property through the __get () Magic Method of PHP. If we try to access a nonexistent column in the same way, an exception will be thrown.

Info: In this guide, we use lowercase letters in both table and column names. This is because different DBMS handles the case differently. For example, PostgreSQL is not sensitive to the name case of a column by default, and we must enclose a mixed-case column name in quotation marks in a query condition. Using lowercase letters can help us avoid this problem.

AR relies on a well-defined primary key in the table. If a table does not have a primary key, the PrimaryKey () method must be overridden in the corresponding AR class to specify which column or columns are the primary key.

Public Function PrimaryKey () {    return ' id ';    For a composite primary key, return an array    //return Array (' PK1 ', ' PK2 ') similar to the following;}

3. Create a record

To insert a new row into a data table, we create an instance of the corresponding AR class, set its properties related to the column of the table, and then call the Save () method to complete the insertion:

$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 self-increasing, after the insert is complete, the AR instance will contain an updated primary key. In the example above, the ID attribute will reflect the primary key value of the newly inserted post, even though we have never explicitly changed it.

If a column is defined with a static default value (for example, a string, a number) in the table structure. The corresponding attribute in the AR instance will automatically contain this default value when this instance is created. One way to change this default value is to display this attribute in the AR class:

Class Post extends cactiverecord{public    $title = ' Please enter a title ';    ......} $post =new Post;echo $post->title; Here will be shown: Please enter a title

Records whose properties can be assigned a value of cdbexpression type before saving (inserting or updating) to the database. For example, to save a 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: Since AR allows us to perform database operations without having to write a large stack of SQL statements, we often wonder what SQL statements the AR executes behind the scenes. This can be achieved by turning on the logging function of Yii. For example, we've opened Cweblogroute in the app configuration, and we'll see the executed SQL statements at the end of each page.

We can set cdbconnection::enableparamlogging to true in the application configuration so that the parameter values bound in the SQL statement are also logged.

4. Reading records

To read data from a data table, we can call one of the Find series methods in the following ways:

Finds the first row in the result that meets the specified criteria $post=post::model ()->find ($condition, $params);//finds the row with the specified primary key value $post=post::model (), FINDBYPK ($postID, $condition, $params);//Finds the row $post=post::model ()->findbyattributes with the specified attribute value ($attributes, $ condition, $params);//finds the first row in the result by the specified SQL statement $post=post::model ()->findbysql ($sql, $params);

As shown above, we call the Find method through Post::model (). Keep in mind that the static method model () is required for each AR class. This method returns an AR instance in the object context that is used to access a class-level method (something similar to a static class method).

If the Find method finds a row that satisfies the query criteria, it returns a Post instance whose properties contain the values of the corresponding columns in the row of the data table. Then we can read the loaded values like the properties of a normal object, such as Echo $post->title;.

If you do not find anything in the database using the given query criteria, the Find method returns NULL.

When we call find, we use $condition and $params to specify the query criteria. Here $condition can be a WHERE string in an SQL statement, $params a parameter array where the value should be bound to a placeholder in the $condation. For example:

Find the line of postid=10 $post=post::model ()->find (' postid=:p ostid ', Array (':p ostid ' =>10));

Note: In the example above, we may need to escape the reference to the PostID column in a particular DBMS. For example, if we use PostgreSQL, we must write this expression "PostID" =:p Ostid, because PostgreSQL is not sensitive to the case of column names by default.

We can also use $condition to specify more complex query conditions. Without using strings, we can make $condition an instance of Cdbcriteria, which allows us to specify conditions that are not limited to WHERE. For example:

$criteria =new Cdbcriteria; $criteria->select= ' title '; Select only the ' title ' column $criteria->condition= ' postid=:p ostid '; $criteria->params=array (':p ostid ' =>10); $post =post: : Model ()->find ($criteria); $params don't need it.

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

One way to replace Cdbcriteria 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 standard (criterion), and the above example can be rewritten as follows:

$post =post::model ()->find (Array (    ' select ' = ' title ',    ' condition ' = ' postid=:p ostid ',    ' Params ' =>array (':p ostid ' =>10),));

Info: When a query condition is about matching several columns by a specified value, we can use Findbyattributes (). We make the $attributes parameter an array of values indexed by the column name. In some frameworks, this task can be implemented by invoking a method similar to Findbynameandtitle. While this approach may seem tempting, it often causes confusion, conflicts, and issues such as column-name-case sensitivity.

When there are multiple rows of data matching the specified query criteria, we can bring them all back through the following FindAll method. Each has its own find method, as we have already said.

Finds all rows that meet the specified criteria $posts=post::model ()->findall ($condition, $params);//finds all rows with the specified primary key $posts=post::model () FINDALLBYPK ($postIDs, $condition, $params);//finds all rows with the specified attribute value $posts=post::model ()->findallbyattributes ($ Attributes, $condition, $params);//Find All Rows $posts=post::model ()->findallbysql ($sql, $params) through the specified SQL statement;

If nothing matches the query criteria,FindAll returns an empty array. Unlike find, find returns null ifnothing is found.

In addition to the find and FindAll methods described above, for convenience, (YII) provides the following methods:

Gets the number of rows that meet the specified criteria $n=post::model ()->count ($condition, $params);//Gets the result rows by the specified SQL $n=post::model ()->countbysql ($ SQL, $params);//Check if there is at least one row of composite specified conditions $exists=post::model ()->exists ($condition, $params);

5. Update records

After the AR instance fills in the values of the columns, we can change them and save them back to the data table.

$post =post::model ()->findbypk, $post->title= ' new post title '; $post->save (); Saving changes to the database

As we can see, we use the same save () method to perform the insert and update operations. If an AR instance is created using the new operator, calling save () inserts a new row of data into the data table, and if the AR instance is the result of a Find or FindAll method, calling Save () updates the existing row in the table. In fact, we are using Cactiverecord::isnewrecord to illustrate whether an AR instance is new.

It is also possible to directly update one or more rows in a data table without first loading. AR provides the following convenient class-level methods for this purpose:

Update Rows Post::model ()->updateall ($attributes, $condition, $params) that meet the specified criteria, or update rows Post::model () that match the specified condition and primary key UPDATEBYPK ($PK, $attributes, $condition, $params);//Update the Count column for rows that meet the specified criteria Post::model ()->updatecounters ($counters, $ condition, $params);

In the preceding code, the $attributes is an array of column values indexed by the column name, $counters is an array of values that are indexed by the column names, and the $condition and $params are described in the preceding paragraphs.

6. Deleting records

If an AR instance is populated with a row of data, we can also delete this row of data.

$post =post::model ()->FINDBYPK (10); Suppose there is a post whose ID is 10$post->delete (); Delete this row from the data table

Note that after deletion, the AR instance remains the same, but the corresponding row in the datasheet is gone.

Using the following class-level code, you can delete a row without first loading it.

Delete Rows Post::model ()->deleteall ($condition, $params) that meet the specified criteria, or delete rows Post::model ()->deletebypk ($PK, $) that match the specified criteria and primary key condition, $params);

7. Data validation

When inserting or updating a row, we often need to check that the values of the columns conform to the appropriate rules. This is even more important if the value of the column is provided by the end user. In general, we can never trust any data from the client .

When save () is called, AR automatically performs data validation. Validation is based on the rule specified in the rules () method of the AR class. The following is a typical workflow that is required to save a record.

if ($post->save ()) {    //data valid and successfully inserted/updated}else{    //Data invalid, call GetErrors () Extract error message}

When the data to be inserted or updated is submitted by the end user in an HTML form, we need to assign it to the corresponding AR property. We can do this in a similar way:

$post->title=$_post[' title '; $post->content=$_post[' content ']; $post->save ();

If there are many columns, we can see a very long list for this kind of replication. This can be simplified by using the Attributes property shown below. More information can be found in the section on secure attribute assignment and the Create Action section.

Suppose $_post[' post ' is an array $post->attributes=$_post[' post ' with the value of the column-name index column value; $post->save ();

8. Compare Records

Similar to table records, AR instances are identified by their primary key values. Therefore, to compare two AR instances, assuming they belong to the same AR class, we only need to compare their primary key values. However, a simpler approach is to call Cactiverecord::equals ().

Info: 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. Accordingly, the primary key value is represented as an array in Yii. The PrimaryKey property gives the primary key value of an AR instance.

9. customizing

Cactiverecord provides several placeholder methods that can be overridden in subclasses to customize their workflow.

Beforevalidate and Aftervalidate: These two will be called before and after the AR instance is validated. BeforeSave and Aftersave: These two will be called before and after the AR instance is saved. BeforeDelete and AfterDelete: These two will be called before and after an AR instance is deleted. Afterconstruct: This will be called after each AR instance is created using the new operator. Beforefind: This will be called before an AR finder is used to execute the query (for example, find (), FindAll ()). The 1.0.9 version is now available. Afterfind: This will be called when each AR instance is created as a query result.

10. Processing Transactions using AR

Each AR instance contains a property called DbConnection, which is an instance of cdbconnection, so that we can use the transaction functionality provided by Yii DAO when needed with AR:

$model =post::model (); $transaction = $model->dbconnection->begintransaction (); try{    // Finding and saving is the two steps that may be intervened by another request    //So we use a transaction to ensure its consistency and integrity    $post = $model->findbypk (ten);    $post->title= ' new post title ';    $post->save ();        $transaction->commit ();} catch (Exception $e) {    $transaction->rollback ();}

11. Naming Ranges

Note: Support for named ranges starts with version 1.0.5. The initial idea for a named range is from Ruby on Rails.

The named range (named Scope) represents a named (named) query rule that can be used in conjunction with other named scopes and applied to an Active Record query.

The naming range is primarily declared in the Cactiverecord::scopes () method in the form of a name-rule pair. The following code declares two named ranges, published and recently, in the Post model class.

Class Post extends cactiverecord{...    Public function scopes ()    {        return Array (           ' published ' =>array (               ' condition ' = ' status=1 ',            ) ,           ' recently ' =>array (               ' order ' = ' create_time DESC ',               ' limit ' =>5,            ),        );}    }

Each named range is declared as an array that can be used to initialize the Cdbcriteria instance. For example, the recently named range specifies that the Order property is Create_time DESC, and the Limit property is 5. They will return the last 5 posts after they have been translated into a query rule.

A named range is used as a modifier for the Find method call. Several named ranges can be chained together to form a more constrained set of query results. For example, to find a recently released post, we can use the following code:

$posts =post::model ()->published ()->recently ()->findall ();

In general, a named range must appear to the left of a Find method call. Each of them provides a query rule and is federated to other rules, including the one that is passed to the Find method call. The end result is like adding a series of filters to a query.

Note: named ranges can only be used for class-level methods . In other words, this method must be called with Classname::model ().

parameterized named ranges

Named ranges can be parameterized. For example, we want to customize the number of posts specified in the recently named range, to do this, rather than declaring a named range in the Cactiverecord::scopes method, but rather defining a name and the same name as this named range:

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

We can then use the following statement to get 3 recently posted posts.

$posts =post::model ()->published ()->recently (3)->findall ();

In the above code, if we do not provide parameter 3, we will get 5 recently posted posts by default.

Default Range

A model class can have a default scope, which is applied to all queries (including related ones) about this model. For example, a Web site that supports multiple languages may want to display only the content of the language specified by the current user. Because there may be many queries about the content of this site, we can define a default scope to resolve this issue. To achieve this, we cover Cactiverecord: The:d Efaultscope method is as follows:

Class Content extends cactiverecord{public    function Defaultscope ()    {        return array (            ' condition ' = "Language= '". Yii::app ()->language. "'",        );    }

Now, if the following method is called, the query rule defined above will be used automatically:

$contents =content::model ()->findall ();

Note that the default named range is only applied to the SELECT query. Insert, UPDATE, and DELETE queries are ignored.

<p>author:qiang.xuetranslators:riverlet, Dongbetaid: $Id $</p>

The above is the Yii Framework Official Guide Series 25--use database: Active record content, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!

