Query Builder use where and orWhere

Hello folks,

im using a query builder to get a filtered list of files in the database.

A logged in user should only see files which he is permitted to see. The files have a name in the Database where the permission is integrated, e.g. ****-AR_Filename.pdf where -AR is the permission.

I can get the user permissions as an array. An with it i wanted to loop over the permissions array and get the files with the given permission.

It works, but i have further filters which should be applied to the given files.

The Code looks like this.

$files_query = (new File)->newQuery();

$user_permissions = Auth::user()->permissions->toArray();

//only get files where user have permissions
foreach ($user_permissions as $permission) {
  $files_query = $files_query->orWhere('name', 'LIKE', '%-'.$permission['name'].'%');                
}

//this filter should be applied to the returned files from above.
if($request->query('p') == 'today'){
   $files_query = $files_query->whereDate('files.updated_at', '=', Carbon::today()->toDateString());
}

I know when im doing it like that, the query ist fired after each other, and therefor the orWhere makes the where obsolete.

But i don't know how to solve it.

I hope you could understand my plan, and i would appreciate advice to solve the problem.

Here is the test log

There was 1 failure:

1) Tests\Feature\FileManagementTest::a_user_can_get_a_list_of_files_to_publish_which_where_uploaded_today_and_for_which_he_has_sufficient_permissions
Failed to assert that the response count matched the expected 2
Failed asserting that actual size 3 matches expected size 2.

Best regards

s3nior

tykus

7 years ago

Best Answer

Level 104

Your orWheres can be troublesome, you want to evaluate them like this:

$files_query = (new File)->newQuery();

$user_permissions = Auth::user()->permissions->toArray();

//only get files where user have permissions
$files_query->where(function ($query) {
    foreach ($user_permissions as $permission) {
    $query->orWhere('name', 'LIKE', '%-'.$permission['name'].'%');                
    }
});

//this filter should be applied to the returned files from above.
if($request->query('p') == 'today'){
   $files_query = $files_query->whereDate('files.updated_at', '=', Carbon::today()->toDateString());
}
``

1 like

Please or to participate in this conversation.