Gridview Problem

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


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



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.

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`

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(










In my view:

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





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

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

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.

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

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`