-
venance
Junior Member
-
Posts: 29
Threads: 9
Joined: Feb 2021
Reputation:
0
02-22-2021, 12:18 AM
(This post was last modified: 02-22-2021, 12:23 AM by venance.)
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 ), )
-
venance
Junior Member
-
Posts: 29
Threads: 9
Joined: Feb 2021
Reputation:
0
02-22-2021, 04:25 AM
(This post was last modified: 02-22-2021, 04:33 AM by venance.)
(02-22-2021, 04:04 AM)iRedds Wrote: (02-22-2021, 03:51 AM)venance Wrote: 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')
(02-22-2021, 03:51 AM)venance Wrote: 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?
|