Codeigniter active record nested select issue

Question

I am using codeigniter for my php project, but am not much experience in it. So, please help me.

I have two database. 1. Kalix2 2. Asterisk

In both the database 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 Active record.

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 active record format.

dasper dasper · Accepted Answer · 2013-01-22T22:26:03

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.

Connecting to Multiple Databases

Active Record Class and Joins

Debug the query using Profiler

Codeigniter active record nested select issue

1 Answers