Advanced join with AND, OR and IS NULL - Laravel

Question

I would like to create a query like this.

SELECT *
FROM user
INNER JOIN course ON user.course_id = course.id
AND (user.year_from IS NULL OR course.year_from > course.year_from)
AND (user.year_to IS NULL OR course.year_to < course.year_to)

My first problem is that I don't know how to translate the IS NULL part. Is it even possible? The second problem is how to get the parentheses right. My first thought was to do something similar to advanced where clauses.

$query->join('course', function($join) {
    $join->on(function($join) {
        $join->onNull('user.year_from')->orOn('user.year_from', '>', 'course.year_from');
    };
    $join->on(function($join) {
        $join->onNull('user.year_to')->orOn('user.year_to', '<', 'course.year_to');
    };
});

However the JoinClouse class doesn't have a method onNull and doesn't support passing a closure as parameter. Any other suggestions?

Answer 1

You can't create that exact query with Laravel methods (unless using raw of course).

Here's what does what you need:

$query->join('course', 'user.course_id', '=', 'course.id')
  ->where(function ($q) {
    $q->whereNull('user.year_from')->orWhere('user.year_from', '>', DB::raw('course.year_from'));
  })
  ->where(function ($q) {
    $q->whereNull('user.year_to')->orWhere('user.year_to', '<', DB::raw('course.year_to'));
  });

This is the only way Laravel can handle your query, and it's is basically doing exactly the same.