Yii - Query Manipulation for Custom CGridView with Advanced Search

Question

So, I've extended CGridView 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 so that I may include comparisons.

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 set to true ...

public function search() {
    $criteria=new CDbCriteria;
    $criteria->compare('id', $this->id);
    $criteria->compare( ...
    ...
    $criteria->with = array('relation0','relation1','relation3');
    $criteria->together = true;

    return new CActiveDataProvider(
        get_class($this), array(
            'criteria'=>$criteria,
            'pagination' => array('pageSize' => 50)
));}

Then I'll snatch the criteria from the DataProvider and add a few conditions, for example, looking for dates > 1234567890. 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, here depicted as relation3, are missing. Furthermore, why is the query a SELECT COUNT(DISTINCT 't'.'id') ?

Edit @DCoder Here's the specific relation I'm working with now. The main table is Call, which has a HAS_MANY relation to CallSegments, which keeps the times. So the startTime of the Call is the minimum start_time of all the related CallSegments. And startTime is the hypothetical relation3 in my anonymized query error.

'startTime' => array(self::STAT, 'CallSegments', 'call_id',
            'select' => 'min(`start_time`)'),

Edit Other people have sent me to CDbCriteria's together property, but as you can see above, I am currently trying that to no avail.

Edit Looks like the issue has may have been reported: Yii and github tickets.

Answer 1

It is not a good idea to snatch the sql from a criteria and use it by yourself.

If you are using the "with" property then you could easily use comparisons like:

$criteria->compare("`relation1`.`id`", $yourVarHere);

Also Yii doesn't behave well with grouping.

My approach with STAT relations is using an subquery in the selects of Yii, followed by having:

$criteria->select = array("`t`.*", "(SELECT COUNT(*) FROM `relation3` WHERE `id` = `t`.id_relation3) AS `rel3`");
$criteria->having = "`rel3` > " . $yourValue;

The above method creates a bug in the gridview pagination because the count is done on a different query. A workaround will be to drop the "with" property and write the joins by yourself in the "join" property like:

$criteria->join = "LEFT OUTER JOIN `relation_table` `relation0` ON (`t`.`id`=`relation0`.`id`) 
LEFT OUTER JOIN `relation_table` `relation1` ON (`t`.`id`=`relation1`.`id`)
LEFT OUTER JOIN `relation_table` `relation3` ON (`t`.`id`=`relation3`.`id`)";