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
- 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
- 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
via
Chebli Mohamed
