CODEWITHSUNDEEP
sqlcodeigniteractiverecord
Andre Garcia
Andre Garcia

Reputation: 914

Codeigniter active record query count

in my project i have 4 tables. One called Calls, Projects, Products and Users.

Calls has 2 FK (project_id and user_id) and Projects has one FK (product_id).

I'm trying to query the table Calls and I want to know the amount of calls for each product has been received from each operator.

I'm trying to query something like this:

[{"name":"User1","Project1":"120","Project2":"10,"Project3":"140...}
{"name":"User2","Project1":"80","Project2":"60,"Project3":"14...}]

Upvotes: 1

Views: 158

Answers (1)

Minhaz
Minhaz

Reputation: 446

I am guessing when you mean operator you mean user 

$this->db->select('count(P.product_id) as total_product ,CL.project_id, CL.user_id, P.project_name')
            ->from('Calls CL')
            ->join('Projects P', 'P.project_id=CL.project_id')
            ->group_by('CL.project_id, CL.user_id')
            ->get();

Now your can write a php script to loop through your result to make the json

    $index = 0;
    $user_data = array();
    $user_key = array();
    foreach ($result as $row) {
        if(!isset($user_key[$row['user_id']])){
            $user_key[$row['user_id']] = $index;
            $index++;
        }
        $user_data[$user_key[$row['user_id']]]['user_name'] = $row['user_name'];
        $user_data[$user_key[$row['user_id']]][$row['project_name']] = $row['total_product'];
    }

I did not tested the code but it should work

Upvotes: 1

Related Questions