Laravel query Builder Select from subquery

Question

I'm trying to convert a sql query to a Laravel Query builder, but i find hard understand which is the best way for FORM SUBQUERY in Laravel, because if i'm not wrong, is not something that you can normally do whit query builder, but you should implement in raw.

is this right?

and how should translate this?

 SELECT t1.* FROM (//with out sub query it would be just products AS t1
                   SELECT SUM(amount)AS amount, products.* 
                   FROM products ,orders 
                   WHERE orders.product_id = products.product_id 
                   GROUP BY orders.product_id )AS t1
 LEFT OUTER JOIN products as t2 
 ON(t1.name = t2.name AND t1.year_production< t2.year_production) 
 WHERE t2.name is null 

I could translate just with out adding the sub query

 $poductlist =Product::from('products AS t1')->select('t1.*')
                     ->leftjoin('products AS t2', function($join){
                               $join->on('t1.name','=', 't2.name')
                                    ->on('t1.year_production','<','t2.year_production')
                                    ->whereNull('t2.name')
                                    ->orderBy('t2.name','desc');})
                     ->get();

Answer 1

I believe you can rewrite your query as correlated sub query to filter products with max value of year_production where names are duplicate

select p.product_id,
       p.name,
       coalesce(sum(o.amount),0) as amount
from products as p
left join orders o on p.product_id = o.product_id
where exists (
    select 1
    from products p1
    where p1.name = p.name
    having max(p1.year_production) = p.year_production
)
group by p.product_id, p.name
order by p.product_id

and in query builder you can transform it as

$products = DB::table('products as p')
               ->select(['p.product_id',
                         'p.name',
                          DB::raw('coalesce(sum(o.amount),0) as amount')
                        ])
               ->leftJoin('orders as o', 'p.product_id', '=', 'o.product_id' )
               ->whereExists(function ($query) {
                   $query->select(DB::raw(1))
                         ->from('products as p1')
                         ->whereRaw(DB::raw('p1.name = p.name'))
                         ->havingRaw('max(p1.year_production) = p.year_production')
                         ;
              })
              ->groupBy('p.product_id','p.name')
              ->orderBy('p.product_id')
              ->get();