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
            ),

    )

Does this answer your question? [SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql\_mode=only\_full\_group\_by](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc) — parttimeturtle, Feb 23 '21 at 01:04

score 0 · Answer 1 · edited Feb 28 '21 at 04:35

0

$sql="SELECT category*,product* FROM category INNER JOIN product.cid=category.id WHERE category.id";

edited Feb 28 '21 at 04:35

Toastrackenigma

7,604
4
45
55

answered Feb 27 '21 at 11:32

Ganpat Thakor

63
4

1

While this code may provide a solution to the question, it's better to add context as to why/how it works. This can help future users learn and eventually apply that knowledge to their own code. You are also likely to have positive feedback/upvotes from users, when the code is explained. – Syscall Feb 28 '21 at 12:51
This still work the same a my problem. But I found solution to this by defining $this->table again – ven Feb 28 '21 at 20:52

score 0 · Accepted Answer · answered Feb 28 '21 at 06:41

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.

score 0 · Answer 3 · answered Feb 28 '21 at 21:03

I found solution to this by setting this table property within a function.

Model

$protected $table = array("default bd table here");

public function showProduct()
{
    $this->table = array('category');
    
    $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();
        
        //pagination
        $pager = $this->model->showProduct()->pager->links();
}

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

3 Answers3