Laravel 5.6 Api - Search,sort and filter on list of data

Question

Hi I am developing a rest api endpoint for retrieving paginated list of users. In the frontend, there are options to search with all the listed columns, sort by all columns and filter by name, status and created date.

So far I have created a repository and local scopes in user model for search, sort and filter. This is my code so far. I am confused with the filter option. Since a user a call filter with all the three options. How to pass those values in api in most optimised way?

Controller:

public function index(Request $request)
{
    $this->userRepository->getAllUsers($request);
}

Repository function:

public function getAllUsers($request)
{
    // Search Parameter
    isset($request->q)? $q = $request->q: $q = null;

    // Sort Parameter
    if ( isset($request->sortby) && (isset($request->direction)) ) {
        $sort[$request->sortby] = $request-> direction;
    }

    return User::where('type','=','student')
                ->ofSearch($q)
                ->ofSort($sort)
                ->paginate($per_page)
}

Model:

public function scopeOfSearch($query, $q)
{
    if ( $q ) {
        $query->orWhere('name', 'LIKE', '%' . $q . '%')
              ->orWhere('school', 'LIKE', '%' . $q . '%')
              ->orWhere('email', 'LIKE', '%' . $q . '%')
              ->orWhere('phone', 'LIKE', '%' . $q . '%')
              ->orWhere('class', 'LIKE', '%' . $q . '%');
    }

    return $query;
}

public function scopeOfSort($query, $sort = [])
{
    if ( ! empty($sort) ) {
        foreach ( $sort as $column => $direction ) {
            $query->orderBy($column, $direction);
        }
    } 
    else {
        $query->orderBy('users.name'); 
    }

    return $query;
}

Answer 1

Anyways I fixed it my creating another post endpoint which will send all the filters with its value. I am not sure if this is the correct way but now I can think of only like this.

Update

I had implemented the filter by following the below tutorial.

https://m.dotdev.co/writing-advanced-eloquent-search-query-filters-de8b6c2598db

Answer 2

I'll change your repository's code this way:

public function getAllUsers($request)
{
    // Set query builder
    $qb = User::query();
    if($request->has('q')){
        $qb->ofSearch($q);
    }
    if($request->has('sortby')){
        //Handle default parameter of get with second argument
        $qb->orderBy($request->get('sortBy'), $request->get('direction', 'ASC'));
    }

    return $qb->paginate();
}

Request::get method handle isset checks for you!

Answer 3

An easy solution for who needs sort and filter DB data using a Laravel API (tested in Laravel 5.x and 6.1).

First: Create your table in DB. You can do it manually for test proposital, but I recommend follow this: https://laravel.com/docs/5.7/migrations

In that exemple the table was named 'my_task';

Then create your Model typing in the root Laravel directory:

php artisan make:model MyTask (return: Model created successfully.)

The model will be created inside app directory.

Next, create your Controller typing in the root Laravel directory:

php artisan make:controller MyTaskController (return: Controller created successfully.)

The controller will be created inside app/Http/Controllers/ After, insert the code below inside the controller MyTaskController.php file:

<?php

namespace App\Http\Controllers;

use App\MyTask;
use DB;
use Illuminate\Http\Request;

class MyTaskController extends Controller
{
       public function filter($parameter)
                {
                        $filter = DB::table('my_task')
                                ->where('field123', '=', $parameter)
                                ->get();
                        return $filter;
                }
}

Lastly: Add a route pointing to your controller in the routes/api.php file:

Route::get("my-task/{parameter}", "MyTaskController@filter");

Test your endpoint using a browser or the Postman:

http://127.0.0.1/api/my-task/value

In the url above "value" is the value you want to look for in the database my-task table.

Ps: You can create new routes poiting to other functions inside the same controller to have others types of manipulations, as 'sort'.