Hey guys,
So, I have a situation where I’d like to be able to use the AR API within Yii to accomplish this (so I can use CGrid, etc)
I have two tables, one called Procedure, one called Treatment. Treatment contains Procedures essentially.
I want to count how many treatments have a certain procedure (so grouping), here’s my SQL query:
$sql = "select (select COUNT(*) from treatment WHERE treatment.procedure_id = procedure.id) as times, procedure.code FROM `procedure` ORDER BY times DESC";
$rows = Yii::app()->db->createCommand($sql)->queryAll();
and this returns just fine, without any issues. However, doing this in AR Form:
$procedures = Procedure::model()->with(array(
'treatments'=> array(
'select'=>'(select COUNT(*) from treatment WHERE treatment.procedure_id = procedure.id) as times'
),
))->findAll();
gives me the following error: CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘procedure.id’ in ‘where clause’
I don’t get it. it’s defined and it’s a column within procedure.
can you guys assist me with this?