How to convert raw subquery into laravel query with query builder?-CodePudding

I have written a raw query which also contains subquery. I'm getting no idea that how to convert subquery in the laravel subquery with query builder. Please someone convert this query. It will be very appreciable.

Query:

SELECT inventory.EmployeeID,
        inventory.created_date AS OrderDate,
        SUM(inventory.calculation) AS TotalPrice
        FROM ( SELECT i.id AS ItemID,
        o.id AS OrderID,
        o.EmployeeID,
        o.created_date,
        (o.Quantity * i.price) AS calculation
        FROM `stationary_orders` AS o
        LEFT JOIN `stationary_items` AS i ON o.Stationary_ID = i.id
        WHERE o.Store IN $storess
        ORDER BY o.id DESC
        LIMIT $Limit,10 ) AS inventory
        GROUP BY inventory.EmployeeID

CodePudding user response：

It took me a while to understand what's going on.

As I understood from your raw query, this is called a nested query.

You can use DB facade to make this query like this:

  
  DB::select('inventory.EmployeeID, inventory.created_date AS OrderDate, SUM(inventory.calculation) AS TotalPrice')
    ->fromSub(function ($nestedQuery) use ($stores) {
      $nesterQuery->select('i.id ...')->from('stationary_orders as o')
        ->leftJoin('stationary_items', 'o.Stationary_ID', '=', 'stationary_items.id')
        ->whereIn('o.Store', $stores)
        ...
    }, 'inventory')
    ->groupBy('inventory.EmployeeID')->get()