Issue table aliases and table prefix [solution?]

[oobi]

if you use a table prefix (dbprefix) in the CodeIgniter database config (e.g. 'ff_'), bad things happen to table aliases in joins.

$this->datatables->select('C.contact_id');
$this->datatables->from('contact as C');
$this->datatables->join('contact_meta as M', 'C.contact_id = M.contact_id', 'LEFT');

The following SQL is generated:

SELECT ff_C.contact_id
LEFT JOIN `ff_contact_meta` as `M` ON `ff_C`.`contact_id` = `M`.`contact_id`

Note the "ff_C" prefix in the join statement. It has prepended the table prefix (ff_) to the table alias (C), which results in an invalid statement. BUT, the meta table is correctly aliased (M.contact_id rather than ff_M.contact_id).

The issue stems from the fact that DB methods are passed through to the CodeIgniter implementation, with the exception of from() which just stores its argument locally for later use in a get() statement. This means that the table alias for FROM is not tracked, as it is set last (after the rest of the query is assembled).

You can replicate a similar circumstance with the CI library directly by placing the call to FROM after the JOIN like so:

$this->datatables->select('C.contact_id');
$this->datatables->join('contact_meta as M', 'C.contact_id = M.contact_id', 'LEFT');
$this->datatables->from('contact as C');  // <-- FROM after JOIN - alias applied in JOIN before it is declared

The easiest way around seems to be to simply pass through the from method. I'm not sure what other implications this may have, but I have not yet observed any adverse affects from doing so:

In datatables.php:

public function from($table)
{
      $this->ci->db->from($table);   // <-- add this line
      $this->table = $table;
      return $this;
}

Update: I was inspecting the query output rather than the output from DT itself. Because DT is using get(table_name) the "fix" above is now resulting in a compound FROM statement

FROM ff_contact as C, ff_contact as C)

so not a quick fix then - bummer

[oobi]

Actually it turned out easier than I thought (I'm not fully up to speed with CI).

pass through FROM to underlying CI library

public function from($table)
{
      $this->ci->db->from($table);   // <-- add this line
      $this->table = $table;
      return $this;
}

Remove table reference in get_display_result()

private function get_display_result()
{
   return $this->ci->db->get(); // table is now set in from()
}

Do the same in get_total_results() and add a FROM call

private function get_total_results($filtering = FALSE)
{
     ...
     // set FROM early so table aliases are respected
    $this->ci->db->from($this->table);

    ... [joins, where, group, like etc goes here]...

    // set TABLE reference to NULL in get() 
    $query = $this->ci->db->get(NULL, NULL, NULL, FALSE);
    return $query->num_rows();
}

Works nicely now.