Query builder didn't find data when using %-CodePudding

I'm trying to use query builder in controller using laravel, and i don't understand but the query didn't find the data.

Here's the code:

public function index()
{
    $data = downloads::all();

    if(request('searchName')){
        $data = $data->where('fileName','like','%'.request('searchName').'%'); //Here's the problem
        return view('download', compact('data'));
    }
    else{
        return view('download', compact('data'));
    }
}

i already tried dd(request('searchName')) and it display the input that i give, so there's no problem here

when I'm using $data->where('fileName','like','%'.request('searchName').'%') there's no data shown

i don't think that i misspell the fileName because when I'm using $data->where('fileName','like',request('searchName')) and it worked and display the file, but the fileName must be exactly the same as the inputed searchName, and of course what i wanted is not this

even when I'm using dd('%'.request('searchName').'%'); it will display "%*searchName*%" that's why i so confused when it didn't work when I'm using $data->where('fileName','like','%'.request('searchName').'%');

I even using SELECT * FROM *tables* WHERE fileName LIKE '%p%'; in SQL Workbench and it worked perfectly fine

Any suggestion of what should i do? Thank you

CodePudding user response：

This looks odd. Why are you filtering the collection instead of adding the where conditional in your query?

Imagine you have thousands of download records but the where condition just match with a few ones, you will be fetching everything just for showing some of them.

IMO, a better approach should be

public function index(Request $request)
{
    $data = downloads::
    when($request->has('searchName'), function($query) use ($request){
        $query->where('fileName','like','%'.$request->searchName.'%');
    })
    ->get();

    return view('download', compact('data'));
}

CodePudding user response：

all() is static method not query builder.If you see internal of all() code then its calling get method

 /**
     * Get all of the models from the database.
     *
     * @param  array|mixed  $columns
     * @return \Illuminate\Database\Eloquent\Collection|static[]
     */
    public static function all($columns = ['*'])
    {
        return static::query()->get(
            is_array($columns) ? $columns : func_get_args()
        );
    }

There are few ways to solve this .

public function index()
{
    $downloads = downloads::query();

    if(!empty(request('searchName'))){
     $downloads->where('fileName','like','%'.request('searchName').'%'); 
       
    }
   
    $data=$downloads->get();
     return view('download', compact('data'));
}

public function index()
{
    $data = downloads::when(!empty(request('searchName')),function($query){
            $query->where('fileName','like','%'.request('searchName').'%');
    })->get();

   
    return view('download', compact('data'));
}

CodePudding user response：

You are trying to apply your querystring with like in a collection. In a collection, you can use the filter($callback_function) method to select elements in the collection. Pass a callback function that returns true for each element to be returned.

In your case, you can use the stristr() function to emulate a LIKE operator, something like this:

collect($data)->filter(function ($item) use ($searchName) {
    return false !== stristr($item->fileName, $searchName);
});