problems with relational data in a gridview

Hi all,

i have two tables user_message and messge whose structure is:

[size="2"]CREATE TABLE IF NOT EXISTS user_message (

id int(32) NOT NULL AUTO_INCREMENT,

from_username varchar(50) COLLATE utf8_unicode_ci NOT NULL,

to_id int(32) NOT NULL,

message_id int(32) NOT NULL,

PRIMARY KEY (id),

KEY from_id (from_username),

KEY to_id (to_id),

KEY message_id (message_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

ALTER TABLE user_message

ADD CONSTRAINT user_message_ibfk_2 FOREIGN KEY (to_id) REFERENCES user (id),

ADD CONSTRAINT user_message_ibfk_3 FOREIGN KEY (message_id) REFERENCES message (id);

[/size]

and

[size="2"]CREATE TABLE IF NOT EXISTS message (

id int(32) NOT NULL AUTO_INCREMENT,

subject varchar(50) CHARACTER SET latin1 NOT NULL,

body mediumtext CHARACTER SET latin1 NOT NULL,

is_read tinyint(1) NOT NULL DEFAULT ‘0’,

created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

is_deleted tinyint(1) NOT NULL DEFAULT ‘0’,

PRIMARY KEY (id),

KEY subject (subject),

KEY is_read (is_read),

KEY created_at (created_at),

KEY deleted (is_deleted)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;[/size]

in my userMessage model i’ve created a method like this:

[size="2"]

public function searchPerToReadMessage($user_id,$subject=""){





    $criteria=new CDbCriteria;


    $criteria->select = "t2.subject,t.from_username,t2.created_at";


    $criteria->join = 'JOIN message as t2 ON t2.id = t.message_id';


    $criteria->condition ="t.to_id=".(int)$user_id." and t2.is_read=0 and t2.is_deleted=0";


    $criteria->addSearchCondition('t2.subject',$subject,true);





    


    return new CActiveDataProvider(get_class($this), array(


        'criteria' => $criteria,


    ));


}

[/size]

And in my view i want to display a gridview with the subject, sender’s username and the send date; so i’ve written something like…

[size="2"]

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

'id'=>'classifica_tech',


'dataProvider'=>$model->searchPerToReadMessage(45),


    'enableSorting'=>false,


'columns'=>array(


            'message.subject'=>array('name'=>'message.subject','value'=>$data->message->subject),


            'from_username',


            'message.created_at'=>array('name'=>'message.created_at','value'=>$data->message->created_at)





),

));

[/size]

I get no errors but both subject and created_at fields are left blank, i’ve looked the “Application Log” where i found this:

[size="2"]

SELECT t2.subject,t.from_username,t2.created_at FROM

user_message t JOIN message as t2 ON t2.id = t.message_id WHERE

(t.to_id=45 and t2.is_read=0 and t2.is_deleted=0) AND (t2.subject LIKE

:ycp0) LIMIT 10

[/size]

I tried this query on my phpmyadmin (replacing “:ycp0” with ‘%%’) and it works fine, why then the gridview doesn’t display the fields?

Try using the with (you must have the relation specified in the active record class) and without select:




$criteria=new CDbCriteria;

$criteria->width= array('message');

$criteria->condition ="t.to_id=".(int)$user_id." and t2.is_read=0 and t2.is_deleted=0";

$criteria->addSearchCondition('t2.subject',$subject,true);




I’ve SOLVED!

just modified this…

    $criteria=new CDbCriteria;


    $criteria->join = 'INNER JOIN message t2 ON t2.id = t.message_id';


    $criteria->condition ="t.to_id=".(int)$user_id." and t2.is_read=0 and t2.is_deleted=0";


    $criteria->with = array('message'=>array('select'=>'subject,created_at'));


    $criteria->addSearchCondition('message.subject',$subject,true);





    


    return new CActiveDataProvider(get_class($this), array(


        'criteria' => $criteria,


    ));

And all started working fine ;)

seems i’ve solved in the meantime you posted the reply, anyway your suggestion was correct and i want to thank you for the reply.

Yeha!

Now you have got an extra Join in the condition, you can delete it.

Thank for kind answer!