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
.id
) FROM company
t
LEFT OUTER JOIN company edits ON
edits.reference_id = t.id LEFT OUTER JOIN user
user
ON
(t
.last_updated_user_id
=user
.id
) WHERE (t.editCount LIKE :ycp0)’ in /var/www
/html/yii-1.1.7.r3135/framework/db/CDbCommand.php:518[/sql]
How do I get Yii to synthesize the editCount parameter for the filtering by the STAT relation?