What is the best method to make use of CI's DB query builder class in the example below?
Example query -
SELECT
*, IF( play_date < CURDATE() , 1, 0 ) playdate
, DATE_FORMAT(game_date, '%d/%m/%Y') game_date
, status
, (TO_DAYS(match_date) - TO_DAYS(CURDATE())) match_in_days
, (select coachname FROM coaches WHERE coach_id = contact.assigned_to_coach_id) coachname
FROM contact
INNER JOIN contact_status ON contact_status.status_id = contact.status_id
AND contact.status != 'inactive'
HAVING followup_in_days < 0
I know how to use query builder and I also know that I can run the above query this $this->db->query($above_sql);
But what I am interested to achieve is to be able to add $this->db->limit(), $this->db->where_in() etc etc query builder function in conjunction with above query. Is there any way to achieve that?
The reverse of what I am asking is $this->db->get_compiled_select() which gives us the SQL as string. I need to be able to input a string then let the query builder take over.
e.g.
$this->db->sql_string($above_sql);
$this->db->where_in('id', $id_array);
$this->db->limit(5, 50);
$query = $this->db->get();
Hope that makes sense.
