subqueries in ActiveRecord?

Yii 1.1 General Discussion
1

I have two AR classes AppointmentGroups and Appointments with a BELONGS_TO relationship between them in Appointment class. I want to perform an eager loading on the Appointment to retrieve all Appointments and their Groups in one query, I use with() method for this. However before joining these two tables I want to filter the Appointment table for e.g. appointments with certain IDs. When I use a call to one of the find methods like this:

$appointments = Appointment::model()->with(‘treatmentGroup’)->findAll(“appointmentID IN (:appID1,:appID2)”,array(":appID1"=>1,":appID2"=>2));

the condition is representing the WHERE clause in a SQL statement. So the SQL looks like the following:

SELECT * FROM appointments

t INNER JOIN treatment_groups treatmentGroup ON

(t.treatmentID=treatmentGroup.treatmentID) WHERE (appointmentID IN

(:appID1,:appID2))

This first joins the tables into one and then uses the WHERE statement to filter this combined table. Is there a way to make it first query one table and then use the result to join it with the other. The query I want to achieve should look sth like this:

SELECT * FROM (

SELECT  * FROM appointments 


WHERE treatmentID IN (1,2,3) 


) AS app

JOIN treatment_groups ON app.treatmentID=treatment_groups.treatmentID

So is there any way to use a subquery in a FROM clause.

2

I have same requirement i.e. i want to add sub-query in FORM clause.

I am using CDbCriteria to create CActiveDataProvider for CListView . so I need to add subquery in FROM clause by CDbCriteria .

Required query -

select * from (SELECT ,price(SELECT rate

from ca_currency_exchange WHERE ca_currency_exchange.fromC = currency_a3

AND ca_currency_exchange.toC = ‘MYR’) as ex_price

FROM ca_listings) as mtb

where ex_price > 20000

3

You can use:

CActiveRecord::findAllBySql() method: http://www.yiiframework.com/doc/api/1.1/CActiveRecord#findAllBySql-detail