如何在laravel值的两个日期之间进行搜索

Question

我有一个表单，有搜索的一些数据

我可以搜索正常字段

但是我无法获得日期范围

控制器:

public function index() {
$query = Matter::query();
$query->when(request('search_client','search_file_ref','search_file_status','search_date_from','search_date_to'), function($query){
$query->where('client_company', 'like', '%' . request('search_client') . '%');
$query->where('file_ref', 'like', '%' . request('search_file_ref') . '%');
$query->where('status', 'like', '%' . request('search_file_status') . '%');
$query->whereBetween('created_at', ['search_date_from', 'search_date_to']);
});
return $query->orderBy('id','asc')->get();
}

视图:

<b-col md="4">
<form @submit.prevent="searchDateFrom">
<label for="">Choose File Status:</label>
<div class="input-group">
<input
v-model="search_date_from"
type="date"
placeholder="Search File Status"
class="form-control"
/>
<input
v-model="search_date_to"
type="date"
placeholder="Search File Status"
class="form-control"
/>
<div class="input-group-append">
<button type="submit" class="btn btn-primary">
<i class="fas fa-search"></i>
</button>
</div>
</div>
</form>
</b-col>

…

data(){
return {
search_client: "",
search_file_ref: "",
search_file_status: "",
search_date_from: "",
search_date_to: "",
matters:[]
}
},

…

searchDateFrom(){
axios.get('/api/auth/matter?search_date_from=' + this.search_date_from + 'search_date_to=' + this.search_date_to)
.then(response => this.matters = response.data)
},

所以当我尝试在controller:

中这样做时

$query->whereBetween('created_at', ['2022-06-01', '2022-06-07']);

我正在获取数据和正确的范围

但是当我像这样添加请求时:

$query->whereBetween('created_at', ['search_date_from', 'search_date_to']);

我停止从API获取数据，没有显示

模型:

{
use HasFactory;
protected $fillable = [
'matter_type','client_company','description','file_group',
'control_account','pic','lawyer','task_assign','task_recipient',
'file_ref','remark','purchaser_1','purchaser_2','status'
];
protected $casts = [
'created_at' => 'datetime:m / d / Y',
];
}

我对请求做错了什么吗?

我能够获得数据和搜索其他字段(除了日期)

并尝试实现搜索字段，以便用户能够设置两个日期并获得该范围内的结果

Answer 1

您的查询是正确的，但是您犯了一个小错误，因为您忘记在requesthelper中包装您的日期参数。不是从请求传递日期，而是将字符串字面值search_date_from和search_date_to传递给whereBetween语句。

代码

$query->whereBetween('created_at', ['search_date_from', 'search_date_to']);

修正代码

$query->whereBetween('created_at', [request('search_date_from'), request('search_date_to')]);

请看下面更正后的代码。

public function index() {
$query = Matter::query();
$query->when(request('search_client','search_file_ref','search_file_status','search_date_from','search_date_to'), function($query){
$query->where('client_company', 'like', '%' . request('search_client') . '%');
$query->where('file_ref', 'like', '%' . request('search_file_ref') . '%');
$query->where('status', 'like', '%' . request('search_file_status') . '%');
$query->whereBetween('created_at', [request('search_date_from'), request('search_date_to')]);
});
return $query->orderBy('id','asc')->get();
}

Answer 2

首先，您在这里将变量写成字符串。$query->whereBetween('created_at', ['search_date_from', 'search_date_to']);

你可以这样写:$query->whereBetween('created_at', [$search_date_from, $search_date_to]);

我更喜欢这样写:$query->whereBetween(DB::raw("DATE('created_at')"), array($from, $to));