phpdatabasecodeigniterselectquery-builder

How to access two databases in a single SELECT query with CodeIgniter's query builder

I have two databases.

  1. Kalix2
  2. Asterisk

In both the databases, I am having some table. I want to make a join on the tables from two different database. I have the below query, it's working fine in back-end, but I don't know how to implement it using CodeIgniter's query builder.

SELECT a.CompanyName,
       SUM(CEIL((b.billsec)/(c.Call_Limit*60))) AS totalcall,
       HOUR(b.calldate) AS use_hour
FROM Kalix2.ph_Companies a 
INNER JOIN Asterisk.cdr b ON b.clid LIKE CONCAT('%', a.CompanyName, '%') 
INNER JOIN Kalix2.ph_Plans c ON c.Comp_ID=a.Comp_ID 
WHERE date(b.calldate)>='2013-01-15'
    AND date(b.calldate)<='2013-1-20'  
    AND c.Plan_Type='Per_Call'
    AND a.CompanyName='ABCD' 
GROUP BY hour(b.calldate);

Please help me to convert this query into query builder format.

Solution

  • You should be able to convert this to active record if you want but you will gain better performance by just converting this to Query Bindings. Common mistake is trying to run a query off of multiple classes:

    $this->db1
$this->db2

    This will not work because running $this->db1->get() because it is only aware of the data in db1 and not db2. Verbosely keep the database.table info in the joins, use only one db object, and use the profiler to debug the query generation and you should be good.