CGridView Filter >> HAS_MANY Column

I have three tables:

books

authors

author_books

Since a book can have multiple authors, we use the author_books table in a HAS_MANY relationship with the books table, rather than a single author_id in the books table.

In my Books class I have:

	public function relations()

	{

          'bookAuthors' => array(self::HAS_MANY, 'BookAuthors', 'book_id'),

          // other relations

	}


        public function search()

	{

          $criteria=new CDbCriteria;

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

          // other criteria

        }

In my /views/books/admin.php I have:

	'columns'=>array(

		array('name' => 'bookAuthors', 'type' => 'raw', 'value' => 'Authors::model()->getAuthorNames($data->bookAuthors)', 'filter'=>CHtml::listData(Authors::model()->findAll(array('order'=>'author_name ASC')), 'id', 'author_name')),

                // other columns

		array(

			'class'=>'CButtonColumn',

		),

	),

The above works nicely, returning the correct authors for a book, plus a drop menu of authors for the filter.

What do I need to add in the search(), rules() etc. to have this filter return any book that has a selected author in the author_books table?

Try put some thing like this

$criteria->compare(‘bookauthors.author_id’, $_GET[‘bookAuthors’]);

I’m pretty sure it will be $_GET[‘books’][‘bookAuthors’]… will give it a fly.

Did it work?

No, that just gives an "undefined index" error.

I tried:

  $criteria->compare('bookAuthors.author_id',$this->bookAuthors);

No joy

try set the together property

$criteria->together = true;

Ok, got it working. Will give a step by step for the benefit of others:

1. Declare a public variable in your class file, in my case "Books":

  public $authors;

2. Make sure that you add that variable at the bottom of your "rules" method in the array that ends with:

  'on'=>'search'

3. Within the relations method, make sure that your relation is declared:

  'bookAuthors' => array(self::HAS_MANY, 'BookAuthors', 'book_id'),

4. Within the "search" method, add something like the following:

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

  $criteria->together = true;

  $criteria->compare('bookAuthors.author_id',$this->authors, true);

5. In your admin.php view file add something like the following:

  array('name' => 'authors', 'type' => 'raw', 'value' => 'Authors::model()->getAuthorNames($data->bookAuthors)', 'filter'=>CHtml::listData(Authors::model()->findAll(array('order'=>'author_name ASC')), 'id', 'author_name')),

The getAuthorNames() function above lists all the authors of a particular book.

Thats it!

Ok, now I have another curly one.

I have a table ‘book_categories’ which stores each book in multiple categories using: categories_id, book_id.

Yii’s CRUD picked up on this relationship and in my model class relations() for ‘categories’ (main categories table) it is as follows:

  'categories' => array(self::MANY_MANY, 'Categories', 'book_categories(book_id, categories_id)'),

How should this be done in the model seach() >> $criteria->compare() ?

There are two issues:

[list=1]

[*]In step 5, a query is now run for every row in the CGridView significantly increasing overhead. Is it possible to eager load the relational records in the controller?

[*]In step 4, the criteria interferes with the pagination limit. In this example, if you have one book with ten authors, only one book will be shown in the grid. It might be possible to get around this by using GROUP_CONCAT to build the authors field in the query.

[/list]