Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

jhyaps's avatar

Please Help me to Convert raw SQL query to Laravel Query Builder

Hi , Laracast Community,

My friend prepares this SQL query which run perfectly, but now I need this to convert to Laravel query builder but unfortunelyy I did not have a good knowledge of it. so, I need some help for generating a Laravel query from the given raw SQL query.

SELECT id, `leave_name`, `total_leave_days`, leave_id, leave_taken_days FROM `leaves` AS t1 INNER JOIN ( SELECT leave_id, SUM(`leave_taken_days`) AS leave_taken_days FROM `leave_applications` WHERE user_id = 2 AND statuses_id = 2 GROUP BY leave_id ) AS t2 ON t1.id = t2.leave_id;

I have also tried to do by myself but I cannot get the exact result.

$user_leaves = DB::table('leaves')
        ->select('id', 'leave_name', 'total_leave_days', 'leave_id', 'leave_taken_days')
        ->join('leave_application', 'leave_application.leave_id', '=', 'leave.id')
        ->select('leave_application.leave_id', DB::raw("SUM(leave_taken_days) as leave_application.leave_taken_days"))
        ->where('user_id','=', 2)
        ->where('statuses_id','=', 2)
        ->get();

Please anyone help me to solve this issue. Thanks in advance

0 likes
13 replies
tisuchi's avatar

@jhyaps What if you try this?

$user_leaves = DB::table('leaves')
        ->select('leaves.id', 'leaves.leave_name', 'leaves.total_leave_days', 'leave_applications.leave_id', DB::raw('SUM(leave_applications.leave_taken_days) as leave_taken_days'))
        ->join(DB::raw('(SELECT leave_id, SUM(`leave_taken_days`) AS leave_taken_days FROM `leave_applications` WHERE user_id = 2 AND statuses_id = 2 GROUP BY leave_id) as leave_applications'), 'leaves.id', '=', 'leave_applications.leave_id')
        ->get();
1 like
tisuchi's avatar

@jhyaps This could be the eloquent approach.

$user_leaves = Leave::select('id', 'leave_name', 'total_leave_days', 'leave_id', 'leave_taken_days')
        ->selectRaw('SUM(leave_applications.leave_taken_days) as leave_taken_days')
        ->join('leave_applications', 'leaves.id', '=', 'leave_applications.leave_id')
        ->where('leave_applications.user_id', 2)
        ->where('leave_applications.statuses_id', 2)
        ->groupBy('leave_applications.leave_id')
        ->get();
3 likes
jhyaps's avatar

@tisuchi Hello , I tried with this code and it shows an error like

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous (SQL: select `id`, `leave_name`, `total_leave_days`, `leave_id`, `leave_taken_days`, SUM(leave_applications.leave_taken_days) as leave_taken_days from `leaves` inner join `leave_applications` on `leaves`.`id` = `leave_applications`.`leave_id` where `leave_applications`.`user_id` = 2 and `leave_applications`.`statuses_id` = 2 and `leaves`.`deleted_at` is null group by `leave_applications`.`leave_id`)
1 like
Tray2's avatar

@jhyaps you need to use and alias on the is columns and specify which tables they come from

1 like
jhyaps's avatar

@Tray2 Hi , The Query my friend give me is a bit confusing, though it has a given a output as desirable . In that SQL query, aliases are already there like t1 and t2, but in the Laravel query builder I is complex for the one has a little knowledge of it.

1 like
jhyaps's avatar

@tisuchi and for this I am also getting an different error like

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select `leaves`.`id`, `leaves`.`leave_name`, `leaves`.`total_leave_days`, `leave_applications`.`leave_id`, SUM(leave_applications.leave_taken_days) as leave_taken_days from `leaves` inner join (SELECT leave_id, SUM(`leave_taken_days`) AS leave_taken_days FROM `leave_applications` WHERE user_id = 2 AND statuses_id = 2 GROUP BY leave_id) as leave_applications on `leaves`.`id` = `leave_applications`.`leave_id`)
tisuchi's avatar

@jhyaps Can you try this and check what you are getting?

$user_leaves = Leave::select('leaves.id', 'leave_name', 'total_leave_days', 'leave_id', 'leave_taken_days')
    ->selectRaw('SUM(leave_applications.leave_taken_days) as leave_taken_days')
    ->join('leave_applications', 'leaves.id', '=', 'leave_applications.leave_id')
    ->where('leave_applications.user_id', 2)
    ->where('leave_applications.statuses_id', 2)
    ->groupBy('leave_applications.leave_id')
    ->get();
jhyaps's avatar

@tisuchi Hi, thanks for the reply, when I tried your code , I am getting a error on GROUP BY like,

SQLSTATE[42000]: Syntax error or access violation: 1055 'attendance.leaves.id' isn't in GROUP BY (SQL: select `leaves`.`id`, `leave_name`, `total_leave_days`, `leave_id`, `leave_taken_days`, SUM(leave_applications.leave_taken_days) as leave_taken_days from `leaves` inner join `leave_applications` on `leaves`.`id` = `leave_applications`.`leave_id` where `leave_applications`.`user_id` = 2 and `leave_applications`.`statuses_id` = 2 and `leaves`.`deleted_at` is null group by `leave_applications`.`leave_id`)
Sinnbeck's avatar

@jhyaps the groupBy needs to be inside the join query. I will give it a shot when I'm at a computer. Sadly I don't think join() can take a query builder

Any reason you convert it? This is the kind of query I would use as it is. Much easier to read then

1 like
numaan's avatar
numaan
3 years ago
Best Answer
Level 2

I didn't test this code but can you try this and let me know the result.

$leaves = DB::table('leaves as t1')
            ->select('t1.id', 't1.leave_name', 't1.total_leave_days', 't2.leave_id', 't2.leave_taken_days')
            ->join(DB::raw('(SELECT leave_id, SUM(leave_taken_days) AS leave_taken_days FROM leave_applications WHERE user_id = 2 AND statuses_id = 2 GROUP BY leave_id) AS t2'), function($join)
            {
                $join->on('t1.id', '=', 't2.leave_id');
            })
            ->get();
1 like

Please or to participate in this conversation.