CActiveFinder ignoring 'with' if limited

Archives Bug Discussions
1

Hi,

I have a model called ‘Magazine’ with the following relation to the ‘Subscription’ model:




'subscriptions' => array(self::HAS_MANY, 'Subscription', 'magazine_id')

I’m trying to find all ‘Magazine’ records for which a record in the ‘subscription’ table is available where the column ‘user_id’ has a specific value:




$criteria = array(

	'with' => array(

		'subscriptions'=>array(

			'joinType'=>'INNER JOIN',

			'condition'=>'subscriptions.user_id = ' . Yii::app()->user->id,

			'order'=>'subscriptions.subscribe_time DESC',

		),

	),

);

				

$crit = new CDbCriteria($criteria);

$data = Magazine::model()->findAll($crit);

Now this works fine and I get the expected results. But if I want to do paging and add limit informations to the criteria before calling ‘findAll()’ as follows:




$crit->limit = 5;

$crit->offset = 0;

the ‘findAll()’ returns ALL records of the ‘Magazine’ model ignoring the ‘with’ property.

I was able to follow this down to ‘CJoinElement::find()’ in the ‘CActiveFinder.php’ file. If I comment out the following line (number 446):




$this->_finder->baseLimited=($criteria->offset>=0 || $criteria->limit>=0);

then I get the correct results with pagination.

Some debugging information:

  • Without pagination, the SQL query created is:



SELECT `t`.`id` AS `t0_c0`, `t`.`user_id` AS `t0_c1`,

`t`.`domain_id` AS `t0_c2`, `t`.`language_code` AS `t0_c3`,

`t`.`page_size_id` AS `t0_c4`, `t`.`name` AS `t0_c5`, `t`.`description` AS

`t0_c6`, `t`.`subdomain` AS `t0_c7`, `t`.`published` AS `t0_c8`,

`t`.`create_time` AS `t0_c9`, `t`.`update_time` AS `t0_c10`,

`t`.`frequency` AS `t0_c11`, `t`.`website` AS `t0_c12`, `t`.`facebook` AS

`t0_c13`, `t`.`twitter` AS `t0_c14`, `t`.`password` AS `t0_c15`,

`subscriptions`.`user_id` AS `t1_c0`, `subscriptions`.`magazine_id` AS

`t1_c1`, `subscriptions`.`subscribe_time` AS `t1_c2` FROM

`magazine_available` `t`  INNER JOIN `subscription` `subscriptions` ON

(`subscriptions`.`magazine_id`=`t`.`id`)  WHERE (subscriptions.user_id = 3)

ORDER BY subscriptions.subscribe_time DESC

which is correct.

  • With pagination, two SQL statements are executed:



SELECT `t`.`id` AS `t0_c0`, `t`.`user_id` AS `t0_c1`,

`t`.`domain_id` AS `t0_c2`, `t`.`language_code` AS `t0_c3`,

`t`.`page_size_id` AS `t0_c4`, `t`.`name` AS `t0_c5`, `t`.`description` AS

`t0_c6`, `t`.`subdomain` AS `t0_c7`, `t`.`published` AS `t0_c8`,

`t`.`create_time` AS `t0_c9`, `t`.`update_time` AS `t0_c10`,

`t`.`frequency` AS `t0_c11`, `t`.`website` AS `t0_c12`, `t`.`facebook` AS

`t0_c13`, `t`.`twitter` AS `t0_c14`, `t`.`password` AS `t0_c15` FROM

`magazine_available` `t`  LIMIT 5

and




SELECT `t`.`id` AS `t0_c0`, `subscriptions`.`user_id` AS

`t1_c0`, `subscriptions`.`magazine_id` AS `t1_c1`,

`subscriptions`.`subscribe_time` AS `t1_c2` FROM `magazine_available` `t`

INNER JOIN `subscription` `subscriptions` ON

(`subscriptions`.`magazine_id`=`t`.`id`)  WHERE (`t`.`id` IN ('2', '4',

'5')) AND (subscriptions.user_id = 3) ORDER BY subscriptions.subscribe_time

DESC

It seems the scond query is using the results of the first query through the ‘IN’ condition. But why is the first one executed without the ‘with’ condition?

  • After commenting out the line 446 from ‘CActiveFinder.php’:



SELECT `t`.`id` AS `t0_c0`, `t`.`user_id` AS `t0_c1`,

`t`.`domain_id` AS `t0_c2`, `t`.`language_code` AS `t0_c3`,

`t`.`page_size_id` AS `t0_c4`, `t`.`name` AS `t0_c5`, `t`.`description` AS

`t0_c6`, `t`.`subdomain` AS `t0_c7`, `t`.`published` AS `t0_c8`,

`t`.`create_time` AS `t0_c9`, `t`.`update_time` AS `t0_c10`,

`t`.`frequency` AS `t0_c11`, `t`.`website` AS `t0_c12`, `t`.`facebook` AS

`t0_c13`, `t`.`twitter` AS `t0_c14`, `t`.`password` AS `t0_c15`,

`subscriptions`.`user_id` AS `t1_c0`, `subscriptions`.`magazine_id` AS

`t1_c1`, `subscriptions`.`subscribe_time` AS `t1_c2` FROM

`magazine_available` `t`  INNER JOIN `subscription` `subscriptions` ON

(`subscriptions`.`magazine_id`=`t`.`id`)  WHERE (subscriptions.user_id = 3)

ORDER BY subscriptions.subscribe_time DESC LIMIT 5

This is the SQL command I was expecting!!

Is this a bug or am I missing something?

Thanks,

Haykel

2

For now I have found a workaround by adding ‘together’ set to ‘true’ to the criteria to force joining all tables at once, but this should not make a difference in the final result, so I think really this is a bug. Please someone confirm or correct me.

Thanks,

Haykel

3

Hey Haykel,

its almost one year since you have written this post. Are you been able to find something about this problem? I had the same problem, the ‘with’-entry in the scope array


 

'with' => array('authassignments'=>array('alias' => 'a', 'select' => false, 'on'=>'t.id = a.userid' , 'joinType'=>'INNER JOIN')),

doesn’t work, if I set a limit-parameter.

Methode in model:




public function limit($int){

  $criteria = new CDbCriteria();

  $criteria->limit = $int;

  $this->getDbCriteria()->mergeWith($criteria);

  return $this;

}

Maybe another has found a solution… or is it fixed in newer yii versions? My yii-version is 1.1.7

UPDATE:

In the criteria-array I tried it with the parameter ‘together’ = true and now it works. The ‘with’ - parameter is no longer ignored:




'testpersons' = array(

   'select' => 't.id',    												

   'with' => array('authassignments'=>array('alias' => 'a', 'select' => false, 'on'=>'t.id = a.userid' , 'joinType'=>'INNER JOIN')),

   'condition' => "a.itemname = 'testperson' AND t.status = 3",

   'together' => true,

),

Calling:




'users' = User::model()->testpersons->limit(10)->findAll();

Greetz badi