Cdbcriteria->With, Many To Many Relation And Method Findall(). Strange Behavior.

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. :blink:

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:


Снимок экрана от 2014-02-25 12:51:54.png

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 :)