Add subtraction of database fields to each other in my sql codeigniter

Question

here is my request

function getInventoryAvailableQty($product_id,$warehouse_id,$location_id) {

   $this->db->select_sum('quantity','addqty');
   $this->db->select_sum('total_cost','addcost');
   if($warehouse_id)
   $this->db->where('warehouse_id',$warehouse_id);
   if($location_id)
   $this->db->where('location_id',$location_id);
   if($product_id)
   $this->db->where('product_id',$product_id);
   $this->db->where('inventory_type','add');
   $query = $this->db->get($this->tablename);
   $resultAdd = $query->row();

   $this->db->select_sum('quantity','removeqty');
   $this->db->select_sum('total_cost','removecost');
   if($warehouse_id)
   $this->db->where('warehouse_id',$warehouse_id);
   if($location_id)
   $this->db->where('location_id',$location_id);
   if($product_id)
   $this->db->where('product_id',$product_id);
   $this->db->where('inventory_type','remove');
   $query1 = $this->db->get($this->tablename);
   $resultRemove = $query1->row();

   $data['imeicost'] = $resultAdd->addcost-$resultRemove->removecost;
   $data['availableQty'] = $resultAdd->addqty-$resultRemove->removeqty;
   return $data;

}

I want to do this in one request. because I want to add pagination, how to do it, please suggest

Answer 1

Not sure if you can do this using a CI Active record, but you can write SQL Query and run it with $this->db->query($query)

Your query might look something like this:

SELECT
    *
FROM
    (
        SELECT
            SUM(quantity) AS addqty_add,
            SUM(total_cost) AS addcost_add
        FROM
            tbName
        WHERE
            warehouse_id = 2
        AND inventory_type = 'add'
    ) AS tb1,
    (
        SELECT
            SUM(quantity) AS addqty_remove,
            SUM(total_cost) AS addcost_remove
        FROM
            tbName
        WHERE
            warehouse_id = 2
        AND inventory_type = 'remove'
    ) AS tb2