Strange behavior of $criteria->order

Yii 1.1 General Discussion
1

Hi guys,

Have a question about CdbCriteria. I am trying to execute this query:

select * from logs_smtpserver where message LIKE ‘Connection from%’ GROUP BY message,class,priority,log_date ORDER BY log_date DESC;

Of course I use Yii Active record, it looks like this:





$name = 'Connection from';

                

$criteria=new CDbCriteria;

$criteria->condition = "message LIKE :name";

$criteria->params = array(":name"=>"$name%");

$criteria->group = 'message,class,log_date,priority';

$criteria->order = 'log_date DESC';


$pages=new CPagination(logs::model()->count($criteria));

$pages->pageSize=self::PAGE_SIZE;

$pages->applyLimit($criteria);


                

$models=logs::model()->findAll($criteria);

It seems it’s working, Yii produces this query(found it in the application logs):

2010/08/23 13:23:50 [trace] [system.db.CDbCommand] Querying SQL: SELECT * FROM "logs_smtpserver" WHERE message LIKE :name GROUP BY message,class,log_date,priority

ORDER BY log_date DESC LIMIT 35. Bind with parameter :name=‘Connection from%’

But problem is it seems that CPagination is getting broken after this query and not showing me any pagination:

I’ve tried to print out $criteria variable,like this print_r($criteria);

And it looks like this:

CDbCriteria Object ( [select] => * [distinct] => [condition] => message LIKE :name [params] => Array ( [:name] => Connection from% ) [limit] => -1 [offset] => -1

[order] => log_date DESC [group] => message,class,log_date,priority [join] => [having] => )

limit and offset are -1 which is weird.

Any help,maybe it is a bug,or something wrong with my query,but the only thing which I am trying is to use ORDER BY.

I use PotgreSQL as a DB backend.

2

Guys, can anyone help,this topic is here for ages, Yii community was way more helpful 6 months ago.

I just really got stuck with this one.

3

Have you tried taking out the pieces of your CDbCriteria one at a time to see if it goes without error? Like, the group by and order lines might be an issue. I just tested some similar type stuff in one of my views and I’m not getting an error. Have you tried taking these out? If so, could you include a little more of your code please.

4

Hi Jaz, this was the first thing which I’ve tried, it seems the problem can be in ORDER BY,because when I

remove ORDER BY, like this:




                $name = 'Connection from';

		

                $criteria=new CDbCriteria;

                $criteria->condition = "message LIKE :name";

                $criteria->params = array(":name"=>"$name%");

                $criteria->order = 'log_date DESC';

Following error occurs:

CDbCommand failed to execute the SQL statement: SQLSTATE[42803]: Grouping error: 7 ERROR: column "logs_smtpserver.log_date" must appear in the GROUP BY clause or be used in an aggregate function at character 71

Which is PostgreSQL error, so as far as I understand I need to specify all the fields which I have in a table to get this working. But after I do this Paging seems stops working.

I really need only to be able to ORDER BY DESC by log_date, seems as a very easy task, but for some reason I have this problem now.