Cgridview Filter On Related Table

Yii 1.1 General Discussion
1

Hi!

I’m having a bit of trouble filtering a table column on a related table, where the table structure is such that:

Grid displays:

  1. Book Id

  2. Book Name

  3. Book Author Name/s

Where Books can have multiple authors and a related by an intermediary table of structure:

book_authors

BOOK_ID

AUTHOR_ID

While I can successfully get a filter at the top of the grid column filled with author names:




'columns' => array(

			...,

			...,     

			...,   

			array(

				'name' => 'assignedAuthor',

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

				'type' => 'html',

				'htmlOptions'=>array('width'=>'15%'),

				'filter'=>GxHtml::listDataEx(Author::model()->findAllAttributes(null, true,' AUTHOR_ID IN (SELECT DISTINCT AUTHOR_ID FROM book_authors) ')),

                ),

			...,

The filter won’t actually work…

I think possibly I’m a bit confused by the findAllAttributes option…

Can anyone please point me in the right direction?

Thanks!!

2

Post your whole code for that section please and also your model search and relations.

Also here is the wiki for it

You should read the wiki if that doesn’t help watch the video after you read it.

A you tube video that follows the wiki however, it’s in Spanish but you can see what he does.

Hope that helps

3

Search Widget:




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

	'id' => 'book-grid',

	'dataProvider' => $model->search(),

	'filter' => $model,

	'columns' => array(

			array('name'=>'BOOK_NAME','header'=>'Book Title','htmlOptions'=>array('width'=>'20%')),     

			array(

				'name'=>'YEAR_OF_PUB',

				'header'=>'Year',

				'htmlOptions'=>array('width'=>'5%')

			),   

			 array(

                                'name' => 'assignedAuthor',

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

                                'type' => 'html',

                                'htmlOptions'=>array('width'=>'15%'),

                                'filter'=>GxHtml::listDataEx(Author::model()->findAllAttributes(null, true,' AUTHOR_ID IN (SELECT DISTINCT AUTHOR_ID FROM book_authors) ')),

                ),

));

Model::Book




$dataProvider=new CActiveDataProvider($model,

        array(

                'criteria'=>array(

		     'with'=>array('author'),

		     'together'=>true)

                )

        );

Relations:




public function relations()

    {

        return array(

            'type'=>array(self::BELONGS_TO, 'BookType', 'BOOK_TYPE_ID'),

	    'author'=>array(self::MANY_MANY, 'Author','book_author(BOOK_ID, AUTHOR_ID)'),		

        );

    }

Search:




public function search() {

        $criteria = new CDbCriteria;


	$criteria->compare('BOOK_ID', $this->BOOK_ID);

	$criteria->compare('BOOK_TYPE_ID', $this->BOOK_TYPE_ID);

        $criteria->compare('BOOK_NAME', $this->BOOK_NAME);

        $criteria->compare('YEAR_OF_PUB', $this->YEAR_OF_PUB);

	$criteria->compare('TITLE', $this->TITLE, true);

	$criteria->compare('PUBLISHER', $this->PUBLISHER, true);


	if (isset($_REQUEST["assignedAuthor"]) && is_numeric($_REQUEST["assignedAuthor"]))

	{

	         $criteria->join = "inner join book_author on book_author.BOOK_ID=t.BOOK_ID AND book_author.AUTHOR_ID='".$_REQUEST["assignedAuthor"]."'";

	}


}
4

Dear Friend

I hope the following would be helpful.

1.Declare a virtual property in Book Model.




$public assignedAuthor.

2.Make it safe on search.




array('assignedAuthor', 'safe', 'on'=>'search'),

3.Create relationship with intermediate table book_author




public function relations()

    {

        return array(

            'type'=>array(self::BELONGS_TO, 'BookType', 'BOOK_TYPE_ID'),

             'bookAuthors' => array(self::HAS_MANY, 'BookAuthor', 'BOOK_ID'),

            'author'=>array(self::MANY_MANY, 'Author','book_author(BOOK_ID, AUTHOR_ID)'),               

        );

    }

4.Create a public function in Book Model to fetch the authors name as a string.




public function fetchAuthors(){

		$authors=array();

		foreach($this->author as $author)

			$authors[]=$author->name;

	   return implode($authors,',');

	}

5.Modify the search methodin Model




public function search() {

        $criteria = new CDbCriteria;

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

        $criteria->together=true;

        $criteria->compare('BOOK_ID', $this->BOOK_ID);

        $criteria->compare('BOOK_TYPE_ID', $this->BOOK_TYPE_ID);

        $criteria->compare('BOOK_NAME', $this->BOOK_NAME);

        $criteria->compare('YEAR_OF_PUB', $this->YEAR_OF_PUB);

        $criteria->compare('TITLE', $this->TITLE, true);

        $criteria->compare('PUBLISHER', $this->PUBLISHER, true);


        if(isset($this->assignedAuthor))

		    $criteria->condition="bookAuthors.AUTHOR_ID=$this->assignedAuthor";


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

}

6.In CGrid make a column like this.




array(...............................................................

		'name'=>'assignedAuthors',

		'header'=>'Authors',

		'value'=>'$data->fetchAuthors()',

		'filter'=>CHtml::listData(Author::model()->findAll(),"id","name"),

		),

.....................................................................

I hope I helped a bit.

5

Thank-you!

You are a legend, it’s all working now!

6

Good to see that. Sorry I was late to reply