Database, Active Record, and Model Tricks

Exclusive offer: get 50% off this eBook here
Yii Application Development Cookbook - Second Edition

Yii Application Development Cookbook - Second Edition — Save 50%

A Cookbook covering both practical Yii application development tips and the most important Yii features with this book and ebook.

$26.99    $13.50
by Alexander Makarov | July 2013 | Web Development

In this article by Alexander Makarov, author of Yii Application Development Cookbook - Second Edition, we will cover the following topics:

  • Getting data from a database
  • Defining and using multiple DB connections
  • Using scopes to get models for different languages
  • Processing model fields with AR event-like methods
  • Applying markdown and HTML
  • Highlighting code with Yii
  • Automating timestamps
  • Setting up an author automatically
  • Implementing single table inheritance
  • Using CDbCriteria

There are three main methods to work with databases in Yii: Active Record, query builder, and direct SQL queries through DAO. All three are different in terms of syntax, features, and performance.

In this article we will learn how to work with the database efficiently, when to use models and when not to, how to work with multiple databases, how to automatically preprocess Active Record fields, and how to use powerful database criteria.

In this article we will use the Sakila sample database Version 0.8, available at the official MySQL website: http://dev.mysql.com/doc/index-other.html.

(For more resources related to this topic, see here.)

Getting data from a database

Most applications today use databases. Be it a small website or a social network, at least some parts are powered by databases. Yii introduces three ways that allow you to work with databases:

  • Active Record
  • Query builder
  • SQL via DAO

We will use all these methods to get data from the film, film_actor, and actor tables and show it in a list. We will measure the execution time and memory usage to determine when to use these methods.

Getting ready

  1. Create a new application by using yiic webapp as described in the official guide at the following URL:

    http://www.yiiframework.com/doc/guide/en/quickstart.first-app

  2. Download the Sakila database from the following URL:

    http://dev.mysql.com/doc/index-other.html

  3. Execute the downloaded SQLs; first schema then data.
  4. Configure the DB connection in protected/config/main.php to use the Sakila database.
  5. Use Gii to create models for the actor and film tables.

How to do it...

  1. We will create protected/controllers/DbController.php as follows:

    <?php class DbController extends Controller { protected function afterAction($action) { $time = sprintf('%0.5f', Yii::getLogger() ->getExecutionTime()); $memory = round(memory_get_peak_usage()/(1024*1024),2)."MB"; echo "Time: $time, memory: $memory"; parent::afterAction($action); } public function actionAr() { $actors = Actor::model()->findAll(array('with' => 'films', 'order' => 't.first_name, t.last_name, films.title')); echo '<ol>'; foreach($actors as $actor) { echo '<li>'; echo $actor->first_name.' '.$actor->last_name; echo '<ol>'; foreach($actor->films as $film) { echo '<li>'; echo $film->title; echo '</li>'; } echo '</ol>'; echo '</li>'; } echo '</ol>'; } public function actionQueryBuilder() { $rows = Yii::app()->db->createCommand() ->from('actor') ->join('film_actor', 'actor.actor_id=film_actor.actor_id') ->leftJoin('film', 'film.film_id=film_actor.film_id') ->order('actor.first_name, actor.last_name, film.title') ->queryAll(); $this->renderRows($rows); } public function actionSql() { $sql = "SELECT * FROM actor a JOIN film_actor fa ON fa.actor_id = a.actor_id JOIN film f ON fa.film_id = f.film_id ORDER BY a.first_name, a.last_name, f.title"; $rows = Yii::app()->db->createCommand($sql)->queryAll(); $this->renderRows($rows); } public function renderRows($rows) { $lastActorName = null; echo '<ol>'; foreach($rows as $row) { $actorName = $row['first_name'].' '.$row['last_name']; if($actorName!=$lastActorName){ if($lastActorName!==null){ echo '</ol>'; echo '</li>'; } $lastActorName = $actorName; echo '<li>'; echo $actorName; echo '<ol>'; } echo '<li>'; echo $row['title']; echo '</li>'; } echo '</ol>'; } }

    Here, we have three actions corresponding to three different methods of getting data from a database.

  2. After running the preceding db/ar, db/queryBuilder and db/sql actions, you should get a tree showing 200 actors and 1,000 films they have acted in, as shown in the following screenshot:

  3. At the bottom there are statistics that give information about the memory usage and execution time. Absolute numbers can be different if you run this code, but the difference between the methods used should be about the same:

    Method

    Memory usage (megabytes)

    Execution time (seconds)

    Active Record

    19.74

    1.14109

    Query builder

    17.98

    0.35732

    SQL (DAO)

    17.74

    0.35038

How it works...

Let's review the preceding code.

The actionAr action method gets model instances by using the Active Record approach. We start with the Actor model generated with Gii to get all the actors and specify 'with' => 'films' to get the corresponding films using a single query or eager loading through relation, which Gii builds for us from InnoDB table foreign keys. We then simply iterate over all the actors and for each actor—over each film. Then for each item, we print its name.

The actionQueryBuilder function uses query builder. First, we create a query command for the current DB connection with Yii::app()->db->createCommand(). We then add query parts one by one with from, join, and leftJoin. These methods escape values, tables, and field names automatically. The queryAll function returns an array of raw database rows. Each row is also an array indexed with result field names. We pass the result to renderRows, which renders it.

With actionSql, we do the same, except we pass SQL directly instead of adding its parts one by one. It's worth mentioning that we should escape parameter values manually with Yii::app()->db->quoteValue before using them in the query string.

The renderRows function renders the query builder. The DAO raw row requires you to add more checks and generally, it feels unnatural compared to rendering an Active Record result.

As we can see, all these methods give the same result in the end, but they all have different performance, syntax, and extra features. We will now do a comparison and figure out when to use each method:

Method

Active Record

Query Builder

SQL (DAO)

Syntax

This will do SQL for you.

Gii will generate models and relations for you.

Works with models, completely OO-style, and very clean API.

Produces array of properly nested models as the result.

Clean API, suitable for building query on the fly.

Produces raw data arrays as the result.

Good for complex SQL.

Manual values and keywords quoting.

Not very suitable for building query on the fly.

Produces raw data arrays as results.

Performance

Higher memory usage and execution time compared to SQL and query builder.

Okay.

Okay.

Extra features

Quotes values and names automatically.

Behaviors.

Before/after hooks.

Validation.

Quotes values and names automatically.

None.

Best for

Prototyping selects.

Update, delete, and create actions for single models (model gives a huge benefit when using with forms).

Working with large amounts of data, building queries on the fly.

Complex queries you want to do with pure SQL and have maximum possible performance.

There's more...

In order to learn more about working with databases in Yii, refer to the following resources:

See also

  • The Using CDbCriteria recipe

Defining and using multiple DB connections

Multiple database connections are not used very often for new standalone web applications. However, when you are building an add-on application for an existing system, you will most probably need another database connection.

From this recipe you will learn how to define multiple DB connections and use them with DAO, query builder, and Active Record models.

Getting ready

  1. Create a new application by using yiic webapp as described in the official guide at the following URL:

    http://www.yiiframework.com/doc/guide/en/quickstart.first-app

  2. Create two MySQL databases named db1 and db2.
  3. Create a table named post in db1 as follows:

    DROP TABLE IF EXISTS `post`; CREATE TABLE IF NOT EXISTS `post` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `text` TEXT NOT NULL, PRIMARY KEY (`id`) );

  4. Create a table named comment in db2 as follows:

    DROP TABLE IF EXISTS `comment`; CREATE TABLE IF NOT EXISTS `comment` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `text` TEXT NOT NULL, `postId` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`) );

How to do it...

  1. We will start with configuring the DB connections. Open protected/config/main.php and define a primary connection as described in the official guide:

    'db'=>array( 'connectionString' => 'mysql:host=localhost;dbname=db1', 'emulatePrepare' => true, 'username' => 'root', 'password' => '', 'charset' => 'utf8', ),

  2. Copy it, rename the db component to db2, and change the connection string accordingly. Also, you need to add the class name as follows:

    'db2'=>array( 'class'=>'CDbConnection', 'connectionString' => 'mysql:host=localhost;dbname=db2', 'emulatePrepare' => true, 'username' => 'root', 'password' => '', 'charset' => 'utf8', ),

  3. That is it. Now you have two database connections and you can use them with DAO and query builder as follows:

    $db1Rows = Yii::app()->db->createCommand($sql)->queryAll(); $db2Rows = Yii::app()->db2->createCommand($sql)->queryAll();

  4. Now, if we need to use Active Record models, we first need to create Post and Comment models with Gii. Starting from Yii version 1.1.11, you can just select an appropriate connection for each model.

    Now you can use the Comment model as usual. Create protected/controllers/DbtestController.php as follows:

    <?php class DbtestController extends CController { public function actionIndex() { $post = new Post(); $post->title = "Post #".rand(1, 1000); $post->text = "text"; $post->save(); echo '<h1>Posts</h1>'; $posts = Post::model()->findAll(); foreach($posts as $post) { echo $post->title."<br />"; } $comment = new Comment(); $comment->postId = $post->id; $comment->text = "comment #".rand(1, 1000); $comment->save(); echo '<h1>Comments</h1>'; $comments = Comment::model()->findAll(); foreach($comments as $comment) { echo $comment->text."<br />"; } } }

  5. Run dbtest/index multiple times and you should see records added to both databases, as shown in the following screenshot:

How it works...

In Yii you can add and configure your own components through the configuration file. For non-standard components, such as db2, you have to specify the component class. Similarly, you can add db3, db4, or any other component, for example, facebookApi. The remaining array key/value pairs are assigned to the component's public properties respectively.

There's more...

Depending on the RDBMS used, there are additional things we can do to make it easier to use multiple databases.

Cross-database relations

If you are using MySQL, it is possible to create cross-database relations for your models. In order to do this, you should prefix the Comment model's table name with the database name as follows:

class Comment extends CActiveRecord { //… public function tableName() { return 'db2.comment'; } //… }

Now, if you have a comments relation defined in the Post model relations method, you can use the following code:

$posts = Post::model()->with('comments')->findAll();

Further reading

For further information, refer to the following URL:

http://www.yiiframework.com/doc/api/CActiveRecord

See also

  • The Getting data from a database recipe

Using scopes to get models for different languages

Internationalizing your application is not an easy task. You need to translate interfaces, translate messages, format dates properly, and so on. Yii helps you to do this by giving you access to the Common Locale Data Repository ( CLDR ) data of Unicode and providing translation and formatting tools. When it comes to applications with data in multiple languages, you have to find your own way.

From this recipe, you will learn a possible way to get a handy model function that will help to get blog posts for different languages.

Getting ready

  1. Create a new application by using yiic webapp as described in the official guide at the following URL:

    http://www.yiiframework.com/doc/guide/en/quickstart.first-app

  2. Set up the database connection and create a table named post as follows:

    DROP TABLE IF EXISTS `post`; CREATE TABLE IF NOT EXISTS `post` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `lang` VARCHAR(5) NOT NULL DEFAULT 'en', `title` VARCHAR(255) NOT NULL, `text` TEXT NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO `post`(`id`,`lang`,`title`,`text`) VALUES (1,'en_us','Yii news','Text in English'), (2,'de','Yii Nachrichten','Text in Deutsch');

  3. Generate a Post model using Gii.

How to do it...

  1. Add the following methods to protected/models/Post.php as follows:

    class Post extends CActiveRecord { public function defaultScope() { return array( 'condition' => "lang=:lang", 'params' => array( ':lang' => Yii::app()->language, ), ); } public function lang($lang){ $this->getDbCriteria()->mergeWith(array( 'condition' => "lang=:lang", 'params' => array( ':lang' => $lang, ), )); return $this; } }

  2. That is it. Now, we can use our model. Create protected/controllers/ DbtestController.php as follows:

    <?php class DbtestController extends CController { public function actionIndex() { // Get posts written in default application language $posts = Post::model()->findAll(); echo '<h1>Default language</h1>'; foreach($posts as $post) { echo '<h2>'.$post->title.'</h2>'; echo $post->text; } // Get posts written in German $posts = Post::model()->lang('de')->findAll(); echo '<h1>German</h1>'; foreach($posts as $post) { echo '<h2>'.$post->title.'</h2>'; echo $post->text; } } }

  3. Now, run dbtest/index and you should get an output similar to the one shown in the following screenshot:

How it works...

We have used Yii's Active Record scopes in the preceding code. The defaultScope function returns the default condition or criteria that will be applied to all the Post model query methods. As we need to specify the language explicitly, we create a scope named lang, which accepts the language name. With $this->getDbCriteria(), we get the model's criteria in its current state and then merge it with the new condition. As the condition is exactly the same as in defaultScope, except for the parameter value, it overrides the default scope.

In order to support chained calls, lang returns the model instance by itself.

There's more...

For further information, refer to the following URLs:

See also

  • The Getting data from a database recipe
  • The Using CDbCriteria recipe

Processing model fields with AR event-like methods

Active Record implementation in Yii is very powerful and has many features. One of these features is event-like methods , which you can use to preprocess model fields before putting them into the database or getting them from a database, as well as deleting data related to the model, and so on.

In this recipe, we will linkify all URLs in the post text and we will list all existing Active Record event-like methods.

Getting ready

  1. Create a new application by using yiic webapp as described in the official guide at the following URL:

    http://www.yiiframework.com/doc/guide/en/quickstart.first-app

  2. Set up a database connection and create a table named post as follows:

    DROP TABLE IF EXISTS `post`; CREATE TABLE IF NOT EXISTS `post` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `text` TEXT NOT NULL, PRIMARY KEY (`id`) );

  3. Generate the Post model using Gii

How to do it...

  1. Add the following method to protected/models/Post.php as follows:

    protected function beforeSave() { $this->text = preg_replace('~((?:https?|ftps?)://.*?)( |$)~iu',
    '<a href="\1">\1</a>\2', $this->text); return parent::beforeSave(); }

  2. That is it. Now, try saving a post containing a link. Create protected/controllers/TestController.php as follows:

    <?php class TestController extends CController { function actionIndex() { $post=new Post(); $post->title='links test'; $post->text='test http://www.yiiframework.com/ test'; $post->save(); print_r($post->text); } }

  3. Run test/index. You should get the following:

How it works...

The beforeSave method is implemented in the CActiveRecord class and executed just before saving a model. By using a regular expression, we replace everything that looks like a URL with a link that uses this URL and call the parent implementation, so that real events are raised properly. In order to prevent saving, you can return false.

There's more...

There are more event-like methods available as shown in the following table:

Method name

Description

afterConstruct

Called after a model instance is created by the new operator

beforeDelete/afterDelete

Called before/after deleting a record

beforeFind/afterFind

Method is invoked before/after each record is instantiated by a find method

beforeSave/afterSave

Method is invoked before/after saving a record successfully

beforeValidate/afterValidate

Method is invoked before/after validation ends

Further reading

In order to learn more about using event-like methods in Yii, you can refer to the following URLs:

See also

  • The Using Yii events recipe 
  • The Highlighting code with Yii recipe
  • The Automating timestamps recipe
  • The Setting up an author automatically recipe
Yii Application Development Cookbook - Second Edition A Cookbook covering both practical Yii application development tips and the most important Yii features with this book and ebook.
Published: April 2013
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

Applying markdown and HTML

When we create web applications, we will certainly have to deal with creating content. Of course, we can create it with pure text or HTML, but text is often too simple and HTML is too complex and insecure. That is why special markup languages, such as BBCode, Textile, and markdown are used.

In this recipe, we will learn how to create a model that will automatically convert markdown to HTML when it is being saved.

Getting ready

  1. Create a new application by using yiic webapp as described in the official guide at the following URL:

    http://www.yiiframework.com/doc/guide/en/quickstart.first-app

  2. Set up a database connection and create a table named post as follows:

    DROP TABLE IF EXISTS `post`; CREATE TABLE IF NOT EXISTS `post` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `text` TEXT NOT NULL, `html` TEXT NOT NULL, PRIMARY KEY (`id`) );

  3. Generate the Post model using Gii.

How to do it...

  1. Open the protected/models/Post.php file and add the following method:

    protected function beforeValidate() { $parser=new CMarkdownParser(); $this->html=$parser->transform($this->text); return parent::beforeValidate(); }

  2. Now the Post model can be used transparently. Create protected/controllers/TestController.php as follows:

    <?php class TestController extends CController { function actionIndex() { $post = new Post(); $post->title = "I promise to share my opinion on Yii framework"; $post->text = "Recently I've started using [Yii framework](http://www.yiiframework.com/) and definitely will share my opinion as soon as I'll have some more free time."; $post->save(); echo "<h1>$post->title</h1>"; echo $post->html; } }

  3. That is it. Now, run test/index. You should get the following:

The text marked up with markdown that you have set for the text value will be automatically converted to HTML, ready to be displayed, and will be saved in the html database field. Therefore, html should be used at the display post screen and the markdown text should be used at the create post or edit post screens.

How it works...

In the preceding code we override CActiveRecord::beforeValidate to preprocess the data we have obtained from the user input. This method is executed just before the validation that is called when we use $post->save().

Yii includes a wrapper around the PHP Markdown Extra markdown parser. The CMarkdownParser function is used mainly in the Yii documentation and we can surely use it in our applications.

Converting text from one format to another requires more CPU and memory resources, so should be avoided if possible. That is why we are not applying markdown on the fly, and instead only doing it one time when saving a post.

When we edit post, we need to get the markdown source somehow. For this reason, we save both the markdown source and the produced HTML into a database. Alternatively, we can use a markdown parser on viewing post and cache results until post is altered.

There's more...

In order to learn more about markdown and how it can be used to build the Yii documentation, you can refer to the following resources:

Markdown syntax

Yii markdown wrapper and usage

See also

  • The Processing model fields with AR event-like methods recipe
  • The Automating timestamps recipe
  • The Setting up an author automatically recipe

Highlighting code with Yii

If you are posting code, be it a company's internal wiki or a public developer's blog, it is always better to have the syntax highlighted, so users who read the code will feel comfortable. Yii has PEAR's Text_Highlighter code-highlighting class bundled. It is used to highlight Yii definitive guide examples, and we can use it to do the same for our application.

In this recipe, we will create a simple application that will allow for adding, editing, and viewing code snippets.

Getting ready

  1. Create a new application by using yiic webapp as described in the official guide at the following URL:

    http://www.yiiframework.com/doc/guide/en/quickstart.first-app

  2. Set up a database connection and create a table named snippet as follows:

    CREATE TABLE `snippet` ( `id` int(11) unsigned NOT NULL auto_increment, `title` varchar(255) NOT NULL, `code` text NOT NULL, `html` text NOT NULL, `language` varchar(20) NOT NULL, PRIMARY KEY (`id`) );

  3. Generate a Snippet model by using Gii.

How to do it...

  1. First, we will tweak the protected/models/Snippet.php model code. Change the rules method to the following:

    public function rules() { return array( array('title, code, language', 'required'), array('title', 'length', 'max'=>255), array('language', 'length', 'max' => 20), ); }

  2. Add methods to the same Snippet model:

    protected function afterValidate() { $highlighter = new CTextHighlighter(); $highlighter->language = $this->language; $this->html = $highlighter->highlight($this->code); return parent::afterValidate(); } public function getSupportedLanguages() { return array( 'php' => 'PHP', 'css' => 'CSS', 'html' => 'HTML', 'javascript' => 'JavaScript', ); }

  3. The model is ready. Now, we will create a controller. Therefore, create protected/controllers/SnippetController.php as follows:

    <?php class SnippetController extends CController { public function actionIndex() { $criteria = new CDbCriteria(); $criteria->order = 'id DESC'; $models = Snippet::model()->findAll($criteria); $this->render('index', array( 'models' => $models, )); } public function actionView($id) { $model = Snippet::model()->findByPk($id); if(!$model) throw new CException(404); $this->render('view', array( 'model' => $model, )); } public function actionAdd() { $model = new Snippet(); $data = Yii::app()->request->getPost('Snippet'); if($data) { $model->setAttributes($data); if($model->save()) $this->redirect(array('view', 'id' => $model->id)); } $this->render('add', array( 'model' => $model, )); } public function actionEdit($id){ $model = Snippet::model()->findByPk($id); if(!$model) throw new CHttpException(404); $data = Yii::app()->request->getPost('Snippet'); if($data) { $model->setAttributes($data); if($model->save()) $this->redirect(array('view', 'id' => $model->id)); } $this->render('edit', array( 'model' => $model, )); } }

  4. Now views; create protected/views/snippet/index.php as follows:

    <h2>Snippets</h2> <?php echo CHtml::link('Add snippet', array('add'))?> <ol> <?php foreach($models as $model):?> <li> <?php echo CHtml::link( CHtml::encode($model->title), array('view', 'id' => $model->id) )?> </li> <?php endforeach?> </ol>

  5. Create protected/views/snippet/view.php as follows:

    <h2><?php echo CHtml::link('Snippets', array('index'))?> → <?php echo CHtml::encode($model->title)?> </h2> <?php echo CHtml::link('Edit', array ('edit', 'id' => $model->id))?> <div> <?php echo $model->html?> </div>

  6. Create protected/views/snippet/add.php as follows:

    <h2><?php echo CHtml::link('Snippets', array('index'))?> → Add snippet </h2> <?php $this->renderPartial('_form', array('model' => $model))?>

  7. Create protected/views/snippet/edit.php as follows:

    <h2><?php echo CHtml::link('Snippets', array('index'))?> → Edit snippet </h2> <?php $this->renderPartial('_form', array('model' => $model))?>

  8. Create protected/views/snippet/_form.php as follows:

    <?php echo CHtml::beginForm()?> <ul> <li> <?php echo CHtml::activeLabel($model, 'title')?> <?php echo CHtml::activeTextField($model, 'title')?> </li> <li> <?php echo CHtml::activeLabel($model, 'code')?> <?php echo CHtml::activeTextArea($model, 'code')?> </li> <li> <?php echo CHtml::activeLabel($model, 'language')?> <?php echo CHtml::activeDropDownList($model, 'language',
    $model->getSupportedLanguages())?> </li> <li> <?php echo CHtml::submitButton('Save')?> </li> </ul> <?php echo CHtml::endForm()?>

  9. That is it. Now, run the snippet controller and try creating code snippets, as shown in the following screenshot:

  10. When it is viewed, it will look similar to the following screenshot:

How it works...

The snippet model's function is used to store the code and snippet title. Additionally, we have added the html and language fields. The first one (html) is used to store HTML representing the highlighted code, while the language field is used for the snippet language (PHP, HTML, CSS, JavaScript, and so on). We need to store these, as we need them when we edit the snippet.

As we remove the safe rule from the Snippet model, we make title, code, and language as the required fields. There is no rule for html, which means that it cannot be set through the form directly.

The afterValidate method, as its name states, is executed after the validation gives us no errors. In this method we transform the code that is stored in the code field to HTML, representing the highlighted code in the html field by using the Yii's CTextHighlighter class and passing the language value to it.

Note that you need to define CSS with php-hl-* classes defined to get highlighting. You can get the default style from framework/vendors/TextHighlighter/highlight.css.

The getSupportedLanguages function returns languages we want to support in the value-label array. We use this method in the snippet form.

There's more...

In order to learn more about code highlighting, you can use the following resources:

Yii code highlighter

More code highlighters

If Text_Highlighter bundled with Yii does not fit your needs, there are many code highlighters available on the Internet. A few good examples are found at the following links:

See also

  • The Processing model fields with AR event-like methods recipe
  • The Applying markdown and HTML recipe
  • The Setting up an author automatically recipe

Automating timestamps

Almost every model representing content should have creation and modification dates to show the content actuality, revisions, and so on. In Yii there are two good ways to automate this, which are as follows:

  • Overriding beforeValidate
  • Using the CTimestampBehavior behavior from Zii

We will see how to apply these to blog posts. We will use Unix timestamps to store the date and time.

Getting ready

  1. Create a new application by using yiic webapp as described in the official guide at the following URL:

    http://www.yiiframework.com/doc/guide/en/quickstart.first-app

  2. Set up a database connection and create a table named post as follows:

    DROP TABLE IF EXISTS `post`; CREATE TABLE IF NOT EXISTS `post` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `text` TEXT NOT NULL, `created_on` INT(10) UNSIGNED NOT NULL, `modified_on` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`) );

  3. Generate a Post model using Gii.
  4. Remove everything about created_on and modified_on from the rules method of the model.

How to do it...

  1. We will start with overriding the beforeValidate method. Open protected/models/Post.php and add the following method:

    protected function beforeValidate() { if($this->getIsNewRecord()) $this->created_on = time(); $this->modified_on = time(); return parent::beforeValidate();; }

  2. Now add the following code to the new controller and run it:

    $post = new Post(); $post->title = "test title"; $post->text = "test text"; $post->save(); echo date('r', $post->created_on);

  3. You should get a date and time. Since we have simply created a post, it will be the current date and time. Another method is to use CTimestampBehavior. Delete the Post model and generate it one more time by using Gii. Remove everything about created_on and modified_on from the rules method of the model. Add the following method to the model:

    public function behaviors() { return array( 'timestamps' => array( 'class' => 'zii.behaviors.CTimestampBehavior', 'createAttribute' => 'created_on', 'updateAttribute' => 'modified_on', 'setUpdateOnCreate' => true, ), ); }

How it works...

The beforeValidate method executes just before the model validation starts. In this method, modified_on is always filled and created_on is filled only if the model is new, which is only when we are creating a post.

When we use the ready behavior from Zii, we specify createAttribute and updateAttribute to match the field names we have chosen. The setUpdateOnCreate property triggers filling modified_on when a record is inserted. The rest is done by the behavior function.

There's more...

In order to learn more about CTimestampBehavior, refer to the following API page:

http://www.yiiframework.com/doc/api/CTimestampBehavior/

See also

  • The Processing model fields with AR event-like methods recipe
  • The Applying markdown and HTML recipe
  • The Highlighting code with Yii recipe

Setting up an author automatically

Almost all applications that involve multiple content authors should have a way to track who created the content or who is the owner.

From this recipe, you will learn how to automate this by using a model. We assume that the application uses CUserIdentity to manage authorization and that Yii::app()->user->id returns integer user ID. We don't need to change the original post author if someone else edited it.

Getting ready

  1. Create a new application by using yiic webapp as described in the official guide at the following URL:

    http://www.yiiframework.com/doc/guide/en/quickstart.first-app

  2. Set up a database connection and create a table named post as follows:

    DROP TABLE IF EXISTS `post`; CREATE TABLE IF NOT EXISTS `post` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(255) NOT NULL, `text` TEXT NOT NULL, `author_id` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`) );

  3. Generate the Post model using Gii.

How to do it...

  1. Add the following method to the protected/models/Post.php model as follows:

    protected function beforeValidate() { if(empty($this->author_id)) $this->author_id = Yii::app()->user->id; return parent::beforeValidate(); }

  2. That is it. Now we will test it. So, create protected/controllers/TestController.php as follows:

    <?php class TestController extends CController { public function actionIndex() { $post = Post::model()->find(); if(!$post) $post = new Post(); $post->title = 'test'; $post->text = 'test'; $post->save(); echo $post->author_id; } }

  3. Now, log in and execute test/index. You should get an ID of the currently logged in user. Log in as another user and execute the code again. This time, you should get the same ID, which is exactly what we have planned.

How it works...

The beforeValidate method executes just before the model validation starts. In this method, we set the author_id value to Yii::app()->user->id only if the author ID is empty. Most likely, this will be post creation, but it can also be when the original author is deleted (if you have properly set the foreign key with the on delete cascade option).

See also

  • The Processing model fields with AR event-like methods recipe
  • The Applying markdown and HTML recipe
  • The Highlighting code with Yii recipe
  • The Automating timestamps recipe

Implementing single table inheritance

Relational databases do not support inheritance. If we need to store inheritance in the database, we should somehow support it through code. This code should be efficient, so it should generate as few JOINs as possible. A common solution to this problem was described by Martin Fowler and is named as single table inheritance .

When we use this pattern, we store all the class tree data in a single table and use the type field to determine a model for each row.

As an example, we will implement the single table inheritance for the following class tree:

Car |- SportCar |- FamilyCar

Getting ready

  1. Create a new application by using yiic webapp as described in the official guide.
  2. Create and set up a database. Add the following table:

    CREATE TABLE `car` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `type` varchar(100) NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO `car` (`name`, `type`) VALUES ('Ford Focus', 'family'), ('Opel Astra', 'family'), ('Kia Ceed', 'family'), ('Porsche Boxster', 'sport'), ('Ferrari 550', 'sport');

How to do it...

  1. First, we will create the car model, protected/models/Car.php, as follows:

    <?php class Car extends CActiveRecord { public static function model($className=__CLASS__) { return parent::model($className); } public function tableName() { return 'car'; } protected function instantiate($attributes) { switch($attributes['type']) { case 'sport': $class='SportCar'; break; case 'family': $class='FamilyCar'; break; default: $class=get_class($this); } $model=new $class(null); return $model; } }

  2. We implement protected/models/SportCar.php as follows:

    <?php class SportCar extends Car { public static function model($className=__CLASS__) { return parent::model($className); } public function defaultScope() { return array( 'condition'=>"type='sport'", ); } }

  3. Also implement protected/models/FamilyCar.php as follows:

    <?php class FamilyCar extends Car { public static function model($className=__CLASS__) { return parent::model($className); } public function defaultScope() { return array( 'condition'=>"type='family'", ); } }

  4. Now create protected/controllers/TestController.php as follows:

    <?php class TestController extends CController { public function actionIndex() { echo "<h1>All cars</h1>"; $cars = Car::model()->findAll(); foreach($cars as $car) { // Each car can be of class Car, SportCar or FamilyCar echo get_class($car).' '.$car->name."<br />"; } echo "<h1>Sport cars only</h1>"; $sportCars = SportCar::model()->findAll(); foreach($sportCars as $car) { // Each car should be SportCar echo get_class($car).' '.$car->name."<br />"; } } }

  5. Run test/index and you should get the output as shown in the following screenshot:

How it works...

The base model Car is a typically used Yii AR model except with two added methods. The tableName method explicitly declares the table name to be used for the model. For the Car model alone, this does not make sense, but for child models, it will return the same car table, which is just what we want—a single table for the entire class tree. The instantiate method is used by AR internally to create a model instance from the raw data when we call methods, such as Car::model()->findAll(). We use a switch statement to create different classes based on the type attribute and use the same class if the attribute value is either not specified or points to the non-existing class.

The SportCar and FamilyCar models simply set the default AR scope, so when we find models with SportCar::model()-> methods, we will get the SportCar model only.

There's more...

Use the following references to learn more about the single table inheritance pattern and Yii Active Record implementation:

See also

  • The Using scopes to get models for different languages recipe

Using CDbCriteria

When we use Yii's Active Record methods, such as findAll or find, we can pass criteria as a parameter. It can be an array or an instance of the CDbCriteria class. This class represents query criteria, such as conditions, ordering by, limit/offset, and so on.

How to do it...

Usually, the criteria class is used as shown in the following example:

$criteria = new CDbCriteria(); $criteria->limit = 10; $criteria->order= 'id DESC'; $criteria->with = array('comments'); $criteria->compare('approved', 1); $criteria->addInCondition('id', array(4, 8, 15, 16, 23, 42)); $posts = Post::model()->findAll($criteria);

How it works...

Internally, the criteria class does not build any queries by itself, but it stores data and allows us to modify it. The actual work is being done inside the AR methods, where criteria are being used.

The preceding code can be read as follows:

Get 10 posts along with comments from approved posts with ID equals to 4, 8, 15, 16, 23, or 42 ordered by the ID descendant.

Alternatively, it can also be read as:

SELECT * FROM post p JOIN comment c ON p.id = c.post_id WHERE p.approved = 1 AND p.id IN (4, 8, 15, 16, 23, 42) ORDER BY p.id DESC LIMIT 10

There's more...

For further information, refer to the following URLs:

See also

  • The Getting data from a database recipe

Summary:

In this article we saw how to work with databases efficiently, when to use models and when not to, how to work with multiple databases, how to automatically pre-process Active Record fields, and how to use powerful database criteria.

Resources for Article :


Further resources on this subject:


Yii Rapid Application Development Hotshot Become a RAD hotshot with Yii, the world's most popular PHP framework with this book and ebook.
Published: December 2012
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Alexander Makarov

Alexander Makarav is an experienced engineer from Russia and has been a Yii framework core team member since 2010. Before joining the Yii core team, he participated in the CodeIgniter community growth in Russia. In 2009, he finished the Russian translation of the framework documentation and created the Russian community website. In 2012, he released the Russian version of the book along with Russian community members. In the same year, he was the technical reviewer for three more books:

  • The Yii Book: Developing Web Applications Using the Yii PHP Framework, Larry Ullman
  • Web Application Development with Yii and PHP, Jeff Winesett
  • Yii Rapid Application Development Hotshot, Lauren O'Meara and James Hamilton

In his free time, Alexander writes technical blog at http://rmcreative.ru/, speaks at conferences, and enjoys movies, music, traveling, photography, and languages. He currently resides in Voronezh, Russia with his beloved wife and daughter.

Books From Packt


Agile Web Application Development with Yii1.1 and PHP5
Agile Web Application Development with Yii1.1 and PHP5

Yii Rapid Application Development Hotshot
Yii Rapid Application Development Hotshot

Web Application Development with Yii and PHP
Web Application Development with Yii and PHP

Instant Building Multi-Page Forms with Yii How-to [Instant]
Instant Building Multi-Page Forms with Yii How-to [Instant]

Yii 1.1 Application Development Cookbook
Yii 1.1 Application Development Cookbook

Instant Yii 1.1 Application Development Starter [Instant]
Instant Yii 1.1 Application Development Starter [Instant]

Flash Game Development by Example
Flash Game Development by Example

Agile Project Management with GreenHopper 6 Blueprints
Agile Project Management with GreenHopper 6 Blueprints


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
v
f
y
j
D
y
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software