• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to get data of joined table using query() method

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

Reply

#2
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;

Reply

#3
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:"
Reply

#4
(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;

Reply

#5
(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?
Reply

#6
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()
Reply

#7
(02-22-2021, 05:12 AM)iRedds Wrote: 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
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.