14 min read

(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. 

  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. 

  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

    
    

    

    4. 



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. 

  2. Create two MySQL databases named db1 and db2.
    3. 

  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. 

  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`) );
    

    

    5. 



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. 

  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. 

  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. 

  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. 

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

    

    6. 



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. 

  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. 

  3. Generate a Post model using Gii.
    4. 



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. 

  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. 

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

    

    4. 



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. 

  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. 

  3. Generate the Post model using Gii
    4. 



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. 

  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. 

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

    

    4. 



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
    • 





 











 
Packt
  






LEAVE A REPLY 





Please enter your comment!





Please enter your name here