How to convert raw SQL query to Laravel Query Builder

Question

I need a following code to convert to Laravel query can any one help me with these.

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 even tried but the output is not showing atall.

$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();

How can I solve this issue?

UPDATE

Relations between two models.

Leave Model

public function leave_application()
    {
        return $this->belongsTo(LeaveApplication::class, 'id' , 'leave_id');
    }

Leave Application Model

 public function leave()
    {
        return $this->belongsTo(Leave::class, 'leave_id', 'id');
    }

Hi thanks for the valuable reply, but I am not understanding at all, while looking at the subquery thing. — Jhyaps
– Jhyaps, Commented Jan 13, 2023 at 8:45
What do you get when you dd($user_leaves->toArray()); after the ->get() line. Be aware that using DB::table() will return \stdClass objects, if you actually want the model objects instead use Leave::query()->select().... — zanderwar
– zanderwar, Commented Jan 13, 2023 at 13:56
Hi @zanderwar I get a value what I needed when I run above SQL query like shown in this image i.ibb.co/3WVmGHH/Capture.jpg But the above query which I wrote shows a lot of error, though I am not quite good in writing a eloquent or Laravel query so I am facing a problem. If you have a solution please help me — Jhyaps
– Jhyaps, Commented Jan 13, 2023 at 15:56
Before the ->get() add ->dd() this will output the SQL and bindings that will be generated, update your question with it. — zanderwar
– zanderwar, Commented Jan 14, 2023 at 2:35

codenathan · Accepted Answer · 2023-01-15 19:50:26Z

1

Try this :

$user_leaves = Leave::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'))
                    ->with('leave_application')
                    ->whereHas('leave_application', function($q) {
                        $q->where('user_id', 2)
                          ->where('statuses_id', 2);
                    })
                    ->groupBy('leaves.id')
                    ->get();

edited Jan 15, 2023 at 19:50

answered Jan 13, 2023 at 13:54

codenathan

7816 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jhyaps Over a year ago

Hey Man, Thanks for the code, but I am not getting the same result which the SQL query is generating, As you see here in this image i.ibb.co/3WVmGHH/Capture.jpg ,

codenathan Over a year ago

Can you post the model code which shows the relationship between the leave and applications model in your question?

Jhyaps Over a year ago

Hi , there I have updated in a question about the relation between two models

codenathan Over a year ago

Hi, i have updated the code, have a check

Joel · Accepted Answer · 2023-02-02 15:55:49Z

0

On this topic I would like to give my recommendations for some tools to help you out in the future.

SQL Statement to Laravel Eloquent to convert SQL to Laravel query builder. This does a decent job at low level queries. It also saves time when converting old code.

The other tool I use to view the query that is being run is Clock Work I keep this open in a tab and monitor slow nasty queries or, also gives me perspective on how the query builder is writing SQL. If you have not use this extension I highly recommend getting and using it.

answered Feb 2, 2023 at 15:55

Joel

1991 gold badge2 silver badges8 bronze badges

Comments

Jhyaps · Accepted Answer · 2023-02-15 09:21:04Z

0

Actually I found my answer,

$user_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 = ' . $user_id . ' AND statuses_id = 2 GROUP BY leave_id) AS t2'), function ($join) {
                $join->on('t1.id', '=', 't2.leave_id');
            })
            ->get();

answered Feb 15, 2023 at 9:21

Jhyaps

14 bronze badges

Comments

RuchDi · Accepted Answer · 2023-02-16 03:54:43Z

0

You can use DB:select("your query", params) and put your query and params (as an array (optional)

As below sample:

$result =  DB:select("
  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" , $params
);
return response()->json($result);

answered Feb 16, 2023 at 3:54

RuchDi

6387 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to convert raw SQL query to Laravel Query Builder

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related