Joining two databases using active record in codeigniter

Question

Does any body have idea how to join two tables from two different database using codeignitor active record?

I have these two database instances:

      $this->DB2 = $this->load->database('asterisk', TRUE);
      $this->DB1 = $this->load->database('default', TRUE); 

I want to join two tables from these two different database. How can I accomplish this?

Please, if you have any docs links give me.

Answer 1

Have you tried prefixing each database? I don't have access to my local server to test this, but if you have permissions to both databases something similar to this should work:

$this->db->select('*');
$this->db->from('asterisk.blogs');
$this->db->join('default.comments', 'default.comments.id = asterisk.blogs.id');

Answer 2

If you'r not able to join on the two different databases, and don't want to use regular SQL statements - you could try doing this by using the ActiveRecord caching.

http://ellislab.com/codeigniter/user-guide/database/active_record.html#caching

Some "pseudo-code" that probably doesn't work - but might help get you started.

$this->db = $this->load->database('asterisk', TRUE);
$this->db->start_cache();
$this->db->select('*');
$this->db->stop_cache();
$this->db->get('table1');

$this->db = $this->load->database('default', TRUE);
$this->db->select('*');
$this->db->from('table2');
$this->db->join('table1', 'table1.id = table2.id');
$this->db->get();

Answer 3

In Your Model Call the another DB:

function __construct() {
    parent::__construct();
    $this->DB2= $this->load->database('DB2', TRUE);
}

then just use the name of database on the Join

function get_personas() {
        $this->db->join('DB2.administrador c', 'c.id_administrador = personas.id_administrador', 'left');
        $this->db->select('c.administrador_nombre AS admin', FALSE);
        $this->db->join('tipo_identificacion b', 'b.id_tipo_identificacion = personas.id_tipo_identificacion', 'left');
        $this->db->select('b.tipo_identificacion_nombre', FALSE);
        $this->db->select('personas.*', FALSE);
        $this->db->where('id_personas <>', 1);
        $query = $this->db->get('personas');
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return FALSE;
        }
    }