HAS_MANY select with limit


(Jesper) #1

Hi everybody

Trying to do relational select in 1.1 with HAS_MANY relationship:




$criteria=new CDbCriteria;

		$withArray = array(


			'recipients'=>array(

				'condition'=>'recipients.elev_id='.Yii::app()->user->id,

				));


		$pages=new CPagination(StudentMessage::model()->with($withArray)->count());

		$pages->pageSize=self::PAGE_SIZE;

		$pages->applyLimit($criteria);

		

		$models=StudentMessage::model()->with($withArray)->findAll($criteria);



My problem is that as its stated here: http://www.yiiframework.com/doc/guide/upgrade yii performs a query on the primary table first. This query doesn’t consider the condition i set for my with() statement. This results in that the data i get is just the first 10 records from the primary table. Like this:




Querying SQL: SELECT `elever_beskeder`.`besked_id` AS `t0_c0`,

`elever_beskeder`.`besked_type` AS `t0_c1`,

`elever_beskeder`.`besked_anledning` AS `t0_c2`,

`elever_beskeder`.`besked_interntekst` AS `t0_c3`,

`elever_beskeder`.`besked_overskrift` AS `t0_c4`,

`elever_beskeder`.`besked_tekst` AS `t0_c5`,

`elever_beskeder`.`besked_oprettet` AS `t0_c6`,

`elever_beskeder`.`besked_oprettet_af` AS `t0_c7`,

`elever_beskeder`.`besked_sendt_til` AS `t0_c8`,

`elever_beskeder`.`event_type` AS `t0_c9`, `elever_beskeder`.`event_id` AS

`t0_c10`, `elever_beskeder`.`besked_oprettet_af_type` AS `t0_c11`,

`elever_beskeder`.`skole_id` AS `t0_c12`, `elever_beskeder`.`brev_id` AS

`t0_c13` FROM `elever_beskeder`  LIMIT 10 OFFSET 10




SELECT `elever_beskeder`.`besked_id` AS `t0_c0`,

`recipients`.`id` AS `t1_c0`, `recipients`.`besked_id` AS `t1_c1`,

`recipients`.`elev_id` AS `t1_c2`, `recipients`.`besked_type` AS `t1_c3`,

`recipients`.`elev_besked_vises` AS `t1_c4`, `recipients`.`fak_uid` AS

`t1_c5`, `recipients`.`fak_id` AS `t1_c6`, `recipients`.`event_type` AS

`t1_c7`, `recipients`.`event_id` AS `t1_c8` FROM `elever_beskeder` LEFT

OUTER JOIN `elever_beskederx` `recipients` ON

(`recipients`.`besked_id`=`elever_beskeder`.`besked_id`) WHERE

(`elever_beskeder`.`besked_id` IN (25, 40, 41, 42, 43, 44, 45, 46, 49, 50))

AND (recipients.elev_id=1108)



Thank you…


(Ft07) #2

Check if together() will bring back the expected result set.




$models=StudentMessage::model()->with($withArray)->together()->findAll($criteria);



/Tommy


(Jesper) #3

Thanks… Works perfectly now…