CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordquery-builder
dhruv jadia
dhruv jadia

Reputation: 1680

Convert raw SQL containing UNION, subqueries, LEFT JOINs, GROUP BYs, and ORDER BYs into CodeIgniter active record script

Please see the following query.

I want to convert into CodeIgniter active records.

SQL QUERY:

Select MobNo,Caller,Sum(Tot_In_Call) as Tot_In_Call,Called,Sum(Tot_Out_Call) as Tot_Out_Call
From (

    SELECT CTE.Caller as MobNo,Incall.Caller,Incall.Tot_In_Call, Null as Called,0 as Tot_Out_Call
    FROM Call_Details CTE
    Left Join (
            Select Count(Called) as Tot_In_Call,Caller,Called
            from call_details 
            Group By Caller,Called
    ) InCall on (Cte.caller = InCall.Called) 
    Where InCall.Called in($call_array) 
    Group BY CTE.Caller,Incall.Caller,Incall.Tot_In_Call

    Union All

    SELECT CTE.Caller as MobNo,Null as Caller,Null as Tot_In_Call, OutCall.Called,OutCall.Tot_Out_Call
    FROM Call_Details CTE
    Left Join (
            Select Count(Called) as Tot_Out_Call,caller,Called
            from call_details 
            group by caller,called
    ) OutCall on (CTE.caller = OutCall.caller And cte.called = OutCall.called) 
    Where CTE.Caller in($call_array) 
    Group BY CTE.Caller,OutCall.Called,OutCall.Tot_Out_Call

) As Qry
WHERE Qry.MobNo in($call_array)
Group By Qry.MobNo,Qry.Caller,Qry.Called
order by Qry.MobNo,Qry.Caller,Qry.Called

How can I convert this without using $this->db->query()?

Tried to make using active record:-

$this->db->select("MobNo,Caller,Sum(Tot_In_Call) as Tot_In_Call,Called,Sum(Tot_Out_Call) as Tot_Out_Call");
$this->db->from("SELECT CTE.Caller as MobNo,Incall.Caller,Incall.Tot_In_Call, Null as Called,0 as Tot_Out_Call
    FROM Call_Details CTE
    Left Join (
            Select Count(Called) as Tot_In_Call,Caller,Called
            from call_details 
            Group By Caller,Called
    ) InCall on (Cte.caller = InCall.Called) 
    Where InCall.Called in($call_array) 
    Group BY CTE.Caller,Incall.Caller,Incall.Tot_In_Call

    Union All

    SELECT CTE.Caller as MobNo,Null as Caller,Null as Tot_In_Call, OutCall.Called,OutCall.Tot_Out_Call
    FROM Call_Details CTE
    Left Join (
            Select Count(Called) as Tot_Out_Call,caller,Called
            from call_details 
            group by caller,called
    ) OutCall on (CTE.caller = OutCall.caller And cte.called = OutCall.called) 
    Where CTE.Caller in($call_array) 
    Group BY CTE.Caller,OutCall.Called,OutCall.Tot_Out_Call");
$this->db->where_in("MobNo",$call_array);
$this->db->group_by("MobNo,Caller,Called");
$this->db->order_by("MobNo,Caller,Called","ASC");
$this->db->get();

Upvotes: 1

Views: 508

Answers (1)

Atural
Atural

Reputation: 5439

If you really want to do that in order to get CI to escape your Queries, you should probably split your queries.

Below i give you an example 

$strQuery = $this->db
    ->select("Count(Called) as Tot_In_Call,Caller,Called", false)
    ->from("call_details")
    ->group_by("Caller,Called")
    ->get_compiled_select();

$strQuery = "(".$strQuery.") InCall";

$strQuery = $this->db
    ->select("CTE.Caller as MobNo,Incall.Caller,Incall.Tot_In_Call, Null as Called,0 as Tot_Out_Call", false)
    ->from("Call_Details CTE")
    ->join($strQuery,"Cte.caller = InCall.Called","left",false)
    ->where_in("InCall.Called", $call_array)
    ->group_by("CTE.Caller,Incall.Caller,Incall.Tot_In_Call")
    ->get_compiled_select();

echo $strQuery;

The query above gives you back the first inner select query until union all

With this piece of code you should be able to build your query and get what you want

Upvotes: 1

Related Questions