Hello, community!
I need to make a big relational query using many-to-many relations. I’ve faced a problem when using $criteria->with and findAll() method of the model. Please, explain me what is going on because i’ve already gone crazy about it. 
I have an Operation model, which is connected to Roles model using many-to-many relation. Here’s the relations() method of the Operations model:
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'roles' => array(self::MANY_MANY, 'Roles', 'operations_in_roles(id_oper, id_role)', 'joinType'=>'INNER JOIN'),
);
}
I have a table in db operations_in_roles and everything is working just fine when i’m using the lazy load.
Now, i’m trying to make a relational query using with:
$criteria = new CDbCriteria();
$criteria->with = array('roles');
$criteria->together = true;
$criteria->compare('t.id_contr', $cur_contr->id_contr);
$criteria->select = array('roles.name_role_ru as sosalue_role');
$roles_and_actions = Operations::model()->findAll($criteria);
foreach($roles_and_actions as $key=>$one_record)
{
echo $key." "." ".$one_record->id_oper." ".$one_record->sosalue_role."<BR>";
}
the result, which the foreach by findAll() return gives me:
0 38 teacher
1 39 teacher
2 40 teacher
3 41 teacher
The query, which is produced by the ar is:
SELECT roles.value_role as sosalue_role, t.id_oper as id_oper, `t`.`id_oper` AS `t0_c0`, `roles`.`id_role` AS `t1_c0`, `roles`.`name_role_ru` AS `t1_c1`, `roles`.`name_role_ua` AS `t1_c2`, `roles`.`value_role` AS `t1_c3`, `roles`.`sort` AS `t1_c4` FROM `operations` `t` INNER JOIN `operations_in_roles` `roles_roles` ON (`t`.`id_oper`=`roles_roles`.`id_oper`) INNER JOIN `roles` `roles` ON (`roles`.`id_role`=`roles_roles`.`id_role`) WHERE (t.id_contr='12')
when i’m executing the query in my mysql console or phpmyadmin or so on… the result is:
5254
it’s the right result. IT HAS 8 RECORDS, NOT 4!!!
But why does the findAll() function return me the array of only 4 records? Please, explain, guys!
p.s. I’ve solved this problem using $criteria->join instead of with and findall returns me the 8 records which it should, but i want to use with in my project…
I want to find the purpose of such strange behavior. Answer please 