46 comments on “Searching and sorting a column from a related table in a CGridView

  1. You will need to add a $sort->applyOrder($criteria); after the $sort object is setup in order for this to work properly.

  2. You don’t have to add $sort->applyOrder($criteria) if you pass CSort on to CActiveDataProvider. But it looks like I didn’t add that in the post at all, so I just edited the post and added that piece of code.
    Thank you for your input!

  3. If you are editing the gii generated files and want the advanced search to work. Just add the following to the _search view

    < ?php echo $form->label($model,’postTitle’); ?>
    < ?php echo $form->textField ($model,’postTitle’,array(‘size’=>60,’maxlength’=>128)); ?>

    and although not needed consider adding the following to the array in attributeLabels() in the model

    ‘postTitle’ => ‘Post Title’,

  4. Thank you for this fantastic tutorial! Very useful.

    I believe that the sort should begin like this instead though:

    $sort = new CSort();
    $sort->defaultOrder=’t.create_time DESC’;
    $sort->attributes = array(

  5. Hi laqrhead,
    You could also do that yes, but you could also define it in attributes like statet in the post. Yii gives you that freedom!

  6. Thanks for this post! I searched countess posts about solving this problem, and your solution is the only one that worked. The thing that your solution has that the others don’t are the setter and getter functions. How did you know to add this?

    Thanks again!

  7. hi,
    this is a very nice blog. helped me very much.
    I have 2 columns that come from 2 different relational tables. when i added the first one (jobsiteName )search worked fine. when i added the second one (instanceName), the first column fails and the second one works fine. Adding the second column, overriding the first one?
    I have added the set and get methods for both the columns.

    $criteria->with = “jobsite”;
    $criteria->compare(‘jobsite.Name’, $this->jobsiteName,true);
    $criteria->with = “nvcinstance”;
    $criteria->compare(‘nvcinstance.hostname’,$this->instanceName,true);

    when searched for jobsiteName, i get an error as “Unknown column ‘jobsite.Name’ in ‘where clause ‘”

  8. @srikanth
    You’re overriding the old value of ‘with’. If you want to specify 2 relations you can add an array like this:

    $criteria->with = array(‘jobsite’, ‘nvcinstance’);

  9. @MrSoundless
    Thanks a lot. Please keep up your good work. It will help us all a lot.

  10. Hey,

    small Question/Problem.

    I have a double Relation:
    Model Bill is my Model where i want to search. There i have a relation to “Reservation” and from there to “Person”. And i want to search after the Person Details so on the relation:
    $this->Reservation->Person->name / Company / surname

    Any Hints?
    Thanks!

  11. Thanks for this fantastic solution but this leads another problem,do sort on any column and then try to move on another page (using paging’s next button) it will give you a database exception.the table alias ‘t’ which we are statically using in defining cSort object is responsible for this Database exception. I am using Mssql server database and attaching my Database query producing DB exception.

    “Select * from (select top 10 * from (select top 30 [t].[ID] as [t0_c0],[t].[Region_ID] as [t0_c1], [t].[Country_Name] as [t0_c2],[t].[Country_Code] AS [t0_c3],[country_region].[Region_ID] AS [t1_c0],[country_region].[Name] as [t1_c1],[country_region].[Description] as [t1_c2],[country_region].[RSM] as [t1_c3] from [xsec].[Country_Data] [t] LEFT OUTER JOIN [xsec].[Regional_Data][country_region] ON ([t].Region_ID = [country_region].[Region_ID]) order by t.Country_Name) as [__inner__] ORDER BY t.Country_Name DESC) as [__outer__] ORDER BY t.Country_Name ASC”.

  12. Great post.
    Any idea how to make JuiAutoComplete work as well on this type of column?
    Thank you in advance.

  13. Haven’t tried that yet, but I would guess you can just assign an instance of CJuiAutoComplete to the filter. Or doesn’t that work at all?

  14. Hi,

    When i tried, i got error:

    CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘ID’ in order clause is ambiguous. The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`username` AS `t0_c1`, `t`.`password` AS `t0_c2`, `t`.`salt` AS `t0_c3`, `t`.`full_name` AS `t0_c4`, `t`.`flag_active` AS `t0_c5`, `t`.`last_login` AS `t0_c6`, `t`.`date_created` AS `t0_c7`, `t`.`date_updated` AS `t0_c8`, `t`.`group_id` AS `t0_c9`, `groupz`.`id` AS `t1_c0`, `groupz`.`group_name` AS `t1_c1` FROM `user` `t` LEFT OUTER JOIN `group` `groupz` ON (`t`.`group_id`=`groupz`.`id`) ORDER BY ID ASC LIMIT 5

    Hmm, until now, i haven’t found the solution.. Can you help me ? Thanks..

  15. Pingback: Spalte einer verknüpften Tabelle in CGridView suchbar und sortierbar machen | vorneweg.de

  16. Hi!
    I have about 20 models where I need to use search by relation. Is there any workaround not to change them all one by one, but to do it alltogether?

  17. Hello,
    Is there one problem: When changing the pages(with pager) in gridview for fetching other records, search functionality will falls. How can solve the problem?

  18. “select reg.name,reg.id from reg inner join ( select name,email,phone from reg group by name having count(name)>1 ) dup on reg.name=dup.name order by reg.name asc
    i have problem to use these query in grid view… plz reply how to use these query in grid view.. what process needed????

    • Depends on what you’re trying to do. Please try to explain your problem. If it’s unrelated to this article, feel free to mail me (contact details in ‘about me’ page).

  19. Pingback: Yii CGridView 关联表搜索排序实例 _ PHP _ Yii _ 建站教程 _ 编程开发 _ 码农场

  20. Hello!

    Thank you for your post. It’s helpful.
    I’ve done all steps you wrote. I’ve joined 1 table with 2 columns.
    And I’ve got an error: Two properties were not defined for my main model.

    Trouble was there: First, you define the private property $_postTitle. But then you use $this->postTitle everywhere. I’ve changed these properties and methods: they use public property. And I’ve got the OK grid!

    private $_postTitle = null;
    public function getPostTitle()
    {
    if ($this->_postTitle === null && $this->post !== null)
    {
    $this->_postTitle = $this->post->title;
    }
    return $this->_postTitle;
    }
    public function setPostTitle($value)
    {
    $this->_postTitle = $value;
    }

    • PHP has the magic methods __get and __set. Yii overrides those and does some magic with them (see actual Yii source code)
      In this case, it pretty much means that calling the getter on $this->postTitle is the same as calling $this->getPostTitle() and calling the setter on $this->postTitle is the same as calling $this->setPostTitle($value)

  21. Hi

    My getPostTitle() function gets data from different tables.

    So I cannot do: $criteria->compare(‘post.title’, $this->postTitle,true);
    I want to do: $criteria->compare(‘postTitle’, $this->postTitle,true);

    Is that possible?

  22. Hi Fatih,

    my issue is about related to related models. So i have main Row model, and a related model called Tournament. In this model there are more related columns like sport. So there is a model Sport with column name. I tried this search function of Row model:


    public $gender_search;
    public $sport_search;

    //gender_search and sport_search are safe in 'rules' on search

    public function search() {
    // Warning: Please modify the following code to remove attributes that
    // should not be searched.

    $criteria = new CDbCriteria;
    $criteria->with = array('tournament',array('sport'));

    $criteria->compare('id', $this->id);
    $criteria->compare('round_id', $this->round_id);
    $criteria->compare('row_id', $this->row_id, true);
    $criteria->compare('tournament.gender', $this->gender_search, true);
    $criteria->compare('tournament.sport.name', $this->sport_search, true);
    $criteria->compare('t.name', $this->name, true);
    $criteria->compare('tournament_id', $this->tournament_id);
    $criteria->compare('close_date', $this->close_date, true);
    $criteria->compare('created', $this->created, true);
    $criteria->compare('modified', $this->modified, true);

    return new CActiveDataProvider($this, array(
    'criteria' => $criteria,
    'sort' => array(
    'attributes' => array(
    'gender_search' => array(
    'asc' => 'tournament.gender',
    'desc' => 'tournament.gender DESC',
    ),
    'sport_search' => array(
    'asc' => 'tournament.sport.name',
    'desc' => 'tournament.sport.name DESC',
    ),
    '*',
    ),
    ),
    ));
    }

    in the view calling the gridview widget i have this columns:


    'columns' => array(
    'round_id',
    'row_id',
    'name',
    array(
    'name' => 'sport_search',
    'header' => 'Sport',
    'value' => '$data->tournament->sport->name',
    'htmlOptions' => array(
    'width' => '100',
    ),
    ),
    array(
    'name' => 'gender_search',
    'header' => 'Nem',
    'value' => '$data->tournament->gender',
    'htmlOptions' => array(
    'width' => '100',
    ),
    ),
    ),

    this is my error message on sort:
    CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘tournament.sport.name’ in ‘order clause’. The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`round_id` AS `t0_c1`, `t`.`row_id` AS `t0_c2`, `t`.`name` AS `t0_c3`, `t`.`tournament_id` AS `t0_c4`, `t`.`close_date` AS `t0_c5`, `t`.`created` AS `t0_c6`, `t`.`modified` AS `t0_c7`, `tournament`.`id` AS `t1_c0`, `tournament`.`sport_id` AS `t1_c1`, `tournament`.`gender` AS `t1_c2`, `tournament`.`name` AS `t1_c3` FROM `tippmix_row` `t` LEFT OUTER JOIN `tippmix_tournament` `tournament` ON (`t`.`tournament_id`=`tournament`.`id`) WHERE (round_id=:ycp0) ORDER BY tournament.sport.name LIMIT 10 (C:\wamp\www\yii-1.1.13\framework\db\CDbCommand.php:541)#0 C:\wamp\www\yii-1.1.13\framework\db\CDbCommand.php(395): CDbCommand->queryInternal(‘fetchAll’, Array, Array)

    and this is my error message on search:
    CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘tournament.sport.name’ in ‘where clause’. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `tippmix_row` `t` LEFT OUTER JOIN `tippmix_tournament` `tournament` ON (`t`.`tournament_id`=`tournament`.`id`) WHERE ((round_id=:ycp0) AND (tournament.sport.name LIKE :ycp1)) (C:\wamp\www\yii-1.1.13\framework\db\CDbCommand.php:541)#0 C:\wamp\www\yii-1.1.13\framework\db\CDbCommand.php(432): CDbCommand->queryInternal(‘fetchColumn’, 0, Array)

    So can anybody help?
    thanks Laszlo from Hungary

    • Hi Laszlo,

      I believe you’re using with() the wrong way. You should try with = array(‘tournament’,'tournament.sport’);

      Hope this helps
      Kind regards,
      Fatih

  23. Pingback: yii 关联表的CActiveDataProvider排序 | 嘻咯咯

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>