mysql从另一个select查询值中减去一个select查询

Question

I have two mysql table and I have build to select query using this two tables, both query output is a summation of quantity from inventory_transfer_details table. I need to subtract "total_to" from "total_from". please check below is mysql tables.

Inventory_transfer

inventory_transfer_details

below is my two queries.

First query:

select sum(b.transfer_quantity) as total_to 
from inventory_transfers as a 
join inventory_transfer_details as b on a.id = b.inventory_transfer_id 
where a.status="approved" and b.inventory_or_composite_id = '1' and a.to_warehouse_id = '2'

second query:

select sum(b.transfer_quantity) as total_from 
from inventory_transfers as a 
join inventory_transfer_details as b on a.id = b.inventory_transfer_id 
where a.status="approved" and b.inventory_or_composite_id = '1' and a.from_warehouse_id = '2'

I need to subtract from query transfer_to to transfer_from

can you have any guide to convert this final query to laravel query?

Answer 1

You can combine those queries and get the difference

SELECT
  sum(
    IF(a.to_warehouse_id = '2', b.transfer_quantity, 0)
  ) - sum(
    IF(a.from_warehouse_id = '2', b.transfer_quantity, 0)
  ) as total
FROM
  inventory_transfers AS a
  JOIN inventory_transfer_details AS b ON a.id = b.inventory_transfer_id
WHERE
  a.status = "approved"
  AND b.inventory_or_composite_id = '1'

In the laravel query builder, this query might look like

DB::table('inventory_transfers as a')
    ->select(DB::raw('sum(IF(a.to_warehouse_id = '2', b.transfer_quantity, 0)) - sum(IF(a.from_warehouse_id = '2', b.transfer_quantity, 0)) as total'))
    ->join('inventory_transfer_details as b', DB::raw('a.id'), '=', DB::raw('b.inventory_transfer_id'))
    ->where([
        ['a.status', '=', 'approved']
        ['b.inventory_or_composite_id', '=', 1]
    ])
    ->get()