Searching for a value in a related table

Yii 1.1 General Discussion
1

I’ve got 2 simple tables:

message

id

subject

message_author_id

message_author

id

name

The relationship between the two tables is that one message_author can create multiple messages;

i.e., there’s a one-to-many relationship between message_author and message.

My goal is, when administering the message table, to be able to search on the messge_author.name field.

Here’s what I did, but searching by message author is turning up ALL messages in the message table:


// 1. Message model. Added new public property, messageAuthorFromName:

public $messageAuthorFromName;[


// 2. Message model. Added function afterFind(), so that the new property gets set when we search:

public function afterFind() {

		$this->messageAuthorFromName = $this->message_author->from_name;

		parent::afterFind();

		return true;

	}




// 3. Message model, relations function, generated by Gii:

[public function relations()

	{

		return array(

			'message_author' => array(self::BELONGS_TO, 'MessageAuthor', 'message_author_id'),

		);

	}


// 4. Message model, rules() function

Added 'message_author' to the list of attributes that will be searchable by the user


// 5. Message model, attributeLabels() function. Added label:

'messageAuthorFromName' => 'Message Author',


// 6. Message model, search() function:

public function search()

{

    $criteria=new CDbCriteria;

    $criteria->with = array('message_author');

    $criteria->addSearchCondition('message_author.from_name', $this->messageAuthorFromName, true);

    return new CActiveDataProvider(get_class($this), array(criteria'=>$criteria));

}

// 7. Message view, _search.php includes a textfield for searching the messageAuthor:

[html] <div class="row">

	&lt;?php echo &#036;form-&gt;label(&#036;model,'messageAuthorFromName'); ?&gt;


	&lt;?php echo &#036;form-&gt;textField(&#036;model,'messageAuthorFromName',array('size'=&gt;60,'maxlength'=&gt;255)); ?&gt;


&lt;/div&gt;[/html]

Can anyone spot what I’m missing? My hunch is that the search condition is being ignored.

I.e., in the code below, $this->messageAuthorFromName is BLANK, hence all results get returned:




public function search()

{

    $criteria=new CDbCriteria;

    $criteria->with = array('message_author');

    $criteria->addSearchCondition('message_author.from_name', $this->messageAuthorFromName, true);

    return new CActiveDataProvider(get_class($this), array(criteria'=>$criteria));

}

Any help appreciated!

Thanks!

Emily

2

Oops! I made one mistake in the MySQL definition:

The message_author table contains a column named "from_name" (not "name").

3

Typing that all up neatly made me realize the error:

In my rules() function in the Message model, I had a typo when entering the searchable fields.

Changing it to

messageAuthorFromName

solved the issue!

4

Drat! I spoke too soon. This implementation causes ADVANCED search to work, but regular search continues to return ALL results, no matter what I enter for the message author!

5

Aha. The admin.php view had to be modified so that regular search works. Voila!


 $this->widget('zii.widgets.grid.CGridView', array( 	'id'=>'message-grid', 	'dataProvider'=>$dataProvider, 	'filter'=>$model, 	'columns'=>array( 					 array('name'=>'messageAuthorFromName', 'type'=>'html', 'value'=>'$data->message_author->from_name'), 		'message_key', 		'subject',  		array( 			'class'=>'CButtonColumn', 		), 	), )); ?>

Hope someone else can find this of use!

Emily

6

Nice self talk… ;)

Glad you solved your problem…

You could have used "$this->message_author->from_name" everywhere instead of "$messageAuthorFromName"

7

Okay, I found the error of my ways, am posting here for anyone who would like the complete solution.

I had 2 problems:

  1. It turns out my Message model DOES indeed require a public property

    message_author_from_name

  2. The rules() function in my Message model was incorrect.

    Because message_author_from_name is NOT a column in the database, it has to

    be explicitly added–and NOT with the qualifier "on=search". See below.


// 1. Message model. Add new property message_author_from_name

public $message_author_from_name;

 

// 2. Message::rules()

// Since message_author_from_name has been explicitly added to this ActiveRecord class,

// and it is not a column in the db table, tell the model class to allow this field to 

// be set in a bulk manner when the setAttributes() method is called

array('message_author_from_name', 'safe'),


// 3. Message::relations()

public function relations() {

   return array('message_author' => array(self::BELONGS_TO, 'MessageAuthor', 'message_author_id'));

}

                

// 4. Message::attributeLabels()

public function attributeLabels() {

   return array('message_author_from_name' => 'Message Author');

}


// 5. Message::search()

public function search()  {             

   $criteria=new CDbCriteria;

   $criteria->with = array('message_author');

   $criteria->addSearchCondition('message_author.from_name', 

                                 $this->message_author_from_name, true);

   return new CActiveDataProvider(get_class($this), array('criteria'=>$criteria));

}


// 6. View : _search.php

echo $form->label($model,'message_author_from_name');

echo $form->textField($model,'message_author_from_name'); 


// 7. View : admin.php

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

        'id'=>'message-grid',

        'dataProvider'=>$dataProvider,

        'filter'=>$model,

        'columns'=>array(

                array('name'=>'message_author_from_name', 'type'=>'html', 

                      'value'=>'$data->message_author->from_name'),

                array(

                        'class'=>'CButtonColumn',

                ),

        )));

Happy coding. Yii is great!

Emily