Gridview Problem

Yii 1.1 General Discussion
1

I have a table message( id, subject, sender,receiver,send_time) where receiver stores the profile_id of profile table containing (id,first_name,last_name) and sender stores the user_id who is sending messages and another table mail(id,subject,sender,receiver,send_time) where receiver stores email address having no references.

$criteria1 = new CDbCriteria;

$criteria->with=’messageprofile’;

$criteria->select = ‘ sender, messageprofile.first_name, send_time, subject ‘ ;

$dataProvider=new CActiveDataProvider(‘Messages’, array(‘criteria’ => $criteria,));

$criteria2 = new CDbCriteria;

$criteria->select = ‘ sender, receiver, send_time, subject ‘ ;

$dataProvider2=new CActiveDataProvider(‘Mails’, array(‘criteria’ => $criteria2,));

$dataProvider= new CArrayDataProvider(array_merge($messages, $mails), array(

'sort'=> array(


	'attributes'=> array(


		'send_time DESC',


	),


),

));

In my view page i am using CGridView to view data.I want to show a column of subject,send_time, sender and receiver where receiver includes names in case of message table and email addresses in case of mail table.

Relation is:

array(

‘messageprofile’ => array(self::BELONGS_TO, ‘Profiles’, ‘receiver’),

)

Error:

Active record “Messages” is trying to select an invalid column “messageprofile.first_name” . Note, the column must exist in the table or be an expression with alias.

Please help me.

2

I’ve spent a while thinking about this one. I think you cannot solve your problem this way. I’d suggest to fall back to using CSqlDataProvider and construct a query using a union. I guess the query would have to be something like this:


SELECT `sender`, `profile`.`first_name` AS first_name, `send_time`, `subject` FROM `message` LEFT JOIN `profile` ON `receiver`=`profile`.`id` UNION ALL SELECT `sender`, `first_name`, `send_time`, `subject` FROM `mail`
3

I have tried your way but still its displaying error: messageprofile.first_name does not exist in this table.

In my Controller:


$sql='SELECT `sender`, `messageprofile`.`first_name` AS first_name, `send_time`, `subject` FROM `messages` LEFT JOIN `profiles` ON `receiver`=`profiles`.`id` UNION ALL SELECT `sender`, `receiver`, `send_time`, `subject` FROM `mails` ' ;


		$dataProvider=new CSqlDataProvider($sql, array(

			'sort'=>array(

				'attributes'=>array(

					'send_time',

				),

			),

			'pagination'=>array(

				'pageSize'=>10,

			),

		));

In my view:


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

	'id'=>'mails',

	'dataProvider'=>$dataProvider,	

));?>

Error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘messageprofile.name_first’ in ‘field list’.

4

Please try [font=“Courier New”]profile.first_name AS first_name[/font] as I wrote before ;)

5

Uh, there are also some other mistakes in your query. Please c&p mine and see what happens. Also: CSqlDataProvider needs a value for totalItemCount if you want working pagination.

6

I have used your query also but still its giving the same error:


Column not found: 1054 Unknown column 'first_name' in 'field list'. The SQL statement executed was: SELECT `sender`, `profile`.`first_name` AS first_name, `send_time`, `subject` FROM `message` LEFT JOIN `profile` ON `receiver`=`profile`.`id` UNION ALL SELECT `sender`, `first_name`, `send_time`, `subject` FROM `mail` LIMIT 10
7

Ah yes, that’s indeed been an error on my part. Terribly sorry.


SELECT `sender`, `profile`.`first_name` AS receiver, `send_time`, `subject` FROM `message` LEFT JOIN `profile` ON `receiver`=`profile`.`id` UNION ALL SELECT `sender`, `receiver`, `send_time`, `subject` FROM `mail`