laravel collections complex and/or Expressions

Question

Trying to use and/or where statements as if they were nested in parentheses. example:

Select * from myTable
where
  (id > 78 or name is not null)
  and term_date >= NOW())

Trying to get these complex where statements to be used in Laravel Collections like so:

$query = myTableModel::where(['id', '>', 78])
    ->orWhereNotNull('name')
    ->where('term_date', '>=', date('Y-m-d'))

But this type of query would return a where like this

where
  (id > 78 or
  name is not null and
  term_date >= Now())

or any other odd combination of where instead of setting up nested parentheses.

Is there a way to solve this type of issue using laravel Collections?

score 1 · Accepted Answer · answered May 13 '19 at 22:03

To get the query that you are trying to achieve you could try adding a callback to the where method and adding your where/orWhere in there like so:

$query = myTableModel::where(function($query) {
    $query->where('id', '>', 78)
        ->orWhereNotNull('name');
})->where('term_date', '>=', date('Y-m-d'));

Running this on a local User model gives the following when doing a toSql() which is what I believe you're after.

select * from `users` where (`id` > ? or `name` is not null) and `term_date` >= ?

score 0 · Answer 2 · answered May 13 '19 at 22:33

So, the concept to go about solving this is to use closures.

If you want to accomplish the example I gave then you would do something like this

$query = mytableModel::where(function($q){
    $q->where(['id', '>', 78])
        ->orWhereNotNull('name');
    })->where('term_date', '>=', date('Y-m-d'))
    ->get();

For a more complex example please Click Here

laravel collections complex and/or Expressions

2 Answers2