Pagination Problem

Yii 1.1 General Discussion
1

Hey guys,

I’m trying to figure out how to paginate data from a manual mysql query.

Here is my code;




 if($ut!='')

       {

       $ut_sql=" and users_role='$ut'";

       }

       if($hopefull!='')

         {

         $hopefull_sql="and users_hopefull=1";

         }

         else

         {

            $hopefull_sql="and users_hopefull=0";

         }          

//Criteria

$crit="SELECT *,SUM(_ratings.ratings_value) AS total_value FROM _ratings LEFT OUTER JOIN _users ON 

_users.users_id=_ratings.ratings_obj_owner where users_approved=1 $ut_sql $hopefull_sql GROUP BY _ratings.ratings_obj_owner 

ORDER BY total_value desc";

//Paging

$pages = new CPagination($model->count($crit));

$pages->pageSize=20;

$pages->applyLimit($crit);

$res_r=$model->with('user')->findAllBySql($crit);

$data=array($res_r,$pages);

I keep getting the following error;




CDbException

Description


CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT *,SUM(_ratings.ratings_value) AS total_value FROM _ratings LEFT OUTER JOI' at line 1

If i comment out the pagination stuff it works no probs. Any ideas?

Cheers,

Sn0rcha

2

The parameter to count() and find…(), if it is a string represents a WHERE clause (e. g. the SELECT part should not be included).

http://www.yiiframework.com/doc/api/CActiveRecord#find

The parameter to applyLimit has to be a CDbCriteria

http://www.yiiframework.com/doc/api/CPagination#applyLimit

/Tommy

3

Thanks for the reply Tommy!

I’m very new to this, I was wondering if you could enlighten me on how to convert that query to use the CDbCriteria so it works with the pagination?

Cheers,

Sn0rcha

4

Read more about Active Record here:

http://www.yiiframework.com/doc/guide/database.ar

and about Relational Active Record here:

http://www.yiiframework.com/doc/guide/database.arr

enable logging:

http://www.yiiframework.com/doc/guide/topics.logging

(searched but didn’t find a cookbook article on SQL logging/profiling)

You have to add a relation to your Ratings model (‘users’=>‘self:BELONGS_TO,Users,ratings_obj_owner’).

Instantiate a CDbCriteria object and populate the members $criteria->with, $criteria->condition, $criteria->group, $criteria->order.

I’ll have to do some testing with Yii 1.1 myself, to sort out what’s the best practice (especially the different flavours of “with”, “together”, “join”). There have been some changes since early 1.0.

/Tommy