CDbCriteria limit bug? Need some help

Yii 1.1 General Discussion
1

Hello!

Doing something like this does not work.




        // Build comment criteria

        $comCriteria = new CDbCriteria;

        $comCriteria->condition = 'document.publisherId=:publisherId';

        $comCriteria->params = array('publisherId' => $Publisher->id);

        $comCriteria->with = array("documentsComments.document");

        $comCriteria->order = "t.created ";

        $comCriteria->limit = 20;

        // Get comments

        $Comments = Comments::model()->findAll($comCriteria);

I end up getting the error:




CDbCommand could not execute the SQL statement: SQLSTATE[42S22]: Column notfound: 1054 Unknown column 'document.publisherId' in 'where clause'

However if i remove the limit parameter it works.

This is the SQL when limit is removed:




SELECT `t`.`id` AS `t0_c0`, `t`.`created` AS `t0_c1`, `t`.`userId` AS `t0_c2`, `t`.`comment` AS `t0_c3`, `documentsComments`.`documentId` AS `t1_c0`, `documentsComments`.`commentId` AS `t1_c1`, `document`.`id` AS `t2_c0`, `document`.`publisherId` AS `t2_c1`, `document`.`title` AS `t2_c2`, `document`.`publishDate` AS `t2_c3`, `document`.`isPublished` AS `t2_c4`, `document`.`descriptionText` AS `t2_c5`, `document`.`totalPages` AS `t2_c6`, `document`.`pageHeight` AS `t2_c7`, `document`.`pageWidth` AS `t2_c8`, `document`.`digitalPrice` AS `t2_c9`, `document`.`currency` AS `t2_c10`, `document`.`campaignId` AS `t2_c11`, `document`.`code` AS `t2_c12`, `document`.`seoAlias` AS `t2_c13`, `document`.`tocPage` AS `t2_c14`, `document`.`pdfName` AS `t2_c15` FROM `Comments` `t`  LEFT OUTER JOIN `DocumentsComments` `documentsComments` ON (`documentsComments`.`commentId`=`t`.`id`) LEFT OUTER JOIN `Documents` `document` ON (`documentsComments`.`documentId`=`document`.`id`) WHERE (publisherId=?) ORDER BY t.created, created DESC

And this is the SQL when limit is there




SELECT `t`.`id` AS `t0_c0`, `t`.`created` AS `t0_c1`, `t`.`userId` AS `t0_c2`, `t`.`comment` AS `t0_c3` FROM `Comments` `t`  WHERE (publisherId=:publisherId) ORDER BY created DESC LIMIT 20

It’s like the joins just miraculously disappears when the limit is added.

And why does it add ORDER BY created when i haven’t told it to?

Actually if i add the order = t.created by myself it will actually duplicate and become "ORDER BY t.created, created DESC"

Well as you might have figured out my problem now is that if we want to show comments we have to remove limit but then in a while

we would be displaying thousands of comments on a single page, and that’s not what we want.

I really apprecate your help on this, we’re really stuck with this error

Oh and FYI, i’m using the latest stable download of the Yii Framework (1.1.0-r1700)

2

I see a little mistake in your code above:




$comCriteria->params = array(':publisherId' => $Publisher->id); // forgot ":". $Publisher or $publisher?

First, correct this :)

3

What happens with together() ? eg

$Comments = Comments::model()->together()->findAll($comCriteria);

When there is no together() and a limit is set, I believe yii does the find in two separate quarries, instead of doing one query with a JOIN. When there is no JOIN, the table reference in the condition does not work

4

I don’t think ‘:’ is actually required

5

I have the same problem. Have you find a workaround for it? If so please post it.

Thanks in advance.

6

Just a ‘stab in the dark’, not really looked too much into your problem, but could ‘document’ be a ‘reserved word’?

I recently had similar problems when I named a table ‘attributes’. Another variable was overwriting it in the model.

7

Confirmed. My model has a self::HAS_MANY relationship that functions properly until I add both criteria on the relationship and pagination, which causes Yii to forget the join. If I deactivate pagination or remove the criteria, the join returns.




$dataProvider = new CActiveDataProvider( 'Group', array(

	'criteria' => array(

		'condition' => 'relationship.column = :value',

		'params' => array( ':value' => 'someValue' ),

		'with'   => array( 'relationship'),

	),

	'pagination' => false,

));
8

I have the same problem with Yii-1.1.8, but if you set




        'criteria' => array(

                'condition' => 'relationship.column = :value',

                'params' => array( ':value' => 'someValue' ),

                'with'   => array( 'relationship'),

                'together' => True,

        ),

can achieve a certain result.