Yii - Custom GridView with Multiple Tables
- by savinger
So, I've extended GridView to include an Advanced Search feature tailored to the needs of my organization.
Filter - lets you show/hide columns in the table, and you can also reorder columns by dragging the little drag icon to the left of each item.
Sort - Allows for the selection of multiple columns, specify Ascending or Descending.
Search - Select your column and insert search parameters. Operators tailored to data type of selected column.
Version 1 works, albeit slowly. Basically, I had my hands in the inner workings of CGridView, where I snatch the results from the DataProvider and do the searching and sorting in PHP before rendering the table contents.
Now writing Version 2, where I aim to focus on clever CDbCriteria creation, allowing MySQL to do the heavy lifting so it will run quicker. The implementation is trivial when dealing with a single database table. The difficulty arises when I'm dealing with 2 or more tables... For example, if the user intends to search on a field that is a STAT relation, I need that relation to be present in my query.
Here's the question. How do I assure that Yii includes all with relations in my query so that I include comparisons? I've included all my relations with my criteria in the model's search function and I've tried CDbCriteria's together ...
public function search() {
$criteria=new CDbCriteria;
$criteria->compare('id', $this->id);
$criteria->compare( ...
...
$criteria->with = array('relation1','relation2','relation3');
$criteria->together = true;
return new CActiveDataProvider(
get_class($this), array(
'criteria'=>$criteria,
'pagination' => array('pageSize' => 50)
));}
But I still get errors like this...
CDbCommand failed to execute the SQL statement:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.relation3' in 'where clause'.
The SQL statement executed was:
SELECT COUNT(DISTINCT `t`.`id`) FROM `table` `t`
LEFT OUTER JOIN `relation_table` `relation0` ON (`t`.`id`=`relation0`.`id`)
LEFT OUTER JOIN `relation_table` `relation1` ON (`t`.`id`=`relation1`.`id`)
WHERE (`t`.`relation3` < 1234567890)
Where relation0 and relation1 are BELONGS_TO relations, but any STAT relations are missing. Furthermore, why is the query a SELECT COUNT(DISTINCT 't'.'id') ?
