Yii Framework official guide series 25-database use: ActiveRecord

Although YiiDAO can process almost any database-related tasks, it is very likely that we spend 90% of the time writing SQL statements that execute common CRUD (create, read, update, and delete) operations. And we...

Although Yii DAO can process almost any database-related tasks, it is likely that we spend 90% of the time writing some common CRUD (create, read, update, and delete) statements) the SQL statement of the operation. In addition, when SQL statements are mixed in our code, it becomes difficult to maintain them. To solve these problems, we can use Active Record.

Active Record (AR) is a popular object-relational ing (ORM) technology. Each AR class represents a data table (or view). the column of a data table (or view) is embodied as a class attribute in the AR class. an AR instance represents a row in the table. Common CRUD operations are implemented as AR methods. Therefore, we can access 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();

Next we will explain how to set the AR and use it to perform the CRUD operation. In the next section, we will show you how to use AR to process database relationships. For simplicity, we use the following data table as an example in this section. Note: If you use the MySQL database, replace AUTOINCREMENT in the following SQL statement with AUTO_INCREMENT.

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 does not need to solve all database-related tasks. Its best application is to model data tables into PHP structures and execute queries that do not contain complex SQL statements. Yii DAO should be used for complex queries.

1. establish 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 ', 'ononstring' => 'sqlite: path/to/dbfile', // enable schema caching) improve Performance // 'schemacachingduration' => 3600 ,),),);

Tip: because Active Record depends on the metadata (metadata) of the table to determine the column information, it takes time to read and parse the metadata. If the table structure of your database is rarely changed, you should enable the table structure cache by configuring the value of the CDbConnection: schemaCachingDuration attribute to a value greater than zero.

AR support is limited by DBMS. Currently, only the following DBMS types are supported:

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

Note: version 1.0.4 supports Microsoft SQL Server and version 1.0.5 supports Oracle.

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

Tip: There are two ways to use multiple databases through AR. If the database structure is different, you can create different AR base classes to implement different getDbConnection (). Otherwise, it is a good idea to dynamically change the static variable CActiveRecord: db.

2. define the AR class

To access a data table, we first need to define an AR class by integrating CActiveRecord. Each AR class represents a separate data table, and an AR instance represents a row in that table. The following example demonstrates the simplest code for representing the AR class of 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: As AR classes are often referenced in multiple places, we can import the entire directory containing AR classes instead of importing them one by one. For example, if all our AR 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 that of the data table. If different, overwrite the tableName () method. The model () method declares this for each AR class (explained later ).

Information: to use the table prefix function introduced in version 1.1.0, the tableName () method of the AR class can be overwritten as follows:

Public function tableName () {return '{post}';} (specify the database component prefix attribute in the configuration file under congfig)

That is to say, we will return a table name without a prefix enclosed by double braces, rather than the complete table name.

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

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

Although we have never explicitly defined the attribute title in the Post class, we can still access it through the above code. This is because title is a column in The tbl_post table. CActiveRecord makes it an accessible attribute through the _ get () magic method of PHP. If we try to access a non-existent column in the same way, an exception will be thrown.

Info: in this guide, lowercase letters are used in table names and column names. This is because different DBMS processes different cases in different ways. For example, PostgreSQL is not case sensitive to column names by default, and we must use quotation marks in a query condition to enclose column names in a mix of upper and lower cases. Using lower-case letters can help us avoid this problem.

AR depends on the well-defined primary key in the table. If a table does not have a primary key, you must overwrite the primaryKey () method in the corresponding AR class to specify which columns or columns are used as the primary key.

Public function primaryKey () {return 'id'; // for a composite primary key, return an array similar to the following // return array ('pk1', 'pk2 ');}

3. create a record

To insert a new row into a data table, we need to create an AR class instance, set its attributes related to the table columns, 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 auto-incrementing, the AR instance will contain an updated primary key after the insertion is complete. In the above example, the id attribute will reflect the primary key value of the newly inserted post, even if we have never explicitly changed it.

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

Class Post extends CActiveRecord {public $ title = 'Please enter a title ';......} $ post = new Post; echo $ post-> title; // displayed here: please enter a title

Before a record is saved (inserted or updated) to the database, its attribute can be assigned a value of the CDbExpression type. 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 ()'; does not work, because // 'Now () 'will be processed as a string. $ Post-> save ();

Tip: As AR allows us to execute database operations without writing a lot of SQL statements, we often want to know what SQL statements AR actually executes. This can be achieved by enabling the log function of Yii. For example, if CWebLogRoute is enabled in the application configuration, we will see the executed SQL statement at the end of each web page.

We can set CDbConnection: enableParamLogging to true in the application configuration, so that the parameter values bound to the SQL statement will also be recorded.

4. read Records

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

// Find the first row in the result that meets the specified condition $ post = Post: model ()-> find ($ condition, $ params ); // find the row with the specified primary key value $ post = Post: model ()-> findByPk ($ postID, $ condition, $ params ); // find the row with the specified attribute value $ post = Post: model ()-> findByAttributes ($ attributes, $ condition, $ params ); // use the specified SQL statement to find the first row in the result $ post = Post: model ()-> findBySql ($ SQL, $ params );

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

If the find method finds a row that meets the query conditions, it returns a Post instance. the instance attributes contain the values of the corresponding columns in the row of the data table. Then we can read the loaded value like reading the attributes of a common object, such as echo $ post-> title ;.

If nothing is found in the database using the given query conditions, the find method returns null.

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

// Find the row with postID = 10 $ post = Post: model ()-> find ('postid =: postid', array (': postid' => 10 ));

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

You can also use $ condition to specify more complex query conditions. Without a string, we can make $ condition an instance of CDbCriteria, which allows us to specify conditions not limited to WHERE. For example:

$ Criteria = new CDbCriteria; $ criteria-> select = 'title'; // select only the 'title' column $ criteria-> condition = 'postid =: postid '; $ criteria-> params = array (': postid' => 10); $ post = Post: model ()-> find ($ criteria ); // $ params no longer required

Note: When CDbCriteria is used as the query condition, the $ params parameter is no longer needed because it can be specified in CDbCriteria, as shown above.

One alternative to CDbCriteria is to pass an array to the find method. The keys and values of the array correspond to the attribute names and values of the standard (criterion). 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 is about matching several columns by specified value, we can use findByAttributes (). The $ attributes parameter is an array of values indexed by column names. In some frameworks, this task can be implemented by calling methods similar to findByNameAndTitle. Although this method looks attractive, it often causes confusion, conflicts, and case sensitivity issues such as column names.

When multiple rows match the specified query conditions, we can use the findAll method below to bring them all back. Each has its own find method, as we have already said.

// Search for all rows that meet the specified condition $ posts = Post: model ()-> findAll ($ condition, $ params ); // search for all rows with the specified primary key $ posts = Post: model ()-> findAllByPk ($ postIDs, $ condition, $ params ); // search for all rows with the specified attribute value $ posts = Post: model ()-> findAllByAttributes ($ attributes, $ condition, $ params ); // query all rows using the specified SQL statement $ posts = Post: model ()-> findAllBySql ($ SQL, $ params );

If nothing meets the query conditions, findAll returns an empty array. Unlike find, find returns null if nothing is found.

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

// Obtain the number of rows meeting the specified condition $ n = Post: model ()-> count ($ condition, $ params ); // Obtain the number of rows of results through the specified SQL $ n = Post: model ()-> countBySql ($ SQL, $ params ); // check whether at least one row of specified conditions is compound $ exists = Post: model ()-> exists ($ condition, $ params );

5. update records

After the AR instance is filled with column 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 changes to the 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 of data into the data table. if the AR instance is the result of a find or findAll method, call save () to update the existing rows in the table. In fact, we use CActiveRecord: isNewRecord to indicate whether an AR instance is new.

Directly update one or more rows in the data table without loading them first. AR provides the following convenient class-level methods for this purpose:

// Update the row Post: model ()-> updateAll ($ attributes, $ condition, $ params) that meets the specified condition and primary key:: model ()-> updateByPk ($ pk, $ attributes, $ condition, $ params); // update the count column of the row that meets the specified conditions. Post: model () -> updateCounters ($ counters, $ condition, $ params );

In the code above, $ attributes is an array containing the column value indexed by the column name; $ counters is an array of increasing values indexed by the column name; $ condition and $ params are described in the previous section.

6. delete records

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

$ Post = Post: model ()-> findByPk (10); // assume that there is a post whose ID is 10 $ post-> delete (); // delete this row from the data table

Note: after deletion, the AR instance remains unchanged, but the corresponding row in the data table is gone.

The following class-level code can be deleted without loading a line.

// Delete a row that meets the specified condition: Post: model ()-> deleteAll ($ condition, $ params); // delete the row Post that meets the specified condition and primary key :: model ()-> deleteByPk ($ pk, $ condition, $ params );

7. data verification

When inserting or updating a row, we often need to check whether the column values comply with the corresponding rules. This is even more important if the column value 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 verification. The verification is performed based on the rules specified in the rules () method of the AR class. The following are typical workflows required to save records.

If ($ post-> save () {// The data is valid and successfully inserted/Updated} else {// The data is invalid. call getErrors () to extract the 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 AR attribute. We can achieve this by using a method similar to the following:

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

If there are many columns, we can see a very long list for this replication. You can use the following attributes attribute to simplify the operation. For more information, see the security feature assignment section and create action section.

// Assume $ _ POST ['post'] is an array with the column name index column value as the value $ Post-> attributes = $ _ post ['post']; $ post-> save ();

8. comparison records

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

Information: Unlike AR execution in other frameworks, Yii supports multiple primary keys in its AR. a composite primary key consists of two or more fields. Correspondingly, the primary key value in Yii is represented as an array. primaryKey attribute, which gives the primary key value of an AR instance.

9. Custom

CActiveRecord provides several placeholder methods that can be overwritten in sub-classes to customize their workflows.

BeforeValidate and afterValidate: These two will be called before and after the AR instance is verified. 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 queries (such as find () and findAll. Version 1.0.9 is available. AfterFind: this is called when each AR instance is created as a query result.

10. use AR to process transactions

Each AR instance has a dbConnection attribute, which is a CDbConnection instance. in this way, we can use the transaction function provided by Yii DAO with AR when necessary:

$ Model = Post: model (); $ transaction = $ model-> dbConnection-> beginTransaction (); try {// search and save are two steps that may be intervened by another request // so we use a transaction to ensure its consistency and integrity $ post = $ model-> findByPk (10); $ post-> title = 'new post title'; $ post-> save (); $ transaction-> commit ();} catch (Exception $ e) {$ transaction-> rollBack ();}

11. name range

Note: The naming range is supported starting from version 1.0.5. The original idea of naming scope came from Ruby on Rails.

Named scope indicates a named (named) query rule, which can be used together with other naming ranges and applied to Active Record queries.

The naming range is declared by name-rule pair in the CActiveRecord: scopes () method. The following code defines two naming ranges in the Post model class: published and recently.

class Post extends CActiveRecord{    ......    public function scopes()    {        return array(           'published'=>array(               'condition'=>'status=1',            ),           'recently'=>array(               'order'=>'create_time DESC',               'limit'=>5,            ),        );    }}

Each name range is declared as an array that can be used to initialize the CDbCriteria instance. For example, the recently naming range specifies that the order attribute is create_time DESC, and the limit attribute is 5. After they are translated as query rules, they will return the last five posts.

The naming range is mostly used as the modifier for calling the find method. Several naming ranges can be linked together to form a more restrictive query result set. For example, to find the latest post, we can use the following code:

$posts=Post::model()->published()->recently()->findAll();

In general, the name range must appear on the left of a find method call. Each of them provides a query rule, which is connected to other rules in parallel, including the one passed to the find method call. The final result is like adding a series of filters to a query.

Note: The naming range can only be used for class-level methods. That is to say, this method must be called using ClassName: model.

Parameterized naming range

The naming range can be parameterized. For example, to customize the number of posts specified in the recently naming range, we do not declare the name range in the CActiveRecord: scopes method, instead, you need to define a method with the same name as the name in this naming range:

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 obtain three recently published posts.

$posts=Post::model()->published()->recently(3)->findAll();

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

Default range

A model class can have a default range, which will be applied to all (including related) queries about this model. For example, a website that supports multiple languages may only display the content in the language specified by the current user. Because there may be a lot of queries on the content of this website, we can define a default range to solve this problem. To achieve this, we overwrite the CActiveRecord: defaultScope method 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 automatically used:

$contents=Content::model()->findAll();

Note that the default naming range is only applicable to SELECT queries. INSERT, UPDATE, and DELETE queries are ignored.

Author: qiang.xueTranslators: riverlet, dongbetaID: $Id$

The above is the Yii Framework official guide series 25-use the database: Active Record content. For more information, please follow the PHP Chinese network (www.php1.cn )!