Sub-select with AR

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?

select (select COUNT(*) from treatment WHERE treatment.procedure_id = procedure.id) as times, procedure.code FROM procedure ORDER BY times DESC

rewrite this to join:

SELECT COUNT(*) as times, p.code FROM procedure p LEFT JOIN treatment t ON (t.procedure_id = p.id) GROUP BY p.id

now you need to create treatment model and read this

So add




    public function relations()

    {

        return array(

            'times' => array(self::STAT, 'Treatment', 'procedure_id'),

        );

    }



Procedure::model()->with(‘times’)->findAll() will generate 2 SQL query (not really 1 with join) so ORDER by time can be trouble here (sure, u can sort it using php). If you need to generate 1 query with JOIN - can try to use HAS_MANY relation.

If tables name has aliases - they MUST be used in query (ex: FROM table as t1 - now you can use t1 name only but can’t table). AR add aliases to all tables. Primary table (table of current model) has alias t and other tables has alias same as relation name.