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.