Question 183777 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – CodeIgniter joined table With SUM

I have two tables

products   sale_items
--------   ----------
id         id
code       product_id 
name       quantity 
price      ...
...

count the number of sales per product, all in one query. I tried something like this, but it doesn’t work:

public function getProductsListing()
    {       
       
        $this->db->select("products.id, products.code, products.name");
        $this->db->from('products');

        $this->db->join('sale_items','sum(COALESCE(quantity, 0)) as qte', false)
        ->where("registers_id = '{$this->session->register_id}' and  sale_items.product_id = products.id", null, false);  
    
         
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            foreach (($query->result()) as $row) {
                $data[] = $row;
                var_dump($data);
            }
            return $data;
        }
        else
        {
            return false;
        }
    }
Tags:

3

Answers


  1. 0

    you can simply join table like this in codeigniter

    $this->db->select('products.id, products.code, products.name');
$this->db->from('products');
$this->db->join('sale_items', 'products.id= products.product_id ');
$this->db->where(array('registers_id '=>$this->session->register_id))
$query = $this->db->get();
    Login or Signup to reply.
  2. 0

    You can use group by clause on product id:

     $this->db->select('SUM(COALESCE(quantity, 0)) as toalQuantity,p.id, p.code, p.name')
 ->from('products as p')
 ->join('sale_items as s', 's.product_id = p.id', 'left')
 //->where('registers_id', $this->session->register_id) 
 ->group_by('p.id')
 ->get();
    Login or Signup to reply.
  3. 0

    I don’t use CI, but I think you need to pass false as the second parameter to select(), to stop CI attempting to escape the column names.

    $this->db->select('p.id, p.code, p.name, IFNULL(SUM(s.quantity), 0) AS totalQuantity', false)
    ->from('products as p')
    ->join('sale_items as s', 'p.id = s.product_id', 'left')
    ->where('registers_id', $this->session->register_id) 
    ->group_by('p.id');

$result = $this->db->get();

    Notice I switched SUM(COALESCE(quantity, 0)) to IFNULL(SUM(s.quantity), 0) so the IFNULL() runs on the aggregate instead of every row.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search