Laravelのクエリビルダーでレコードが存在しない会員を取得したい

Question

前提・実現したいこと

お世話になっております。
タイトルの通りですがLaravelのクエリビルダーでレコードが存在しない会員を取得したいのですが、クエリの発行がうまくいかず期待通りの結果が取得できず２日程悩んでも解決しなかったのでアドバイスいただければ幸いですm(__)m。よろしくお願い致します。

【取得したい結果】
lecture_group_attendances.attendanceが２０でかつlecture_user_statuses.statusが終了またはレコードが存在しない会員

※イレギュラーな対応でlecture_group_attendancesにはレコードがあるがlecture_user_statusesには同会員のレコードがない状態
※テーブル構造に関しては仕様上変更できない状態

使用するテーブル

【users】

user_id	name	status
1	ユーザー１	登録済
2	ユーザー2	登録済
3	ユーザー3	登録済

【lectures】

lecture_id	name	lecture_group_id
1	地理学科	1
2	文学科	1
3	心理学科	1
4	教育学科	2

【lecture_groups】

lecture_group_id	name
1	文学部
2	教育学部

【lecture_group_attendances】

lecture_group_id	user_id	attendance
1	1	20
2	2	6
1	3	20

【lecture_user_statuses】

lecture_user_status_id	user_id	lecture_id	status
1	1	1	終了
2	2	2	継続
### 実行したクエリ
以下クエリでは終了の会員は取得できるがlecture_user_statusesにレコードが存在しない会員を取得できない。
サブクエリやnot exitsを使ったりもしましたがうまくいきませんでした。
```
$lecture_ids　= [1,2]
DB::table($this->getTable())

        ->select([
            'users.user_id',
        ])
        ->leftJoin('lectures', 'lecture_group_attendances.lecture_group_id', '=', 'lectures.lecture_group_id')
        ->join('lecture_user_statuses', function ($join) {
            $join->on('lectures.lecture_id', '=', 'lecture_user_statuses.lecture_id')
                ->on('lecture_group_attendances.user_id', '=', 'lecture_user_statuses.user_id');
        })
        ->join('users', 'lecture_user_statuses.user_id', '=', 'users.user_id')
        ->where('lecture_group_attendances.attendance', 20)
        ->whereIn('lecture_user_statuses.lecture_id', $lecture_ids)
        ->where(function ($query) {
            $query->where('lecture_user_statuses.status', '終了')
                ->orWhereNull('lecture_user_statuses.status');
        })
        ->where('users.status', '!=', '退会')
        ->pluck('user_id');

$lecture_ids = [1,２];

    DB::table($this->getTable())
        ->select([
            'users.user_id',
        ])
        ->leftJoin('lectures', 'lecture_group_attendances.lecture_group_id', '=', 'lectures.lecture_group_id')
        ->join('lecture_user_statuses', function ($join) {
            $join->on('lectures.lecture_id', '=', 'lecture_user_statuses.lecture_id')
                ->on('lecture_group_attendances.user_id', '=', 'lecture_user_statuses.user_id');
        })
        ->join('users', 'lecture_user_statuses.user_id', '=', 'users.user_id')
        ->where('lecture_group_attendances.attendance', 20)
        ->whereIn('lecture_user_statuses.lecture_id', $lecture_ids)
        ->where(function ($query) use ($lecture_ids) {
            $query->where('lecture_user_statuses.status', '終了')
                ->orWhereRaw("not exits (
                select  `users`.`user_id`
                from `lecture_group_attendances` 
                left join `lectures` on `lecture_group_attendances`.`lecture_group_id` = `lectures`.`lecture_group_id`
                inner join `lecture_user_statuses` on `lectures`.`lecture_id` = `lecture_user_statuses`.`lecture_id` 
                and `lecture_group_attendances`.`user_id` = `lecture_user_statuses`.`user_id`
                inner join `users` on `lecture_user_statuses`.`user_id` = `users`.`user_id` 
                where `lecture_group_attendances`.`attendance` = 20 
                and `lecture_user_statuses`.`lecture_id` in {$lecture_ids} 
                and  `lecture_group_attendances`.user_id = `lecture_user_statuses`.user_id)");
        })
        ->where('users.status', '!=', '退会')
        ->pluck('user_id');

### 補足情報（FW/ツールのバージョンなど）
- Laravel5.5
- php7.2

Answer 1

lecture_user_statusesテーブルについても、left joinすれば良いのではないでしょうか。

Comments

[yoto009]

ご回答ありがとうございます。
leftjoinは１度試したのですが結果は取得できませんでした

[MYas30]

結合元になっているテーブルはlecture_group_attendancesですかね。

->leftJoin('lecture_user_statuses', 'lecture_group_attendances.user_id', '=', 'lecture_user_statuses.user_id')

とかでいけそうな気がするんですが、いかがでしょうか。

[yoto009]

結合元のテーブルを変更してleftjoinして、join('users', 'lecture_user_statuses.user_id', '=', 'users.user_id')をlecture_group_attendancesに変更したところ取得できるようになりました！
ありがとうございます！助かりました！

[MYas30]

お役に立ててよかったです！