我要将以下sql查询传递给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` 将由接收 `Auth::id()` 方法,到目前为止,我有一个子查询工作得很好:
$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();
但是我在语法方面遇到了问题,我对laravel还很陌生,所以这可能不是一个好的查询方法。
非常感谢你的帮助。
1条答案
按热度按时间ebdffaop1#
我认为您可以尝试在这样一个查询中使用查询生成器:
您需要db::raw作为count(g.grade)