Write Custom query in laravel 5

问题:

I want to get the some data and also last leave date of the employee with their employee name so i wrote this query how can i do this in laravel 5

SELECT employee.firstname,employee.lastname,employee.id,hr_leave.*,LAST(hr_leave.date) AS lastleaveday
FROM employee
INNER JOIN hr_leave
ON employee.id=hr_leave.hrid
WHERE hr_leave.oid=$id

employee
id  oid  firstname  lastname

id is primary key

hr_leave
id  oid  hrid  leave_from  leave_to  days  reason

id is primary key hrid is the foreign key

回答1:

$empls= DB::table('employee')
            ->join('hr_leave', 'hr_leave.hrid', '=', 'employee.id')
            ->select('employee.*')
            ->where('hr_leave.oid', $id)
            ->get();

more info - https://laravel.com/docs/5.1/queries

回答2:

For Select --> DB::select('your query here.....');
For Insert --> DB::insert('your query here.....');
For Update --> DB::update('your query here.....');
For Delete --> DB::delete('your query here.....');
For General -->  DB::statement( 'your query here....' );

Read this Article

回答3:

You can use below example.

$users = \DB::select(“SELECT users.,products. FROM users LEFT JOIN products ON users.id = products.user_id WHERE 1 ORDER BY users.id ASC”);