Active Record

Although Yii DAO can handle virtually any database-related task, chancesare that we would spend 90% of our time in writing some SQL statementswhich perform the common CRUD (create, read, update and delete) operations.It is also difficult to maintain our code when they are mixed with SQLstatements. To solve these problems, we can use Active Record.

Active Record (AR) is a popular Object-Relational Mapping (ORM) technique.Each AR class represents a database table (or view) whose attributes arerepresented as the AR class properties, and an AR instance represents a rowin that table. Common CRUD operations are implemented as AR methods. As aresult, we can access our data in a more object-oriented way. For example,we can use the following code to insert a new row to the Post table:

  1. $post=new Post;
  2. $post->title='sample post';
  3. $post->content='post body content';
  4. $post->save();

In the following we describe how to set up AR and use it to perform CRUDoperations. We will show how to use AR to deal with database relationshipsin the next section. For simplicity, we use the following database tablefor our examples in this section. Note that if you are using MySQL database,you should replace AUTOINCREMENT with AUTO_INCREMENT in the following SQL.

  1. CREATE TABLE Post (
  2.     id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  3.     title VARCHAR(128) NOT NULL,
  4.     content TEXT NOT NULL,
  5.     createTime INTEGER NOT NULL
  6. );
Note: AR is not meant to solve all database-related tasks. It is best used for modeling database tables in PHP constructs and performing queries that do not involve complex SQLs. Yii DAO should be used for those complex scenarios.

1. Establishing DB Connection

AR relies on a DB connection to perform DB-related operations. By default,it assumes that the db application component gives the neededCDbConnection instance which serves as the DB connection. The followingapplication configuration shows an example:

  1. return array(
  2.     'components'=>array(
  3.         'db'=>array(
  4.             'class'=>'system.db.CDbConnection',
  5.             'connectionString'=>'sqlite:path/to/dbfile',
  6.             // turn on schema caching to improve performance
  7.             // 'schemaCachingDuration'=>3600,
  8.         ),
  9.     ),
  10. );
Tip: Because Active Record relies on the metadata about tables to determine the column information, it takes time to read the metadata and analyze it. If the schema of your database is less likely to be changed, you should turn on schema caching by configuring the CDbConnection::schemaCachingDuration property to be a value greater than 0.

Support for AR is limited by DBMS. Currently, only the following DBMS aresupported:

Note: The support for Microsoft SQL Server has been available since version 1.0.4; And the support for Oracle has been available since version 1.0.5.

If you want to use an application component other than db, or if youwant to work with multiple databases using AR, you should overrideCActiveRecord::getDbConnection(). The CActiveRecord class is the baseclass for all AR classes.

Tip: There are two ways to work with multiple databases in AR. If the schemas of the databases are different, you may create different base AR classes with different implementation of getDbConnection(). Otherwise, dynamically changing the static variable CActiveRecord::db is a better idea.

2. Defining AR Class

To access a database table, we first need to define an AR class byextending CActiveRecord. Each AR class represents a single databasetable, and an AR instance represents a row in that table. The followingexample shows the minimal code needed for the AR class representing thePost table.

  1. class Post extends CActiveRecord
  2. {
  3.     public static function model($className=__CLASS__)
  4.     {
  5.         return parent::model($className);
  6.     }
  7. }
Tip: Because AR classes are often referenced in many places, we can import the whole directory containing the AR class, instead of including them one by one. For example, if all our AR class files are under protected/models, we can configure the application as follows: 
  1. return array(  'import'=>array(      'application.models.*',  ),);

By default, the name of the AR class is the same as the database tablename. Override the tableName() method if theyare different. The model() method is declared assuch for every AR class (to be explained shortly).

Column values of a table row can be accessed as properties of thecorresponding AR class instance. For example, the following code sets thetitle column (attribute):

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

Although we never explicitly declare the title property in the Postclass, we can still access it in the above code. This is because title isa column in the Post table, and CActiveRecord makes it accessible as aproperty with the help of the PHP __get() magic method. An exception willbe thrown if we attempt to access a non-existing column in the same way.

Info: In this guide, we name columns using camel cases (e.g. createTime). This is because columns are accessed in the way as normal object properties which also uses camel-case naming. While using camel case does make our PHP code look more consistent in naming, it may introduce case-sensitivity problem for some DBMS. For example, PostgreSQL treats column names as case-insensitive by default, and we must quote a column in a query condition if the column contains mixed-case letters. For this reason, it may be wise to name columns (and also tables) only in lower-case letters (e.g. create_time) to avoid any potential case-sensitivity issues.

3. Creating Record

To insert a new row into a database table, we create a new instance of thecorresponding AR class, set its properties associated with the tablecolumns, and call the save() method to finish theinsertion.

  1. $post=new Post;
  2. $post->title='sample post';
  3. $post->content='content for the sample post';
  4. $post->createTime=time();
  5. $post->save();

If the table's primary key is auto-incremental, after the insertion the ARinstance will contain an updated primary key. In the above example, theid property will reflect the primary key value of the newly insertedpost, even though we never change it explicitly.

If a column is defined with some static default value (e.g. a string, anumber) in the table schema, the corresponding property in the AR instancewill automatically has such a value after the instance is created. One wayto change this default value is by explicitly declaring the property in theAR class:

  1. class Post extends CActiveRecord
  2. {
  3.     public $title='please enter a title';
  4.     ......
  5. }
  6.  
  7. $post=new Post;
  8. echo $post->title;  // this would display: please enter a title

Starting from version 1.0.2, an attribute can be assigned a value of CDbExpressiontype before the record is saved (either insertion or updating) to the database.For example, in order to save a timestamp returned by the MySQL NOW() function,we can use the following code:

  1. $post=new Post;
  2. $post->createTime=new CDbExpression('NOW()');
  3. // $post->createTime='NOW()'; will not work because
  4. // 'NOW()' will be treated as a string
  5. $post->save();
Tip: While AR allows us to perform database operations without writing cumbersom SQL statements, we often want to know what SQL statements are executed by AR underneath. This can be achieved by turning on the logging feature of Yii. For example, we can turn on CWebLogRoute in the application configuration, and we will see the executed SQL statements being displayed at the end of each Web page. Since version 1.0.5, we can set CDbConnection::enableParamLogging to be true in the application configuration so that the parameter values bound to the SQL statements are also logged.

4. Reading Record

To read data in a database table, we call one of the find methods asfollows.

  1. // find the first row satisfying the specified condition
  2. $post=Post::model()->find($condition,$params);
  3. // find the row with the specified primary key
  4. $post=Post::model()->findByPk($postID,$condition,$params);
  5. // find the row with the specified attribute values
  6. $post=Post::model()->findByAttributes($attributes,$condition,$params);
  7. // find the first row using the specified SQL statement
  8. $post=Post::model()->findBySql($sql,$params);

In the above, we call the find method with Post::model(). Rememberthat the static method model() is required for every AR class. The methodreturns an AR instance that is used to access class-level methods(something similar to static class methods) in an object context.

If the find method finds a row satisfying the query conditions, it willreturn a Post instance whose properties contain the corresponding columnvalues of the table row. We can then read the loaded values like we do withnormal object properties, for example, echo $post->title;.

The find method will return null if nothing can be found in the databasewith the given query condition.

When calling find, we use $condition and $params to specify queryconditions. Here $condition can be string representing the WHERE clausein a SQL statement, and $params is an array of parameters whose valuesshould be bound to the placeholders in $condition. For example,

  1. // find the row with postID=10
  2. $post=Post::model()->find('postID=:postID', array(':postID'=>10));
Note: In the above, we may need to escape the reference to the postID column for certain DBMS. For example, if we are using PostgreSQL, we would have to write the condition as "postID"=:postID, because PostgreSQL by default will treat column names as case-insensitive.

We can also use $condition to specify more complex query conditions.Instead of a string, we let $condition be a CDbCriteria instance, whichallows us to specify conditions other than the WHERE clause. For example,

  1. $criteria=new CDbCriteria;
  2. $criteria->select='title';  // only select the 'title' column
  3. $criteria->condition='postID=:postID';
  4. $criteria->params=array(':postID'=>10);
  5. $post=Post::model()->find($criteria); // $params is not needed

Note, when using CDbCriteria as query condition, the $params parameteris no longer needed since it can be specified in CDbCriteria, as shownabove.

An alternative way to CDbCriteria is passing an array to the find method.The array keys and values correspond to the criteria's property name and value,respectively. The above example can be rewritten as follows,

  1. $post=Post::model()->find(array(
  2.     'select'=>'title',
  3.     'condition'=>'postID=:postID',
  4.     'params'=>array(':postID'=>10),
  5. ));
Info: When a query condition is about matching some columns with the specified values, we can use findByAttributes(). We let the $attributes parameters be an array of the values indexed by the column names. In some frameworks, this task can be achieved by calling methods like findByNameAndTitle. Although this approach looks attractive, it often causes confusion, conflict and issues like case-sensitivity of column names.

When multiple rows of data matching the specified query condition, we canbring them in all together using the following findAll methods, each ofwhich has its counterpart find method, as we already described.

  1. // find all rows satisfying the specified condition
  2. $posts=Post::model()->findAll($condition,$params);
  3. // find all rows with the specified primary keys
  4. $posts=Post::model()->findAllByPk($postIDs,$condition,$params);
  5. // find all rows with the specified attribute values
  6. $posts=Post::model()->findAllByAttributes($attributes,$condition,$params);
  7. // find all rows using the specified SQL statement
  8. $posts=Post::model()->findAllBySql($sql,$params);

If nothing matches the query condition, findAll would return an emptyarray. This is different from find who would return null if nothing isfound.

Besides the find and findAll methods described above, the followingmethods are also provided for convenience:

  1. // get the number of rows satisfying the specified condition
  2. $n=Post::model()->count($condition,$params);
  3. // get the number of rows using the specified SQL statement
  4. $n=Post::model()->countBySql($sql,$params);
  5. // check if there is at least a row satisfying the specified condition
  6. $exists=Post::model()->exists($condition,$params);

5. Updating Record

After an AR instance is populated with column values, we can change themand save them back to the database table.

  1. $post=Post::model()->findByPk(10);
  2. $post->title='new post title';
  3. $post->save(); // save the change to database

As we can see, we use the same save() method toperform insertion and updating operations. If an AR instance is createdusing the new operator, calling save() would inserta new row into the database table; if the AR instance is the result of somefind or findAll method call, calling save() wouldupdate the existing row in the table. In fact, we can useCActiveRecord::isNewRecord to tell if an AR instance is new or not.

It is also possible to update one or several rows in a database tablewithout loading them first. AR provides the following convenientclass-level methods for this purpose:

  1. // update the rows matching the specified condition
  2. Post::model()->updateAll($attributes,$condition,$params);
  3. // update the rows matching the specified condition and primary key(s)
  4. Post::model()->updateByPk($pk,$attributes,$condition,$params);
  5. // update counter columns in the rows satisfying the specified conditions
  6. Post::model()->updateCounters($counters,$condition,$params);

In the above, $attributes is an array of column values indexed by columnnames; $counters is an array of incremental values indexed by columnnames; and $condition and $params are as described in the previoussubsection.

6. Deleting Record

We can also delete a row of data if an AR instance has been populated withthis row.

  1. $post=Post::model()->findByPk(10); // assuming there is a post whose ID is 10
  2. $post->delete(); // delete the row from the database table

Note, after deletion, the AR instance remains unchanged, but thecorresponding row in the database table is already gone.

The following class-level methods are provided to delete rows without theneed of loading them first:

  1. // delete the rows matching the specified condition
  2. Post::model()->deleteAll($condition,$params);
  3. // delete the rows matching the specified condition and primary key(s)
  4. Post::model()->deleteByPk($pk,$condition,$params);

7. Data Validation

When inserting or updating a row, we often need to check if the columnvalues comply to certain rules. This is especially important if the columnvalues are provided by end users. In general, we should never trustanything coming from the client side.

AR performs data validation automatically whensave() is being invoked. The validation is based onthe rules specified by in the rules() method of the AR class.For more details about how to specify validation rules, refer tothe Declaring Validation Rulessection. Below is the typical workflow needed by saving a record:

  1. if($post->save())
  2. {
  3.     // data is valid and is successfully inserted/updated
  4. }
  5. else
  6. {
  7.     // data is invalid. call getErrors() to retrieve error messages
  8. }

When the data for inserting or updating is submitted by end users in anHTML form, we need to assign them to the corresponding AR properties. Wecan do so like the following:

  1. $post->title=$_POST['title'];
  2. $post->content=$_POST['content'];
  3. $post->save();

If there are many columns, we would see a long list of such assignments.This can be alleviated by making use of theattributes property as shown below. Moredetails can be found in the Securing Attribute Assignmentssection and the Creating Action section.

  1. // assume $_POST['Post'] is an array of column values indexed by column names
  2. $post->attributes=$_POST['Post'];
  3. $post->save();

8. Comparing Records

Like table rows, AR instances are uniquely identified by their primary keyvalues. Therefore, to compare two AR instances, we merely need to comparetheir primary key values, assuming they belong to the same AR class. Asimpler way is to call CActiveRecord::equals(), however.

Info: Unlike AR implementation in other frameworks, Yii supports composite primary keys in its AR. A composite primary key consists of two or more columns. Correspondingly, the primary key value is represented as an array in Yii. The primaryKey property gives the primary key value of an AR instance.

9. Customization

CActiveRecord provides a few placeholder methods that can be overriddenin child classes to customize its workflow.

  • beforeValidate andafterValidate: these are invoked before andafter validation is performed.

  • beforeSave andafterSave: these are invoked before and aftersaving an AR instance.

  • beforeDelete andafterDelete: these are invoked before andafter an AR instance is deleted.

  • afterConstruct: this is invoked forevery AR instance created using the new operator.

  • beforeFind: this is invoked before an AR finderis used to perform a query (e.g. find(), findAll()). This has been availablesince version 1.0.9.

  • afterFind: this is invoked after every ARinstance created as a result of query.

10. Using Transaction with AR

Every AR instance contains a property nameddbConnection which is a CDbConnectioninstance. We thus can use thetransaction feature provided by YiiDAO if it is desired when working with AR:

  1. $model=Post::model();
  2. $transaction=$model->dbConnection->beginTransaction();
  3. try
  4. {
  5.     // find and save are two steps which may be intervened by another request
  6.     // we therefore use a transaction to ensure consistency and integrity
  7.     $post=$model->findByPk(10);
  8.     $post->title='new post title';
  9.     $post->save();
  10.     $transaction->commit();
  11. }
  12. catch(Exception $e)
  13. {
  14.     $transaction->rollBack();
  15. }

11. Named Scopes

Note: The support for named scopes has been available since version 1.0.5. The original idea of named scopes came 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.

Named scopes are mainly declared in the CActiveRecord::scopes() method as name-criteria pairs. The following code declares two named scopes, published and recently, in the Post model class:

  1. class Post extends CActiveRecord
  2. {
  3.     ......
  4.     public function scopes()
  5.     {
  6.         return array(
  7.             'published'=>array(
  8.                 'condition'=>'status=1',
  9.             ),
  10.             'recently'=>array(
  11.                 'order'=>'createTime DESC',
  12.                 'limit'=>5,
  13.             ),
  14.         );
  15.     }
  16. }

Each named scope is declared as an array which can be used to initialize a CDbCriteria instance. For example, the recently named scope specifies that the order property to be createTime DESC and the limit property to be 5, which translates to a query criteria that should bring back the most recent 5 posts.

Named scopes are mostly used as modifiers to the find method calls. Several named scopes may be chained together and result in a more restrictive query result set. For example, to find the recently published posts, we can use the following code:

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

In general, named scopes must appear to the left of a find method call. Each of them provides a query criteria, which is combined with other criterias, including the one passed to the find method call. The net effect is like adding a list of filters to a query.

Starting from version 1.0.6, named scopes can also be used with update and delete methods. For example, the following code would delete all recently published posts:

  1. Post::model()->published()->recently()->delete();
Note: Named scopes can only be used with class-level methods. That is, the method must be called using ClassName::model().

Parameterized Named Scopes

Named scopes can be parameterized. For example, we may want to customize the number of posts specified by the recently named scope. To do so, instead of declaring the named scope in the CActiveRecord::scopes method, we need to define a new method whose name is the same as the scope name:

  1. public function recently($limit=5)
  2. {
  3.     $this->getDbCriteria()->mergeWith(array(
  4.         'order'=>'createTime DESC',
  5.         'limit'=>$limit,
  6.     ));
  7.     return $this;
  8. }

Then, we can use the following statement to retrieve the 3 recently published posts:

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

If we do not supply the parameter 3 in the above, we would retrieve the 5 recently published posts by default.

Default Named Scope

A model class can have a default named scope that would be applied for all queries (including relational ones) about the model. For example, a website supporting multiple languages may only want to display contents that are in the language the current user specifies. Because there may be many queries about the site contents, we can define a default named scope to solve this problem. To do so, we override the CActiveRecord::defaultScope method as follows,

  1. class Content extends CActiveRecord
  2. {
  3.     public function defaultScope()
  4.     {
  5.         return array(
  6.             'condition'=>"language='".Yii::app()->language."'",
  7.         );
  8.     }
  9. }

Now, if the following method call will automatically use the query criteria as defined above:

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

Note that default named scope only applies to SELECT queries. It is ignored for INSERT, UPDATE and DELETE queries.

原文: https://www.yiichina.com/doc/guide/1.0/database.ar