[SOLVED] CodeIgniter update() with LEFT JOIN and multiple WHERE conditions

CodeIgniter update() with LEFT JOIN and multiple WHERE conditions

I have the following SQL:

UPDATE msh_leads ml
LEFT JOIN msh_leads_disposition_log dl
    ON ml.id = dl.lead_id
SET ml.assigned_to = null
WHERE ((dl.disposition_id != 6 AND dl.disposition_id != 3) OR (dl.disposition_id IS NULL))
    AND (ml.assigned_to = ? AND ml.decline = 0 AND ml.subcategory_id = ?)

There is a bit of logic in creating this (some of the where's come and go depending on certain situations) so I was hoping to recreate this in CodeIgniter (2.2) Active Record. I'm not sure how to add a join to an update or how to add multiple complex where statements to an update.

Solution

This'll help you. Try this.

Note: you need to place your values over ? else it'll throw an error 1064

$this->db->set('ml.assigned_to', 'null');
$this->db->where('ml.assigned_to = ?');
$this->db->where('((dl.disposition_id != 6 AND dl.disposition_id != 3) OR (dl.disposition_id IS NULL))');
$this->db->where('ml.decline = 0');
$this->db->where('ml.subcategory_id = ?');
$this->db->update('msh_leads ml join msh_leads_disposition_log dl on ml.id = dl.lead_id');