在Yii框架中使用CPagination和createCommand函数

Question

I am trying to use CPagination.The example shown here http://www.yiiframework.com/doc/api/1.1/CPagination is using criteria. I am using stored procedure for selecting the data. The database used is postgresql. I am calling the stored procedure using create command function.

$command = Yii::app()->db->createCommand("select sp_images_select(:project_id,:milestone_id);");
$command->bindParam(":project_id",$this->project_id,PDO::PARAM_STR);
$command->bindParam(":milestone_id",$this->milestone_id,PDO::PARAM_STR);
$command->queryAll();

How can I use CPagination with the result set of this command. I am using ajax to display the image gallery as explained here http://www.yiiframework.com/wiki/49/update-content-in-ajax-with-renderpartial/

Please help.

Thanks in advance.

Answer 1

This can be done only if you can apply Limit (maximum records to retrieve) and Offset (from which record to start) in some way to the stored procedure.

Let's go step by step through the example and analyze what is needed

Your Yii controller for the images

function actionIndex(){
    //Not needed, you apply criteria in your stored procedure with the 
    //arguments of your stored procedure.
    //$criteria=new CDbCriteria();

    // In some way you have to be able to determine how many records will 
    // be returned by the stored procedure when no limits are set 
    // (your challenge).
    $count= ..; // challenge here

    // create pagination object
    $pages=new CPagination($count);

    // Results per page, configure the maximum number of rows to be 
    // displayed (the limit).
    $pages->pageSize=10;

    // Set the page you are on. Either the starting page (0) or the page 
    // when the action is called by the linker (clicking on a button to 
    // go to a certanin page). The linker will send the page using get.
    // Pages are zero based so you have to decrease the received page by 
    // 1. 
    $pages->currentPage = (isset($_GET['page']) ? $_GET['page']-1 : 0)

    // You can not use this. This method applies the criteria to a plain 
    // vanilla query (not a stored procedure) and extends that query with 
    // a limit and offset.
    //$pages->applyLimit($criteria);
    // Instead, you retrieve the limit and offset from the pagination 
    // object and pass it to your stored procdure. F.i.
    $command->bindParam(":limit",$pages->limit,PDO::PARAM_INT);
    $command->bindParam(":offset",$pages->offset,PDO::PARAM_INT);

    // Get the the images with the stored procedure.
    $images=$command->queryAll();

    // Display the images using a view
    $this->render('index', array(
    'images' => $images,
         'pages' => $pages
    ));
}

So, this is what you have to do if you want to use the aforementioned approach:

• find out if your stored procedure supports Limit and Offset
• if so, extend the arguments of the stored pocedure to receive Limit and Offset and apply it
• find a way to determine the number of records the stored procedure would result in when Limit and Offset are not set. This is needed so the pagination object can calculate the Offset based on the current page

Hope this will get you on track...