Laravel complex query issue

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?

tisuchi

6 years ago

Best Answer

Level 70

@snapkwi

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` >= ?

1 like

SnapKWI

6 years ago

Level 1

@tisuchi thank you sir. Saved my day.

Please or to participate in this conversation.