Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Advanced join with AND, OR and IS NULL - Laravel

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?

like image 729
Simon Bengtsson Avatar asked Sep 04 '25 03:09

Simon Bengtsson


1 Answers

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.

like image 116
Jarek Tkaczyk Avatar answered Sep 05 '25 17:09

Jarek Tkaczyk