时间戳

首页 > 解决方案 > How to write eloquent left join with subquery?

php - How to write eloquent left join with subquery?

问题描述

I want to write a left join query with a subquery in eloquent. I have two tables, lessons and lesson_user. 

lesson
lesson_id | name | grade

lesson_user
lesson_id|user_id

If a particular student is participating a lesson, his id will be in lesson_user table.

I want to get all the lessons for a grade, and if an user participating a lesson, then with his id.

Result would be: for user_id 5 and grade 3

 grade   lesson   user_id
   3      java       5
   3      C++        5
   3      Python    NULL  //He is not participating

Here the user id will come as an paramter, I tried this but didn't work.

public function getAll($grade_id, $user_id)
{
    $lessons = Lesson::where('grade_id', $grade_id);

    if($user_id != null){
        $sub = LessonUser::where('user_id', $user_id);
        $lessons = $lessons->leftJoin(DB::raw("($sub->toSql()) as b"), 'b.lesson_id', '=', 'lesson.id');
     }

     $lessons = $lessons
                  ->select("lesson.id", "lesson.name", "user_id", "grade")
                    ->get();

     return $lessons;
}

PS: Each lesson has a grade.

Edit:

instead of sub-query and code in the if block, i used this code initially. But it gives invalid results. It returns lessons which are not belongs to grades.

$lessons = $lessons->leftJoin('lesson_user as b', 'b.lesson_id', '=', 'lesson.id');
$lessons = $lessons->where("b.user_id",  $user_id)->orWhereNull("b.user_id");

Thank you :)

标签： phplaraveleloquent

解决方案

我的建议是where('user_id', $user_id)在 join 子句中使用，这会使查询更具可读性：

public function getAll($grade_id, $user_id)
{
    return Lesson::query()
        ->leftJoin('lesson_user', function (JoinClause $join) use ($user_id) {
            $join->on('lesson_user.lesson_id', '=', 'lesson.id')
                ->when($user_id !== null, function ($query) use ($user_id) {
                    $query->where('lesson_user.user_id', $user_id);
                });
        })
        ->where('lesson.grade', $grade_id)
        ->select([
            'lesson.id',
            'lesson.name',
            'lesson.grade',
            'lesson_user.user_id',
        ]);
        ->get();
}

推荐阅读