Problem with CGridview

Hi, I have some problems with CGridview, I’v created a search that searches through two relational tables (that works).

First of all, the CGridview is only showing 3 rows per page but it’s set at showing 10, when I do the search it shows 5 rows per page, and on some pages it shows only one.

Second problem is when I implement a standard search(a search in the current model), it provides a query-error.

When I remove the “with” in the search() function, everything is back to normal (but ofcourse the relational search doesn’t work).

Is there any obvious errors in my code?


public $author_search;

public function rules()


		return array(

			array('..., author_search', 'safe', 'on'=>'search'),



public function relations()


		return array(

			'authorgroup' => array(self::HAS_MANY, 'AuthorGroup', 'author_id')),



public function search()


		$criteria=new CDbCriteria;


		$criteria->with = array('authorgroup'=>array('together'=>true,'with'=>'author'));






		return new CActiveDataProvider($this, array(



                'defaultOrder'=>'t.post_title ASC',







And this is in the _search:

   		<?php echo $form->label($model,'author_search'); ?>

		<?php echo $form->textField($model,'author_search'); ?>


		<?php echo $form->label($model,'post_title'); ?>   

		<?php echo $form->textField($model,'post_title'); ?> //<--------------THIS IS NOT WORKING

What is your current model?

I’m not sure what model has the attribute named ‘post_title’.

Would you please elaborate the relations among ‘Author’, ‘AuthorGroup’ and probably ‘Post’.

My current model is "Post", so the post_title attribute belongs to that model.

Post relates to AuthorGroup via author_id (HAS_MANY relation)

AuthorGroup relates to Author via author_group_id(pk) (BELONGS_TO relation)

I’m sorry I don’t understand the relations.

What I can imagine for the relations would be something like the following when I think of ‘Post’, ‘Author’ and ‘AuthorGroup’.


Post BELONGS_TO Author

& Author HAS_MANY Posts

... A post is written by a single author, and an author writes one or more posts.


Author BELONGS_TO AuthorGroups

& AuthorGroup HAS_MANY Authors

... An author can belong to a single group, and a group can have many authors.


Author HAS_MANY AuthorGroups

& AuthorGroup HAS_MANY Authors

... An author can belong to multiple groups, and a group can have many authors.

I believe these are the usual relations among the three.

Is the following definitions correct for your ‘Post’, ‘AuthorGroup’ and ‘Author’?


Post HAS_MANY AuthorGroups

& AuthorGroup BELONGS_TO Posts


AuthorGroup BELONGS_TO Author

& Author HAS_MANY AuthorGroups

I just want to make clear the relations in order to understand your issue.

As far as I know, you can not expect a correct number of results displayed in CGridView when you filter it by an attribute of a HAS_MANY relation.

As for the search by ‘post_title’, make sure that it is set ‘safe’ on ‘search’ scenario.

And you have to use ‘t.post_title’ to disambiguate when Author or AuthorGroup has an attribute with the same name.

It’s a bit confusing, sorry for being unclear.

AuthorGroup connects Posts and Author, because a post can have several authors(yes it sounds wierd but this is a special case)

So Post connects to AuthorGroup with HAS_MANY and then AuthorGroup connects to Author with BELONGS_TO

If I change HAS_MANY to BELONGS_TO, the search still work but the CGridview is still looking wierd.

I think I understand the post you wrote

So you don’t think there is a fix?

In short, we can’t search without ‘together’, and we can’t get correct page sizes with ‘together’.

I think there’s no straight way to fix it, as long as we want make use of HAS_MANY AR relations in search.

But we can use an old SQL way like the following:

// We don't use AR relations

// $criteria->with = array('authorgroup'=>array('together'=>true,'with'=>'author'));

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

if ($this->author_search != '')





			FROM author a

			INNER JOIN author_group ag ON = ag.author_group_id

			WHERE ag.author_id = AND LIKE :name



	$criteria->params[':name'] = '%' . $this->author_search . '%';


The code is not tested and I’m not sure if it works fine.

I would appreciate your feedback. :)

I can’t get it to work, played around with it but getting an sql-error.

I should still write the code in the search() function, right?

Is there another way? Maybe you missed something in your example?

Maybe there is a way to force CGridview to show 10 rows?

I really appreciate your help!

Ah, sorry. Not “EXIST()” but “EXISTS()”. :-[

It may have other errors …

Could you show me the sql error?

Changed EXIST() to EXISTS(), but still not working

this is the error:

Error 500: <h1>CDbException</h1>

<p>CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 208 General SQL Server error: Check messages from the SQL Server [208] (severity 16) [(null)]. The SQL statement executed was: SELECT COUNT(*) FROM…

and then followed by the query I wrote in the model

Any ideas?

208 stands for "Invalid object name" …

Could you show all the error message including the SQL statement executed? And didn’t you notice any table name or column name that is not in your db?

Got it to work!

I used the name of the relations instead of typing out the table name itself in the query.

Thank you SO much for your help! ;D

btw, is there any downsides by using this method?

I’m not sure if it has any significant performance drawback. :)

I think "IN" clause might be also used for this kind of scenario.

Something like this:

if ($this->author_search != '')



		' IN (

			SELECT DISTINCT ag.author_id

			FROM author_group ag

			INNER JOIN author a ON = ag.author_group_id

			WHERE LIKE :name



	$criteria->params[':name'] = '%' . $this->author_search . '%';


Again, I really don’t know which is faster and more appropriate.