phpcodeigniterjoinsql-updatequery-builder

Codeigniter 2 $this->db->join used with $this->db->update

I just realized that you cannot use:

$this->db->join()

with

$this->db->update()

It seems the "join" is executed by codeigniter alright, but is not used in the query, seen after an update on the base table obtained with: $this->db->last_query();

I saw that there was no join. Then I tried an update on the joined table thinking the join would only been used if needed, but I did not work and told me the error 1054 "Unknown column XXX in where clause".

Is there a way to force codeigniter? The way I built my software, I REALLY don't want to build all the different parts of the queries myself (join, where) and the call $this->db->query().

NOTE: I saw these links:

Codeigniter active record update statement with a join

Is it possible to UPDATE a JOINed table using Codeigniter's Active Record?

codeigniter - database : how to update multiple tables with a single update query

but if someone knows a cleaner way it would be nice, because these solutions are not working with my case, since I was using the same joins in a "preProcessing()" method that uses the joins for a preview of the changes, then the same "preProcessing()" method is used for the replacement

Solution

  • Ok well I managed to find a "clean" solution, using codeigniter's join, set, etc. So what's cool is that you will have all CI's benefits of using $this->db->join(), $this->db->join(), etc. like escaping and adding quotes.

    So first do all your CI stuff:

    $this->db->join(..) // Set all your JOINs
$this->db->set(..) // Set your SET data
$this->db->where(..) // Set all your WHEREs

    Then you can build the query using Active Record's ready, cleaned and escaped query elements:

    // JOIN
$sql = "UPDATE $this->baseTable ";
$sql .= implode(' ', $this->db->ar_join);

// SET
$sql .= ' SET';
$setArray = array();
foreach ($this->db->ar_set as $column=>$newValue)
    array_push($setArray, " $column = $newValue");
$sql .= implode(',', $setArray);

// WHERE
$sql .= ' WHERE '.implode(' ', $this->db->ar_where);

$this->db->query($sql);

    If someone has a better solution, I will gladly accept it and use it instead