Use Query Builder to select from two subqueries

I'm at a loss on how to duplicate the following query using the query builder:

select ii.total_in, io.total_out, (ii.total_in - io.total_out) as QTY_ON_HAND, ii.warehouse_id, ii.part_id 
from (select sum(it.qty) as total_in, it.warehouse_id, it.part_id from INVENTORY_TRANS it join PART_SITE ps on it.PART_ID=ps.PART_ID where it.type='I' and it.PART_ID = '46277' and it.TRANSACTION_DATE <= '2019-01-01' and ps.SITE_ID='AF' group by it.WAREHOUSE_ID, it.PART_ID) as ii
join (select sum(it.qty) as total_out, it.warehouse_id, it.part_id from INVENTORY_TRANS it join PART_SITE ps on it.PART_ID=ps.PART_ID where it.type='O' and it.PART_ID = '46277' and it.TRANSACTION_DATE <= '2019-01-01' and ps.SITE_ID='AF' group by it.WAREHOUSE_ID, it.PART_ID) as io on ii.PART_ID = io.PART_ID and ii.WAREHOUSE_ID = io.WAREHOUSE_ID
where (ii.total_in - io.total_out) > 0
order by ii.WAREHOUSE_ID, ii.PART_ID

Any ideas?

jlrdw

5 years ago

Level 75

Similar to this

Here is what a basic group by looks like:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

The order of commands matter.

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Query Builder will be similar, and there are examples in that chapter.

https://laravel.com/docs/8.x/queries

Please or to participate in this conversation.