8 months ago

Paginate a Laravel query with optional parameters?

Posted 8 months ago by uccdev

I'm trying to make a search table with criteria for people to look up - name, surname, ID, course, etc. When they put in their specific values, laravel will paginate the results in a table and show them.

Sounds straightforward, right? The trick is that every field is optional. So if a field is included, it must be searched based on that value. If a field is left blank, then the query must not consider that value in its search (which is to say, the excluded fields can be of any value, only the included fields must be specific).

My solution below, for this specific purpose, does exactly that:

       $name   = ($request->name != '' ? " AND  post.name = '" . $request->name . "'" : "");
       $surname    = ($request->surname != '' ? " AND post.surname = '" . $request->surname . "'" : "");
       $cats_id  = ($request->cats_id > 0 ? " AND post.cats_id = " . $request->cats_id : "" );
       $status_id  = ($request->status_id > 0 ? " AND post.status_id = " . $request->status_id : "");
       $postmen_id = ($request->postmen_id > 0 ? " AND post.postmen_id = " . $request->postmen_id : "");

       $postmen =
    DB::select("SELECT TOP 50
      post.postmen_id, post.name, post.surname, cat.description AS catdesc, reg.description AS regdesc
      FROM postmen AS post
      INNER JOIN cats AS cat
        ON (cat.cats_id = post.cats_id)
      INNER JOIN registration_status AS reg
        ON (reg.status_id = post.status_id)
      WHERE active_flag = 'Y'
      " . $name . $surname . $cats_id . $status_id . $postmen_id .

      return view('postmen_table')->with('postmen', $postmen);

The above code works. So what's the problem? Pagination. In my view, when I call:

        {{ $postmen->links() }}

I get this error: "Call to a member function links() on array"

I know I can get around this with a DB::table query, which might look like this:

             $postmen = DB::table('postmen')
  ->join('cats', 'postmen.cats_id', '=', 'cats.cats_id')
  ->join('registration_status', 'postmen.status_id', '=', 'registration_status.status_id')
  ->select('postmen.postent_id', 'postmen.name', 'postmen.surname',
   'cats.description AS catdesc', 'registration_status.description AS regdesc')

However, this doesn't have the flexibility of the first solution; I have to query by every field, even the ones that the user leaves blank. I know how to express this in DB::select, but not in DB::table, and that's where I am stuck.

Does anyone know anything I could do to remedy this? Any suggestions would be greatly appreciated.

Please sign in or create an account to participate in this conversation.