CActiveDataProvider and pagination in subquery

Yii 1.1 General Discussion
1

Hi guys,

I now want to display the latest rows in ascending order on CListView/CGridView. We all know it would be nice using SQL approach:


SELECT * FROM (

    SELECT `id` , `content` , `createdDate` FROM `message`

    ORDER BY `createdDate` DESC

    LIMIT 20 OFFSET 0

) AS t

ORDER BY

    t.`createdDate` ASC

As you can see we have ORDER BY DESC in subquery and ORDER BY ASC in the wrapper.

But how to implement it with CActiveDataProvider (or even CSqlDataProvider)? The problem is LIMIT (pagination) must be applied in the subquery.

Any help would be highly appreciated.

2

One possible approach would be to write a custom dataprovider class specifically for queries of this type. This can be done relatively easily by extending CDataProvider and implementing its abstract methods.

3

Thanks for the reply.

However, I’m not pretty good at Yii so I still need some helps implementing that approach. Actually I face some problems retrieving the full executed sql command from criteria then wrap it with a SELECT query and reverse order.

In fetchData() method:


if (!$this->reverseOrder)

{

     $data=$this->model->findAll($criteria);        

}

else

{

      // What should I do here?

}

Any helps?

4

Anyone interested in?

5

Why not simply reverse order at PHP level?




$data = $this->model->findAll($criteria);

if ($this->reverseOrder) {

  $data = array_reverse($data);

}
6

Thanks for your reply phtamas!

Yes array_reverse was the first thing came to my mind. But I thought sorting in DB level by using ORDER BY is generally gonna be a bit faster than in PHP level since my sort field is indexed.

As your suggest, I will use array_reverse while waiting for another help :)

Thank you again for your great support!