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.id', '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' => 'group.id',
'condition' => 'group.id = :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 (group.id =
:group)
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.id = :group) ORDER BY t.firstname, t.lastname,
t.mobile_no)
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.