Skip to content

Categories:

Using Class Properties To Minimise Database Queries And Code Execution

In my previous post Customising CGridView – Adding A Select Menu I used two public methods from my Orderstatus class to display a select menu for seaching the ‘Orders Status’ CGridView column and to display status name rather than ID in the column. In that post I used the following in OrdersController to grab a database object to pass to the CHtml listData() method:


$status_obj = Orderstatus::model()->getStatusObj();

This was passed to CHtml listData()to create a select menu like this:


CHtml::listData($status_obj, 'id', 'orders_status_name')

I could have easily used CActiveRecord‘s findAll() method:

CHtml::listData(Orderstatus::model()->findAll(), 'id', 'orders_status_name')

however there is a good reason to use my own method  >>>  To save database queries and prevent unnecessary code execution by using declared properties in the Orderstatus class.

The first step was to declare two properties (these can be public or private) in the Orderstatus class, to be used in the two methods:


    public $status_obj;

    public $status_array;

The first of these is used in the getStatusObj() method:


    public function getStatusObj()
    {
        if(empty($this->status_obj)) {
            $this->status_obj = self::model()->findAll();
        }
        return $this->status_obj;
    }

This method is called in my OrdersController:

public function actionAdmin()

{
    $model=new Orders('search');
    $model->unsetAttributes();  // clear any default values
    if(isset($_GET['Orders']))
        $model->attributes=$_GET['Orders'];

    $status_obj = Orderstatus::model()->getStatusObj(); //fetch our orderstatus object

    $this->render('admin',array(
        'model'=>$model, 'status_obj' => $status_obj, //pass orderstatus object to view
    ));
}

So what is the advantage of this?  Because $status_obj is a property getStatusObj() can be called multiple times when a page is loaded and the database query will be executed only once.  But haven’t we only called it once in the OrdersController?  Thus far, yes, however we will also call it in the next method, getStatusArrayVal() which is called for every row in our CGridView:

    public function getStatusArrayVal($id)
    {
        if(empty($this->status_array)) {
            foreach($this->getStatusObj() as $val) {
                $this->status_array[$val->id] = $val->orders_status_name;
            }
        }
        return $this->status_array[$id];
    }

These are used in CGridView like this:

array('name' => 'orders_status', 'value' => 'Orderstatus::model()->getStatusArrayVal($data->orders_status)', 'filter'=>CHtml::listData($status_obj, 'id', 'orders_status_name')),

So if we have twenty rows of data on each page of our CGridView the database query in the getStatusObj() method is called only once (from our controller), rather than for each row in which the getStatusArrayVal() method is called.  Because we also have $status_array as a declared property the foreach() loop to build the array is also only iterated once per page load rather than each time getStatusArrayVal() is called (every row).

Happy coding!

Posted in PHP, Yii Framework.

Tagged with , , , , , .


6 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. Yuliidha says

    Nice explanation buta0is not quite worikng for me Looks OK, but the filter doesn’t actually filter anything The search textbox for the column gets replaced with the dropdown list, and the values shown inside the list are correct I’ve verified the returned array contents for the filter:$test = CHtml::listData(Status::model()->findAll(), id’, code’);print_r($test);The Drop Down shows blank on start (which is ok), and when I click on the drop down and select a value, the GridView does some processing (shows the little circle icon moving) and gets back to the original view, nothing filtered and the dropdown back to blank.Note: If I search through the Advanced Search’ link in the same form (GII generated), it works Ok.Am I missing something? need to enable something somewhere else?Like it? 0

    • Matti says

      You need to make sure that the attribute sent from your drop menu is searchable. This is done in the rules() method of your class (the last line).

      Also, make sure that your drop menu does not have the same ID as in the advanced search. I don’t use the advanced search and always remove it altogether.

      • LeonaCatherine says

        In the example shown above, all the tlbeas were related but I think you can do some thing like this:

        widget('zii.widgets.grid.CGridView',
            array('id'=>'business-grid',
            'dataProvider'=>$dataProvider,
            'filter'=> Business::model(),
            'columns'=>array(
                'business_id',
                'keywords' => array('header' => 'Keywords','value' => 'someFunction($data->someField);',),),));
        
            function someFunction($value){
                // print something after processing
                // you can get values from DB but keep
                // this thing in mind that this function will execute// once for each row in your grid
                $someOtherValue = 'ABC';
                echo $someOtherValue;
            }
        

        Like it? 0

    • Abdullah says

      First of all, thanks for shairng info about YII framework Second, can you show how to create a checkbox list with more values selected, taken from a table DB? For example, checkbox list for preferred music with rock , rap and indie values selected, where these values has active=’1 in music table?Thanks for all, anyway CiaoDanilo



Some HTML is OK

or, reply to this post via trackback.


− seven = one