mysql - 将 mysql 子查询转换为 Laravel

Question

如何将 mysql 查询转换为 laravel 查询？这是查询

SELECT
  users.first_name,
  users.id AS uid,
  (
  SELECT
    COUNT(vpl_submissions.accept)
  FROM
    vpl_submissions
  INNER JOIN vpl ON vpl_submissions.vpl = vpl.id
  WHERE vpl.courseid = 2 
    AND vpl_submissions.accept = 1 
    AND vpl_submissions.userid = uid
) AS completed
FROM
  users
INNER JOIN course_enroles ON users.id = course_enroles.user_id
WHERE course_enroles.course_id = 2

Answer 1

你可以试试这个。

    DB::table('users')
        ->join('course_enroles', 'users.id', '=', 'course_enroles.user_id')
        ->where("course_enroles.course_id", "=", 2)
        ->select(users.first_name,users.id AS uid,
            DB::raw("(SELECT COUNT(vpl_submissions.accept) FROM 
                    vpl_submissions INNER JOIN vpl ON vpl_submissions.vpl = vpl.id 
                     WHERE vpl.courseid = 2 
                           AND vpl_submissions.accept = 1 
                           AND vpl_submissions.userid = uid) as completed")
           )
        ->get();

这会对你有帮助。