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

noblemfd's avatar

How to Convert MYSQL Qury with self join to Laravel Eloquent Query

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

0 likes
5 replies
jlrdw's avatar

Like your other query you asked about, Just use the db facade.

If converted to eloquent you will probably wind up having to use some raw sql anyway.

But again, just use the query builder syntax if you want to convert. It will probably take a little trial and error to work it up.

Don't forget there is also getPdo().

noblemfd's avatar

@jlrdw - What I mean is how do I write Laravel Query for this MYSQL

obet's avatar

Use Model and set the Relation between Models https://laravel.com/docs/5.6/eloquent-relationships. Your Model represents a table in your database (use Singular form for Model name and Plural form for the table name. And eloquent will do the magic. And for the CONCAT part place it inside the model using ACCESSORS.

Please or to participate in this conversation.