Populate data from more than 2 Modules

Archives Yii 1.0.x Discussions
1

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

tbl_member

id [PK]

email

password

name

tbl_message

messageId [PK]

authorId [current user’s id]

time

subject

text

[b]

[/b]

tbl_messageto

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'),


		);
2

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:

$message[$i]->member->email;

3

Got error:

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

4

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";

5

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(

	'id'=>'message-grid',

	'dataProvider' => $dataProvider,

	'columns' => array(

			'messageId',

			array('name'=>'From',

		      	'value'=>'$data->memberId',					

			),

			array('name'=>'Subject',

		      	'value'=>'$data->messageSubject',				

			),

			array('name'=>'Date',

		      	'value'=>'$data->messageTime',

			

			),

			array(

			'class'=>'CButtonColumn',

			),

		),		

	));