Hi all,
I love the idea of Active Records and would like to keep this in my application, however, I cannot figure out how to construct the following query:
SELECT *
FROM (
SELECT DISTINCT clients.first_name, clients.last_name, SUM( user.off_court_rate * ( (
timesheet.offcourt_end_time - timesheet.offcourt_start_time
) /60 /60 ) ) AS offcourt_total, SUM( user.on_court_rate * ( (
timesheet.oncourt_end_time - timesheet.oncourt_start_time
) /60 /60 ) ) AS oncourt_total
FROM `clients`
LEFT JOIN timesheet ON timesheet.client_id = clients.id
LEFT JOIN user ON user.id = timesheet.user_id
GROUP BY clients.id
)total
WHERE total.oncourt_total IS NOT NULL
OR total.offcourt_total IS NOT NULL
Now, I can create the sub query using this:
$this->db->select("SUM(user.off_court_rate*((timesheet.offcourt_end_time - timesheet.offcourt_start_time)/60/60)) as offcourt_total, SUM(user.on_court_rate*((timesheet.oncourt_end_time - timesheet.oncourt_start_time)/60/60)) as oncourt_total");
$this->db->from('timesheet');
$this->db->join('user','user.id=timesheet.user_id','left');
But how do I wrap THAT around another select query? My only idea is to just use a manual query for this but I’d rather not. I don’t even think my usage of Active Records is good for the sub query i wrote.
Anybody have any ideas on what I should change/do?
thanks!
-Prateek
