Populate data from more than 2 Modules

I have 3 tables; tbl_member, tbl_message, tbl_messageto.

tbl_member records user information.

tbl_message stores current user id, subject, time, and message

tbl_messageto stores record of tbl_message 2 times but with different id of memberId i.e.the same message is send 2 times, one for other user and the next one for the user himself.

Now I need to access all the record of particular member whose messageId =memberId .

My sql code is: [ "SELECT * FROM tbl_message m, tbl_messageto t, tbl_member u WHERE m.messageId=t.messageId AND m.authorId=t.memberId AND t.memberId=u.id AND u.id=7"])

I tried but I got errrors. And at last I compel to post in this blog.

Need your idea.

Below is the schema & relation for my table


id [PK]





messageId [PK]

authorId [current user’s id]







messageId [FK]

memberId [message receiver’s id]

authorId [message sender id i.e. current user’s id]

Message module

return array(

		'messageto' => array(self::HAS_ONE,'Messageto','messageId'),

		'member' => array(self::BELONGS_TO,'Member','authorId'),


Member module

return array(

		'message' => array(self::HAS_MANY,'Message','authorId'),


Try to use some with:

$criteria=new CDbCritera;

$criteria->with= array(‘messageto’, member);

$criteria->condition= "id=7";

$message= Message::model()->findAll($criteria);

Now you can access data of member, for example, with:


Got error:

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘authorId’ in where clause is ambiguous

That’s because you have 2 field with the same name.

You have to use a proper Alias, Yii gives to the main table the alias t, at the first in with the alias t1, then t2 and so on…

$criteria=new CDbCritera;

$criteria->with= array(‘messageto’, member);

$criteria->condition= "t1.authorId=7";

I did in the following way but couldn’t get data may be query wrong? It displays No result found.

In the above, does criteria matches :

message.messageId=messageto.messageId AND message.authorId=messageto.memberId AND messageto.memberId=member.id AND member.id=$currentUserId


		$criteria =  new CDbCriteria;

		$criteria->with = array('messageto','member');

		$criteria->condition = 'member.id=:memberId';

		$criteria->params = array(':memberId'=>$currentUserId);

		$criteria->condition = 't.messageId=:messageId';

		$criteria->params = array(':messageId'=>'messageto.messageId');

		$criteria->condition ='t.authorId=:senderId';

		$criteria->params = array(':senderId'=>'messageto.memberId');


		$dataProvider=new CActiveDataProvider('Message', array('criteria'=>$criteria));

In view I have used:

$this->widget('zii.widgets.grid.CGridView', array(


	'dataProvider' => $dataProvider,

	'columns' => array(