There are many topics pointing out how to filter on HAS_MANY relations and other related situations. There is information on joining to related tables, but I have been unable to find a real example of how to filter when you have a STAT relation to the same table. I’ve got sorting working but not searching / filtering.
Here’s my relation in model Company:
'editCount' => array(self::STAT, 'Company', 'reference_id'),
now in Company::search():
$criteria = new CDbCriteria; // because we need the STAT editCount for sorting, we need to synthesize our own query. // if there is a more AR way to do this someone let me know! $criteria->select = 't.*, count(edits.id) AS editCount'; $criteria->join = 'LEFT OUTER JOIN company edits ON edits.reference_id = t.id'; $criteria->group = 't.id'; // we also want to include the user record fields for the related user (last_updated_by_user_id) $criteria->with = array('user', 'editCount');
The grid itself loads fine. I am displaying editCount as one of the grid columns. I can sort on editCount properly as I have it defined as the following attribute of the sort associated with the CActiveDataProvider:
'editCount' => array('asc' => 'editCount', 'desc' => 'editCount desc'),
When I submit the filter textfield in the CGridView this search is associated with, I get the following SQL error:
[sql]2011/06/29 19:59:13 [error] [exception.CDbException] exception ‘CDbException’ with
message 'CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not
found: 1054 Unknown column ‘t.editCount’ in ‘where clause’. The SQL statement executed
was: SELECT COUNT(DISTINCT
t LEFT OUTER JOIN company edits ON
edits.reference_id = t.id LEFT OUTER JOIN
id) WHERE (t.editCount LIKE :ycp0)’ in /var/www
How do I get Yii to synthesize the editCount parameter for the filtering by the STAT relation?