I have this MYSQL Query which has self joins:
SELECT
e.id as id,
e.employee_code as staff_id,
e.hr_status,
CONCAT(e.first_name, ' ', e.last_name) AS 'direct_report',
e.user_id,
e.company_id,
e.email as official_email,
CASE
WHEN e.gender_code = 0 THEN "Female"
ELSE "Male"
END AS gender,
em.employee_code as manager_id,
CONCAT(em.first_name, ' ', em.last_name) AS line_manager,
d.id AS department_id,
d.dept_code,
d.dept_name,
CONCAT(eh.first_name, ' ', eh.last_name) AS hod_name,
CONCAT(eb.first_name, " ", eb.last_name) AS hrbp_name
FROM
hr_employees e
INNER JOIN hr_employees em ON
em.employee_code = e.line_manager_id
INNER JOIN hr_departments d ON
e.department_id = d.id
INNER JOIN hr_employees eh ON
eh.employee_code = d.dept_head
INNER JOIN hr_employees eb ON
eb.employee_code = d.hr_business_partner_id
WHERE
e.hr_status = 0 AND
em.company_id = e.company_id AND
d.company_id = e.company_id AND
e.hr_status = 0
ORDER BY
d.dept_name,
e.employee_code
How do I convert it to Laravel Eloquent Query?
Thanks