How to get data of joined table using query() method

Question

I have an issue with getting data of the joined table if I use query(). I did not get the data of product table, how can I solve this using query() without using Active record? Here is my db table structure

        category table
        +--------------------------+
        | cId | added  | category  |       
        +-----+--------+-----------+
        |  1  | 1.2.20 |  PC       |
        |  2  | 1.7.20 | electron  |
        +-----+--------+-----------+

        product table
        +--------------------------+
        | id  |  cId   |  cost     |       
        +-----+--------+-----------+
        |  1  |   1    |  3000     |
        |  1  |   2    |  9000     |
        +-----+--------+-----------+

My Model

    protected $table = 'category';

    public function showProduct()
    {
        $sql = "SELECT 
                     category.*, COALESCE(SUM(product.cost),0) as price 
                FROM category 
                JOIN product 
                ON product.cId = category.cId
                GROUP BY category.cId
            ";
        $this->db->query($sql);
        return $this;
    }

My Controller

    public function index()
    {
            $result = $this->model->showProduct();
            echo "<pre>";
            print_r($result->asObject()->paginate(1));  
            
            //pagination
            $pager = $this->model->showProduct()->pager->links();
    }

Result I get

    Array
    (
        [0] => stdClass Object
            (
                [cId] => 1
                [added] => 1.2.20
                [category] => PC
            )

        [1] => stdClass Object
            (
                [cId] => 2
                [added] => 1.7.20
                [category] => electron
            ),

    )

Answer 1

You are requested to run this code.

SELECT category.cId,category.added,category.category,product.id,COALESCE(SUM(product.cost),0) as price
FROM category,product
WHERE category.cId=product.cId
GROUP BY category.cId;

If you are using CodeIgniter-4 then you can easily write this using Query Builder Class.