Relational Active Record failed when limit applied. [solved]

First of all I have 3 tables - contact, group and group-contact. contact and group tables are link by many-many relationship.

When I try to get contact table CActiveDataProvider with the following criteria,

$criteria = new CDbCriteria();


$criteria->select = array('', 't.firstname', 't.lastname', 't.mobile_no');

$criteria->condition = 't.deleted IS NULL';

$criteria->order = 't.firstname, t.lastname, t.mobile_no ASC';

$criteria->with = array('group' => array(

	'select' => '',

	'condition' => ' = :group',

	'params' => array(':group' => $group_id),


the result return is different from what I got with same criteria on CActiveRecord.

Log trace on CActiveDataProvider->getData (limit is by pagination):

Log trace on CActiveRecord->findAll (limit is set):

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

`t`.`firstname` AS `t0_c1`, `t`.`lastname` AS `t0_c2`, `t`.`mobile_no` AS

`t0_c4` FROM `contact` `t`  WHERE (t.deleted IS NULL) ORDER BY t.firstname,

t.lastname, t.mobile_no ASC LIMIT 100

SELECT `t`.`id` AS `t0_c0`, `group`.`id`

AS `t1_c0` FROM `contact` `t` LEFT OUTER JOIN `group-contact` `group_group`

ON (`t`.`id`=`group_group`.`contact_id`) LEFT OUTER JOIN `group` `group` ON

(`group`.`id`=`group_group`.`group_id`) WHERE (`t`.`id` IN ('6', '5', '14',

'16', '17', '18', '20', '21', '10', '19', '12', '15')) AND ( =


Log trace on CActiveRecord->findAll (without limit set):

SELECT `t`.`id` AS `t0_c0`, `t`.`firstname`

AS `t0_c1`, `t`.`lastname` AS `t0_c2`, `t`.`mobile_no` AS `t0_c4`,

`group`.`id` AS `t1_c0` FROM `contact` `t`  LEFT OUTER JOIN `group-contact`

`group_group` ON (`t`.`id`=`group_group`.`contact_id`) LEFT OUTER JOIN

`group` `group` ON (`group`.`id`=`group_group`.`group_id`) WHERE (t.deleted

IS NULL) AND ( = :group) ORDER BY t.firstname, t.lastname,


I went to through the framework coding, and tested on a few queries. I realised whenever limit is applied, the query returns a wrong set of results.

This issue has been resolved. Just to share with you all. SQL limit does not applied in HAS_MANY/MANY_MANY relationships. For example a contact exists in 2 groups and another exists in 3 groups, having a limit of 2 will not reflect a correct recordset.

Hopefully it benefits everyone.