CODEWITHSUNDEEP
mysqlsqllaravellaravel-5
Germán Ruelas
Germán Ruelas

Reputation: 125

Use a subquery in laravel eloquent with group by

I want to pass the following sql query to laravel:

SELECT total.candidate_name, total.name, total.questions, COUNT(total.grade)
FROM 
(
    SELECT c.name as candidate_name, j.name, t.questions, g.grade 
    FROM candidates as c 
        INNER JOIN job_openings as j on c.fk_id_job_opening=j.id_job_opening 
        INNER JOIN interview_templates as t on  j.fk_id_template=t.id_template 
        INNER JOIN responses as r on r.fk_id_candidate=c.id_candidate 
        INNER JOIN grades as g on r.id_response=g.fk_id_response 
    WHERE g.fk_id_user='some_id'
) as total
GROUP BY total.candidate_name, total.name, total.questions;

some_id will be received by Auth::id() method, until now I have the subquery that works well:

$subquery = Candidate::select('candidates.name AS c_name', 'j.name AS j_name', 't.questions AS questions', 'g.grade AS grade')
    ->join('job_openings AS j', 'candidates.fk_id_job_opening', '=', 'j.id_job_opening')
    ->join('interview_templates AS t', 'j.fk_id_template', '=', 't.id_template')
    ->join('responses AS r', 'r.fk_id_candidate', '=', 'candidates.id_candidate')
    ->join('grades AS g', 'r.id_response', '=', 'g.fk_id_response')
    ->where('g.fk_id_user',Auth::id())
    ->get();

but I'm having troubles in the syntax to accomplish my purpose, I'm really new in laravel so maybe this is not a good way to do the query.

Thanks a lot for your help.

Upvotes: 1

Views: 3032

Answers (1)

Radu
Radu

Reputation: 1031

I think you can try to use query builder in just one query like that:

 return DB::table('candidates AS c')
    ->select( DB::raw('c.name AS c_name,j.name AS j_name,t.questions AS questions, count(g.grade) as countGrade'))
    ->join('job_openings AS j', 'c.fk_id_job_opening', '=','j.id_job_opening')
    ->join('interview_templates AS t', 'j.fk_id_template', '=', 't.id_template')
    ->join('responses AS r', 'r.fk_id_candidate', '=', 'c.id_candidate')
    ->join('grades AS g', 'r.id_response', '=', 'g.fk_id_response')
    ->where('g.fk_id_user',Auth::id())
    ->groupBy('c_name', 'j_name', 'questions')
    ->get();

You need DB::raw for count (g.grade)

Upvotes: 2

Related Questions