[SOLVED] How to use CodeIgniter's query builder methods to make an UPDATE query with a JOIN

How to use CodeIgniter's query builder methods to make an UPDATE query with a JOIN

This is the query that I'm trying to achieve via CodeIgniter's query builder pattern:

UPDATE `Customer_donations` cd 
JOIN Invoices i ON i.cd_id = cd.cd_id 
SET cd.amount = '4', cd.amount_verified = '1' 
WHERE i.invoice_id =  '13';

This is my coding attempt:

$data = array('cd.amount' => $amount, 'cd.amount_verified' => '1');
$this->db->join('Invoices i', 'i.cd_id = cd.cd_id')
     ->where('i.invoice_id', $invoiceId);

// update the table with the new data
if ($this->db->update('Customer_donations cd', $data)) {
    return true;
}

And this is the query that's actually getting produced:

UPDATE `Customer_donations` cd 
SET `cd`.`amount` = '1', `cd`.`amount_verified` = '1' 
WHERE `i`.`invoice_id` =  '13'

Why is this active record statement not applying my join clause?

Solution

Version-specific Note: The query builder snippet below will render an invalid query in CodeIgniter version 3 released March 30, 2015 after narfbg's commit (bf94058) on Jun 10, 2012 - Fix issue #1452

A bit ugly but it achieved what you expected in your question.

$invoiceId = 13;
$amount = 4;
$data = array('cd.amount' => $amount, 'cd.amount_verified' => '1');

$this->db->where('i.invoice_id', $invoiceId);

$this->db->update(
    'Customer_donations cd join Invoices i on i.cd_id = cd.cd_id',
    $data
);