I am trying to figure out the best way within Yii to have the ORDER BY performed before the GROUP BY for a SQL query.
I have a table of appointments as:
int appt_id int customer_id datetime appt_datetime int appt_length
Customers can have multiple appointments for each day.
I want a list of the first appointment for each customer scheduled on a certain day.
The SQL statement that would perform this uses a subquery:
SELECT * FROM ( SELECT * FROM appointments WHERE DATE(appt_datetime) = DATE("2012-02-01") ORDER BY datetime } as t1 GROUP BY customer_id
I’d just use the MIN(appt_datetime) if I needed just the appointment time, however I also need the length and appt_id of this appointment.
What is the best way to accomplish this in Yii.
I’m avoiding the use of findBySql() as I’d like to easily be able to change my DB system in the future and really don’t want to have to rewrite a boat load of SQL statements.
Thanks in advance,