How to check if related table doesn't have record

Question

matches table has releationship with scores table.

I need to get all matches without record in scores table.

tryied smth, but didint worked:

$upcomingMatch = Match::join('scores', 'matches.id', '=', 'scores.match_id')
           ->where('away_team_id', '=', '1')
           ->orWhere('home_team_id', '=', '1')
           ->where('scores.match_id', null)
           ->latest('matches.match_date')
           ->first();

Answer 1

If you use relationship, you can use doesntHave() , but if you use query builder as you write in your question, you may use these codes, I saw that your where condition need some adjustment to use nested condition. If you want to retrieve null record, then use whereNull in scores table which is not a relationship key with matches table, in this example I use scores.id

Get matches which its score is null (use leftJoin with some additional condition)

$upcomingMatch = Match::leftjoin('scores', 'matches.id', '=', 'scores.match_id')
           ->where(function ($where)
           {
               $where->where('matches.away_team_id', '=', '1')
               ->orWhere('matches.home_team_id', '=', '1');
           })
           ->whereNull('scores.id')
           ->latest('matches.match_date')
           ->first();