Explaining advance search trait in laravel and flatten object

i have learn this kind of trait for advanced searching from this github project with laravel backend project with vuejs as the front end framework (SPA project) but it did not given any explanation and now when i need something quite different from the example project, i facing problem.

So i hoping someone in here can help me understand this trait for searching, it retrieving this kind of get request

https://bkcuvue.dev/api/v1/user?column=id&direction=desc&per_page=2&page=1&search_column=name&search_operator=like&search_query_1=&search_query_2=

and as the url given, there is column parameter for specifying which column in the query that use for sorting, and then there is direction for controlling sorting (ascending or descending).

and then there is per_page to determine how many row being retrieving at the get call, and then there is page for pagination.

and there is search_column for which column being used for searching with coresponding search_operator for searching operator (like,between,equal, etc) and 2 search_query that use for user inputing keyword for searching.

and to make this kind of get url work i need to add this trait

<?php 

namespace App\Support;

use Validator;

trait FilterPaginateOrder {

    protected $operators = [
        'equal_to' => '=',
        'not_equal' => '<>',
        'less_than' => '<',
        'greater_than' => '>',
        'less_than_or_equal_to' => '<=',
        'greater_than_or_equal_to' => '>=',
        'in' => 'IN',
        'not_in' => 'NOT_IN',
        'like' => 'LIKE',
        'between' => 'BETWEEN'
    ];

    public function scopeFilterPaginateOrder($query)
    {
        $request = request();

        $v = Validator::make($request->all(), [
            'column' => 'required|in:'.implode(',', $this->filter),
            'direction' => 'required|in:asc,desc',
            'per_page' => 'required|integer|min:1',
            'search_operator' => 'required|in:'.implode(',', array_keys($this->operators)),
            'search_column' => 'required|in:'.implode(',', $this->filter),
            'search_query_1' => 'max:255',
            'search_query_2' => 'max:255'
        ]);

        if($v->fails()) {

            //for debug
            // dd($v->messages());
        }

        return $query->orderBy($request->column, $request->direction)
            ->where(function($query) use ($request) {
                // check if search query is empty
                if($request->has('search_query_1')) {
                    // determine the type of search_column
                    // check if its related model, eg: customer.id
                    if($this->isRelatedColumn($request)) {
                        list($relation, $relatedColumn) = explode('.', $request->search_column);
                        return $query->whereHas($relation, function($query) use ($relatedColumn, $request) {
                            return $this->buildQuery(
                                $relatedColumn,
                                $request->search_operator,
                                $request,
                                $query
                            );
                        });
                    } else {
                        // regular column
                        return $this->buildQuery(
                            $request->search_column,
                            $request->search_operator,
                            $request,
                            $query
                        );
                    }
                }
            })
            ->paginate($request->per_page);
    }

    protected function isRelatedColumn($request)
    {
        return strpos($request->search_column, '.') !== false;
    }

    protected function buildQuery($column, $operator, $request, $query)
    {
        switch ($operator) {
            case 'equal_to':
            case 'not_equal':
            case 'less_than':
            case 'greater_than':
            case 'less_than_or_equal_to':
            case 'greater_than_or_equal_to':
                $query->where($column, $this->operators[$operator], $request->search_query_1);
                break;
            case 'in':
                $query->whereIn($column, explode(',', $request->search_query_1));
                break;
            case 'not_in':
                $query->whereNotIn($column, explode(',', $request->search_query_1));
                break;
            case 'like':
                $query->where($column, 'like', '%'.$request->search_query_1.'%');
                break;
            case 'between':
                $query->whereBetween($column, [
                    $request->search_query_1,
                    $request->search_query_2
                ]);
                break;
            default:
                throw new Exception('Invalid Search Operator', 1);
                break;
        }

        return $query;
    }
}

and then to make it works with our model then we need to add it into our model

<?php

namespace App;

use Laravel\Passport\HasApiTokens;
use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
use App\Support\FilterPaginateOrder;
use Spatie\Permission\Traits\HasRoles;

class User extends Authenticatable
{
    use HasRoles, HasApiTokens, Notifiable, FilterPaginateOrder;

    protected $table = 'users';

    public static $rules = [
        'name' => 'required|min:5',
        'email' => 'required|email',
        'username' => 'required|min:5',
        'password' => 'required|min:5',
    ];

    protected $fillable = [
        'id_pus','id_cu','name','email','username', 'password','gambar','status'
    ];

    protected $filter = [
        'id','id_cu','id_pus','name','email','username','gambar','status','created_at'
    ];

    public function getNameAttribute($value){
        return !empty($value) ? $value : '-';
    }

    public static function initialize()
    {
        return [
            'id_cu' => '0' , 'id_pus' => '0', 'name' => '','email' => '', 'username' => '', 'status' => '0', 'gambar' => ''
        ];
    }

    protected $hidden = [
        'password', 'remember_token',
    ];

    // protected $casts = [
    //     'status' => 'boolean',
    // ];

    public function getId(){
        return $this->id;
    }

    public function pus(){
        return $this->belongsTo('App\Pus','id_pus','id')->select('id','name');
    }

    public function CU(){
        return $this->belongsTo('App\CU','id_cu','id')->select('id','name');
    }
}

and then in my controller (which in this example is usercontroller) will be like this

public function index()
{
    $table_data = User::with(array('CU','pus','roles' => function($query){
            $query->select('name');
        }))->select('id','id_cu','id_pus','name','username','gambar','status','created_at')->filterPaginateOrder();

    return response()
        ->json([
            'model' => $table_data
        ]);
}

then it will return

{
  "model": {
    "current_page": 1,
    "data": [
      {
        "id": 8,
        "id_cu": 0,
        "id_pus": 1,
        "name": "test1",
        "username": "test17",
        "gambar": "",
        "status": 1,
        "created_at": "2018-02-27 06:37:10",
        "c_u": null,
        "pus": {
          "id": 1,
          "name": "Puskopdit BKCU Kalimantan"
        },
        "roles": [
          {
            "name": "CU Akses Penuh",
            "pivot": {
              "model_id": 8,
              "role_id": 2
            }
          }
        ]
      },
      {
        "id": 7,
        "id_cu": 0,
        "id_pus": 1,
        "name": "test1",
        "username": "test16",
        "gambar": "",
        "status": 1,
        "created_at": "2018-02-27 06:36:06",
        "c_u": null,
        "pus": {
          "id": 1,
          "name": "Puskopdit BKCU Kalimantan"
        },
        "roles": [

        ]
      }
    ],
    "from": 1,
    "last_page": 4,
    "next_page_url": "https://bkcuvue.dev/api/v1/user?page=2",
    "path": "https://bkcuvue.dev/api/v1/user",
    "per_page": "2",
    "prev_page_url": null,
    "to": 2,
    "total": 8
  }
}

now the question is

1. i don't quite understand how this all code works.... and it giving me trouble when i want a quite specific result like in my second question below
2. as you can see in the json data, the data returned with roles array that contain multiple object and i have no idea how to use those roles for searching the roles name or for sorting roles name according to those trait, i tried to make it like in pus relationship table by using pus.name as search_column parameter but when i tried it to roles relationship roles.name it just not working

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2BZu7SG
via IFTTT