How to Write sub Query in laravel-CodePudding

Here is my Query

SELECT
    SUM(A.total_price) AS total_sum
FROM
(
    SELECT
    *, (qty * cost) AS total_price
    FROM
    services_p_o_items_management
    WHERE
    services_pos_id = '.$id.'
) AS A

How to write in laravel anyone here to help me

CodePudding user response：

If you want to run the query in a loop:

DB::table('services_p_o_items_management')
    ->selectRaw("SUM(qty * cost) as total_sum")
    ->where('services_pos_id', $id)
    ->first();

If you want to execute a single query for all the ids:

DB::table('services_p_o_items_management')
    ->selectRaw("services_pos_id, SUM(qty * cost) as total_sum")
    ->whereIn('services_pos_id', $ids)
    ->groupBy("services_pos_id")
    ->get();

CodePudding user response：

You can use raw query in from() method

DB::query()->from('SELECT *, (qty * cost) AS total_price FROM  services_p_o_items_management WHERE services_pos_id = '.$id, 'A')
->get();

Beside that, first argument of from() also accept a Builder object

/**
* Set the table which the query is targeting.
*
* @param  \Closure|\Illuminate\Database\Query\Builder|string  $table
* @param  string|null  $as
* @return $this
*/
public function from($table, $as = null)

Thus, you can change your raw query into a Builder object

$query = DB::table('services_p_o_items_management')
   ->select(['*', DB::raw('(qty * cost) AS total_price')])
   ->where('services_pos_id', $id);
DB::query()->from($query, 'A')->get();