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();
Advertisement
Answer
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 (...)
usewhereIn('column', $array)
- For subquery tables, use
Closures
orBuilder
classes (DB::table(fn($q) => ... , alias)
orDB::table($builder, alias)
) - For
UNION ALL
useunionAll()
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.