Laravel 5.4加入两张桌子

Question

I'm quite lost here. I am trying to get the list of people who are on the Records table which aren't on the Profiles tables which has the specific course (e.g Psychology) and year (e.g 2011). So far, this is what I came up with and unfortunately it's not working.

$check = Record::join('Profiles', function ($join) {
        $join->on('Records.firstname', '!=', 'Profiles.firstname');
        $join->on('Records.lastname', '!=', 'Profiles.lastname');
        $join->on('Records.middlename', '!=', 'Profiles.middlename');
    })
    ->select('Records.*', 'Profiles.*')
    ->where('Records.year', '2011')
    ->where('Records.course', 'Psychology')
    ->get(); 

    dump($check);

Is there any way that I can go around about this? I'm new to this. Thanks in advance. Advises and tips on joining tables would be greatly appreciated.

Answer 1

Please try a NOT EXISTS subquery:

use Illuminate\Database\Query\Builder;

$check = \DB::table('Records')
            ->whereNotExists(function(Builder $query) {
                $query->select(\DB::raw(1))
                    ->from('Profiles')
                    ->whereRaw('Records.firstname = Profiles.firstname')
                    ->whereRaw('Records.middlename = Profiles.middlename')
                    ->whereRaw('Records.lastname = Profiles.lastname');
            })
            ->select('firstname', 'middlename', 'lastname')
            ->where('year', 2011)
            ->where('course', 'Psychology');
            ->get();