I am new to Laravel and trying to support an existing application that is in Laravel 5. I am trying to convert the following SQL to eloquent structure
SELECT s.id, CONCAT(u.first_name, ' ', u.last_name) AS user_name, u.avatar_location AS user_img, s.employee_photo, d.name AS department, seg.name AS segment, s.survey_title, s.before_action, s.before_picture, s.action, s.action_date, s.after_action, s.after_picture, s.nominated, s.awarded, s.created_at, s.updated_at, (SELECT COUNT(1) FROM likes l WHERE l.survey_id = s.id) AS likes, (SELECT COUNT(1) FROM likes l WHERE l.survey_id = s.id AND l.user_id = 5) AS UserLikes, (SELECT COUNT(1) FROM comments c WHERE c.survey_id = s.id ) AS comments FROM surveys s JOIN departments d ON s.department = d.id JOIN segments seg ON s.segment_id = seg.id JOIN users u ON s.user_id = u.id WHERE s.status = 'Approved' ORDER BY s.action_date DESC LIMIT 20 OFFSET 20
I know enough Laravel to know that my basic start would probably be
$surveys = DB::table('surveys') ->join('departments', 'surveys.department', '=', 'departments.id') ->join('segments', 'surveys.segment_id', '=', 'segments.id') ->join('users', 'surveys.user_id', '=', 'users.id') ->where('surveys.status', 'Approved') ->orderBy('surveys.action_date') ->skip(20)-take(20) ->select(...)->get();
However, I am not sure how to do the subqueries. Looking for any suggestions.