Question 16224 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to convert raw SQL query to Laravel Query Builder

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');
    }
Tags:

4

Answers


  1. Chosen as BEST ANSWER
    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();

  2. 0

    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();
    Login or Signup to reply.
  3. 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.

    Login or Signup to reply.
  4. 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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search