Laravel 5.6：如何制作App \ sys_data :: where（$ moreWhere） - > paginate（10）; 工作？

Question

I have three different columns in my database. I want to allow user to fetch data from these columns based on his/her needs. The user could choose to fetch all data from all columns or the user could choose specific information about a column.

$moreWhere="";
if($request->status != 'all')
    $moreWhere="['status', '=', '$request->status'],";
if($request->category != 'all')
    $moreWhere = $moreWhere . "['cat', '=', '$request->cat'],";
if($request->type != 'all')
    $moreWhere = $moreWhere . "['type', '=', '$request->type']";

$moreWhere = '[' . $moreWhere . ']';

$showData = sys_data::where($moreWhere)->wherebetween('date', [$request->from_date, $request->to_date])->paginate(10);

The problem I have is that, when I pass the $moreWhere variable to "where" clause of the eqlouent, I get an error.

It gives me the following error.

Illuminate \ Database \ QueryException (42S22)
SQLSTATE[42S22]: Column not found: 1054 Unknown column 
'[['status', '=', 'Yes'],['cat', '=', 'Food'],['type', '=', 'Free']]' 
in 'where clause' (SQL: select count(*) as aggregate 
from `sys_data` where `[['status', '=', 'Yes'],['cat', '=', 'Food'],['type', '=', 'Free']]` = `` 
and `date` between 2018-09-01 and 2018-09-03)

If I manually add

[['status', '=', 'Yes'],['cat', '=', 'Food'],['type', '=', 'Free']]

as argument to "where" like following

sys_data::where([['status', '=', 'Yes'],['cat', '=', 'Food'],['type', '=', 'Free']])

then it works like a charm.

However, the same thing saved in a variable and assigned to "where", just doesn't work. Am I missing something?

I am sorry I am new to Laravel and programming, so any help would be greatly appreciated. Again, I am using Laravel 5.6, if that's relevant here.

Thanks

Answer 1

If you want to dynamically add where statements to your query depending on other settings you can either start your query with a variable or add a closure inside a where call.

Option 1:

$query = sys_data::query();

if($request->status != 'all')
    $query->where('status', $request->status);
if($request->category != 'all')
    $query->where('cat', $request->cat);
if($request->type != 'all')
    $query->where('type', $request->type);

$query->wherebetween('date', [$request->from_date, $request->to_date])->paginate(10);

$showData = $query;

Option 2:

$showData = sys_data::where(function($query){
    if($request->status != 'all')
        $query->where('status', $request->status);
    if($request->category != 'all')
        $query->where('cat', $request->cat);
    if($request->type != 'all')
        $query->where('type', $request->type);

    return $query;
})->wherebetween('date', [$request->from_date, $request->to_date])->paginate(10);