how to convert this SQL query to eloquent in Laravel

Question

I am trying to convert this SQL query to Eloquent in Laravel

Convert SQL code to Eloquent

SELECT
  session_id,
  SUM(points) AS total_points 
FROM
  (
    SELECT
      session_id,
      spent_points AS points 
    FROM
      session_details 
    WHERE
      session_id IN 
      (
        " - Meagevy6y9ukbmFXvB7",
        " - Meak6dG9iqvHWfAGQvy"
      )
    UNION ALL
    SELECT
      session_id,
      price_points 
    FROM
      template_sales 
    WHERE
      session_id IN 
      (
        " - Meagevy6y9ukbmFXvB7",
        " - Meak6dG9iqvHWfAGQvy"
      )
  )
  t 
GROUP BY
  session_id

my code in Laravel but not working

$ids = ["-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy"];
$query = DB::table('session_details')
    ->select('session_id',DB::raw('SUM(points) AS total_points FROM ( SELECT session_id, spent_points AS points FROM session_details
    WHERE session_id IN ("'.$ids.'") UNION ALL SELECT session_id,price_points FROM template_sales WHERE session_id IN ("'.$ids.'") ) t GROUP BY session_id'))
->get();

Answer 1

There is documentation available for all the operations in your query.

• For selected columns use select('column1', 'column2', ...)
• For selected aggregate columns use selectRaw('sum(column) as column')
• For WHERE column IN (...) use whereIn('column', $array)
• For subquery tables, use Closures or Builder classes (DB::table(fn($q) => ... , alias) or DB::table($builder, alias))
• For UNION ALL use unionAll() with the same syntax as subquery tables.

Option 1: Closures

$ids = ["-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy"];
$query = DB::table(function ($sub) use ($ids) {
        $sub->select('session_id', 'spent_points as points')
            ->from('session_details')
            ->whereIn('session_id', [1,2])
            ->unionAll(function ($union) use ($ids) {
                $union->select('session_id', 'price_points')
                      ->from('template_sales')
                      ->whereIn('session_id', $ids);
            });
    }), 't')
    ->select('session_id')
    ->selectRaw('sum(points) as total_points')
    ->groupBy('session_id')
    ->get();

Option 2: Builder (or translating the subqueries from the inside-out)

$ids = ["-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy"];

$union = DB::table('template_sales')
    ->select('session_id', 'price_points')
    ->whereIn('session_id', $ids);

$sub = DB::table('session_details')
    ->select('session_id', 'spent_points as points')
    ->whereIn('session_id', $ids)
    ->unionAll($union);

$query = DB::table($sub, 't')
    ->select('session_id')
    ->selectRaw('sum(points) as total_points')
    ->groupBy('session_id')
    ->get();

Pick whichever you prefer. Both evaluate to the same query you posted.