Yii Routing, Active Record and Caching

Sandeep Panda

Almost all modern web apps have 3 major concerns: Retrieving data from a database easily and effectively, caching the web content and URL rewriting to create user friendly URLs. Yii, like any other good framework, offers simple and easy solutions to all of the above. In my previous article I covered the basics of building a simple CRUD app with Yii. In this tutorial we will look at how Yii greatly simplifies the development of database driven websites with its Active Record support. Additonally, Yii lets you further improve your websites by implementing user friendly URLs and powerful caching techniques. Let's dive in!

Hitting the database

We will be creating a very simple web app for storing and retrieving details about different smartphones using Yii.

To create a skeleton Yii application we will use the command line tool yiic that ships with Yii framework. You can find it under YiiRoot/framework directory. As I am on windows it's under C:\yii\framework. I recommend adding this directory to your system path so that you can run yiic from any folder. If you are on Windows you also need to add the path to php.exe to your system path.

Just go to the folder where you keep all your PHP projects (I'm on Windows, so it's C:\wamp\www in my case), then run the command: yiic webapp project_name_here. I am naming the project as gadgetstore. That should create a new Yii project with the necessary folder hierarchy.

By default, the Yii actions defined inside the controllers are accessed in the following way:


Since we want to be user friendly, we don't want this type of URL. To change this, open the config file i.e. main.php and uncomment the following lines:

'<controller:\w+>/<action:\w+>/<id:\d+>'=>'<controller>/<action>',        '<controller:\w+>/<action:\w+>'=>'<controller>/<action>',

Add 'showScriptName'=>false after line 2 to suppress the annoying index.php from the URL. Also don't forget to add a .htaccess file with the following content to the root of your project:

Options +FollowSymLinks
IndexIgnore */*
<IfModule mod_rewrite.c>
RewriteEngine on
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . index.php

Now, the URLs can be accessed in a much simpler way:


Configuring your app to use a database is a matter of adding a couple of lines to your configuration. Just open up your /protected/config/main.php file and uncomment the following lines:

'connectionString' =>'mysql:host=localhost;dbname=testdb',
'emulatePrepare' => true,
'username' => 'root',
'password' => '',
'charset' => 'utf8',

Here we are essentially configuring our app to use a particular MySQL or MariaDB database. In my case, the database name is testdb. Change the dbname in the above snippet accordingly. The rest of the details are self explanatory.

Now let's quickly set up a database table that will contain information about our amazing gadgets.

The table structure is as follows:

Table Name: phone

Column        Type    
id            int(10)    
name          varchar(65)    
price          int(6)    
memory          varchar(65)    
camera          varchar(65)    
screen_size   varchar(65)    
os          varchar(65)

For the time being we will just keep 5 simple attributes that are common to all smartphones i.e. price, memory, camera, screen_size and os.

Now, the next step is to create the Active Record class that will hold the attributes of a smartphone. Each AR class corresponds to a database table and each AR instance represents a single row in that table. Now let's create an AR class called Phone. To generate this class we will use gii, the automatic code generation tool that ships with Yii. Open up: http://localhost/gadgetstore/gii in your browser and go to the model generator. Provide the table name (in my case phone) and type Phone in the model class field. Have a look at the following screenshot.

Now hit preview and click on generate to create the model class. You can find it inside the protected/models directory.

As the AR class is generated you can instantiate it anywhere and access the database table attributes as the properties of the AR instance. This is achieved through the __get() magic method. For example, it's perfectly legal to do the following:

$model=new Phone;   //creates new model instance
$model->name="Samsung Galaxy Note 3"; //sets name property
$model->price=299;    //sets price property
$model->os="Android 4.3"; //sets os property

Now to save the model all you need to do is call save() on it.

$model->save(); //saves the phone to the database.

Updating an existing row is also very simple.

$model=Phone::model()->findByPK(10);  //phone with id 10
$model->save(); //save the updates in DB.

To Delete a row:

$model->delete(); //gone from the table

Active Record basically offers an easy way to perform CRUD operations that often involve simple SQL commands. For complex queries you might want to switch to Yii DAO.

Just a small change is needed in the generated model. Open up model class Phone and find the following line in the rules() function:

array('id, name, price, memory, camera, screen_size, os', 'safe', 'on'=>'search')

Now replace search with insert in the above line. Why we did this will become clear in the subsequent sections.

Now that you have the AR ready we need to create a controller that will actually do the insertion/update (also called upsert) in the database using the AR class. Just create a file PhoneController.php inside protected/controllers. Inside the file create an empty class PhoneController.

class PhoneController extends Controller{


Now let's add a function actionAdd() to the class which looks like following:

public function actionAdd(){
    $model=new Phone;
    if(isset($_POST['Phone'])) //line 3
        $model->attributes=$_POST['Phone']; //line 5
          $this->redirect("view/$model->id"); //line 6

In this function we are adding a new row to the table. But prior to this we need to create a view file that shows a form through which one can enter various attribute values of the phone. The form can be generated very easily through gii's form generator. You can open up gii in your browser and go to the form generator. Just enter the name of the model (Phone) and name of the view (in this case phone/add) and click generate. It will create a view file add.php inside protected/views/phone.

In the above snippet first we check if the request is POST. If not then we simply show the form where the user can enter values. But if it's a post back we need to store the data in table. To capture the incoming data we do the following:


The above operation is known as massive assignment. Here all the properties of model are given values that are received in the request. Remember how we changed the scenario from search to insert inside the Phone class earlier? It's because of this massive assignment. Whenever we are instantiating new model the scenario is insert. So, if we declare the attributes safe only for the search scenario, this massive assignment will fail. That's the reason we declared the attributes of Phone as safe for insertion.

Next, we check if there are validation errors, and if there are none, we proceed to save the model. The user is then redirected to a URL where he can see the added smartphone. The update and view functionality are implemented in a similar fashion.

Just a quick note: you can download the demo app and check out the source code. There you can see how the additional functions and views for PhoneController are implemented.

User Friendly URLs Are Always Good

Currently our URL for viewing a newly added smartphone uses this format:


But how about making it a bit more attractive? Maybe we can impress our users by showing them the name of the smartphone in the URL? Something like http://localhost/gadgetstore/phones/samsung-galaxy-s4 perhaps?

To implement this type of URL just add the following line to the urlManager rules in protected/config/main.php.


Together, all the rules are as follows:

'phones/<name:[\w\-]+>'=>'phone/show', //rule 1

What rule 1 means is any url that starts with 'phones/' should be handled by the actionShow() function of PhoneController class. Additionally, the part after 'phones/' will be passed as a GET request parameter called 'name' to the actionShow() function. By doing this we can capture the request param and utilize it to find the required smartphone by name!

The actionShow() function is implemented as follows:

public function actionShow(){
    $name=implode(' ',explode('-',$name));
    $phone=Phone::model()- >find('name=:name',array(':name'=>$name));
    throw new CHttpException(404,'Page Not Found');
      throw new CHttpException(404,'Page Not Found');

Remember Yii's URL management is pretty vast and you can create really impressive URL patterns with it. This was just a basic tutorial showing a subset of what the URL management module is capable of.

Also, please note that while selecting entries by name is perfectly legit, you should always select them by unique slugs (URL-optimized strings), just in case some entries have the same name. This is done by generating a unique name-based slug on every insertion. For example, if Samsung Galaxy S4 was inserted, the slug might be samsung-galaxy-s4. However, if another Samsung Galaxy S4 model appears, the new slug should be something like samsung-galaxy-s4-01, just to be different from the first one.

Cache Your Content For Better Performance

Yii's caching implementation has many types. In the most simple scenarios we might get our task done through query caching.

While using Active Record we can specify to put the retrieved data into cache and subsequently use the cache instead of hitting the database. In our app, query caching can be achieved through the following snippet:


The above code retrieves the data from the DB and adds it to the cache. The first parameter specifies how many seconds the cache will live. The second parameter is the dependency which is null in our case. The third parameter denotes the number of subsequent queries to cache. As we have specified 2 as 3rd argument the next 2 queries will be cached. So, the next two times a request comes, the cache will be searched for the content instead hitting the database. This clearly improves the performance if you are getting too many requests per second.

There are other, advanced, types of caching, but outside the scope of this article. If you'd like them covered in more detail, let us know in the comments below.


The abilities of the above discussed modules are really vast. As it was not possible to cover all the aspects in a single tutorial, some points were left out. So, aside from letting us know what you'd like to read more about, here are the top 3 things you should start reading after this tutorial.

  • Yii's AR offers a very nice API to select data from the database in different ways. You can find data by attributes, primary keys and your own search conditions. Just head over to the above link to know more about it.

  • You should start reading about how to use different patterns in your URL rules inside the config file. This gives you the power to create impressive URLs in your apps. Apart from that it's also useful to create your own URL rule class.

  • Query caching is not the only caching mechanism available in Yii. There are several other implementations too. The Yii documentation has an excellent tutorial regarding caching with Yii.

Thank you for reading, and don't forget to check out the source code for more info!