How to use cSort and cPagination when not using CDbCriteria

Archives Yii 1.0.x Discussions
1

I’m using User::model()->findBySql().

So how can I use the sort and pagination classes with this.

The alternative is to use CDbCriteria i.e. User::model()->find($CDbCriteria);

But my query is too complicated for $CDbCriteria->select etc… as there are lots of subqueries

Any ideas?

2

try this… hope it helps… :)




$criteria=new CDbCriteria;

$criteria->select='title';  // only select the 'title' column

$criteria->condition='postID=:postID';

$criteria->params=array(':postID'=>10);

$post=Post::model()->find($criteria); // $params is not needed

or


$post=Post::model()->find(array(

    'select'=>'title',

    'condition'=>'postID=:postID',

    'params'=>array(':postID'=>10),

));
3

This should work, though I haven’t tested it :)


    $pages=new CPagination($totalmodels); // $totalmodels should be the total count of query results, so you'll have to run a count(*) query first most likely, and preferably cache it if your query really is complex

    $pages->pageSize=self::PAGE_SIZE; // set to page size or use a static class var PAGE_SIZE like in most Yii examples


    $sort=new CSort('User');

    $order = array();

    foreach ($sort->getDirections() as $field => $direction)

    {

        $field = 'tablename.'.$field; // replace tablename with the name of the User table or its alias in the query. if you sort on attributes from multiple tables, you'll have to add additional code here to link the fields to their table's name/alias

        if ($direction)

            $order[] = $field.' ASC';

        else

            $order[] = $field.' DESC';

    }


    $models = User::model()->findBySql('YOUR_QUERY'.' ORDER BY '.implode(',', $order).' LIMIT '.($pages->getPageSize()*$pages->getCurrentPage()).','.$pages->getPageSize());