我有一个laravel 4项目,我需要制作一个具有高级搜索功能的模块,这是我难以理解的问题。
这是我的表格:
<div class="form-group">
{{ Form::open(['route' => 'requests.search']) }}
{{ Form::label('status', 'Status:') }}
{{ Form::select('status', $statuses, null, ['class' => 'form-control']) }}
</div>
<!-- Category Form Input -->
<div class="form-group">
{{ Form::label('category', 'Category:') }}
{{ Form::select('category', $categories, null, ['class' => 'form-control']) }}
</div>
<!-- Teamleader Form Input -->
<div class="form-group">
{{ Form::label('teamleader', 'Team Leader:') }}
{{ Form::select('teamleader', $projectmembers, null, ['class' => 'form-control']) }}
</div>
<!-- Requestid Form Input -->
<div class="form-group">
{{ Form::label('requestid', 'Request ID:') }}<br/>
<span>Between:</span>
{{ Form::text('requestidstart', null, ['class' => 'form-control', 'style' => 'width:100%;']) }}
<span>And:</span>
{{ Form::text('requestidend', null, ['class' => 'form-control', 'style' => 'width:100%;'])}}
</div>
<!-- Requestdate Form Input -->
<div class="form-group">
{{ Form::label('requestdate', 'Request Date:') }}<br/>
<span>From:</span>
{{ Form::text('requestdatestart', null, ['class' => 'form-control etadatepicker', 'style' => 'width:100%;']) }}
<span>To:</span>
{{ Form::text('requestdateend', null, ['class' => 'form-control etadatepicker', 'style' => 'width:100%;'])}}
</div>
<!-- Requestduedate Form Input -->
<div class="form-group">
{{ Form::label('requestduedate', 'Due Date:') }}<br/>
<span>From:</span>
{{ Form::text('requestduedatestart', null, ['class' => 'form-control etadatepicker', 'style' => 'width:100%;']) }}
<span>To:</span>
{{ Form::text('requestduedateend', null, ['class' => 'form-control etadatepicker', 'style' => 'width:100%;'])}}
</div>
{{ Form::hidden('search', 'advanced') }}
{{ Form::submit('Search') }}
{{ Form::close() }}<br/>
在我的控制器中,我有以下开关来确定用户是在进行简单的文本搜索还是高级搜索:
switch($search)
{
case 'text':
$requests = DataRequest::search($query, ["id", "userid", "subject", "details", "eta", "leader", "member1", "member2", "member3", "member4", "member5", "member6", "status", "time_spent", "date_completed", "category", "comments", "response_method", "created_at", "updated_at"])
->paginate(10);
return View::make('requests.index', ['requests' => $requests, 'statuses' => $statuses, 'requestresponsetype' => $requestresponsetype, 'projectmembers' => $projectmembers, 'categories' => $categories, 'query' => $query]);
break;
case 'advanced':
break;
default:
$requests = DataRequest::orderBy('id', 'DESC')->paginate(10);
return View::make('requests.index', ['requests' => $requests, 'statuses' => $statuses, 'requestresponsetype' => $requestresponsetype, 'projectmembers' => $projectmembers, 'categories' => $categories, 'query' => $query]);
break;
}
显然,当我为"文本"案例做这件事时,我确实知道如何使用输入来查询数据库以进行简单的文本搜索。
对于"高级"情况,我将如何遍历每个字段,利用它们的所有查询,并返回满足字段中所有查询的结果?
此外,我知道我可以做:
$requests = DataRequest::where('category', '=', $advancedCategories)
->orWhere('status', '=', $advancedStatus)
->orWhere(...)
->orWhere(...)
->orWhere(...)
->orWhere(...)
->orWhere(...)
->paginate(10);
但是,如果其中一个字段为空,则会出现以下错误:"where clause"中的1054未知列"
如果orWhere
条件的输入值为空,则不会引发任何MySQL错误,条件仅为OR WHERE column = ""
。但是,这并不是您想要的条件,因为它将在结果集中包括该列为空的所有条目。
以下是我如何处理它,以保持它的清洁,并只添加所需的条件:
// Define an array with the names of the input parameters for advanced search
$searchParams = ['category', 'teamleader', 'requestidstart', 'requestidend', 'requestdatestart', 'requestdateend', 'requestduedatestart', 'requestduedateend'];
// Create a blank query for your model on which you can add conditions
$query = DataRequest::query();
// Add only the params that have been passed values from the form as conditions
foreach ($searchParams as $param) {
if (Input::has($param)) {
$query->orWhere($param, Input::get($param));
}
}
// Get your paginated results
$requests = $query->paginate(10);