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

Hello everyone.
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

PHP Code:

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


PHP Code:

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


MY MODEL

PHP Code:

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

PHP Code:

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

PHP Code:

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


PHP Code:

public function showProduct(){
    $this->db->select('category.*, COALESCE(SUM(product.cost),0) as price', false)
         ->join('product', 'product.cId = category.cId')
         ->groupBy('category.cId');

    return $this;
} 


Thank you, But all I need here is to use query() method to get the same result. How can I do because it only see the first table with defined property protected $table = "category:"

Thank you, But all I need here is to use query() method to get the same result. How can I do because it only see the first table with defined property protected $table = "category:"

Use $this->builder() instead of $this->db or ->from('table name') 

I did not get the data of product table, how can I solve this using query() without using Active record?" 

PHP Code:

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";
    $queryResult = $this->db->query($sql); // <------ like this
    return $this;
} 


Thank you, But all I need here is to use query() method to get the same result. How can I do because it only see the first table with defined property protected $table = "category:"

Use $this->builder() instead of $this->db or ->from('table name') 

I did not get the data of product table, how can I solve this using query() without using Active record?" 

PHP Code:

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";
    $queryResult = $this->db->query($sql); // <------ like this
    return $this;
} 


 
I tried the solution you give me and check the data

PHP Code:

print_r( $this->model->getProduct()->asObject()->paginate(1) ); 


But still I get the same result, It didn't show the data of the second table when I use

PHP Code:

$queryResult = $this->db->query($sql); and return $this 


With $this seems to return data of the defined table. I don't know why it ignore data from second table. What will be the solution to this?

I already told you that query() and paginate() are not linked and cannot be linked in principle.
You have two options, either use QueryBuilder or implement the paginate method with query()

I already told you that query() and paginate() are not linked and cannot be linked in principle.
You have two options, either use QueryBuilder or implement the paginate method with query()

Thank you  very much iRedds