如何在laravel中获取开始日期和结束日期之间的所有记录？ [重复]
This question already has an answer here:
In my blade file there is a search bar on leave duration column, leave duration column contail startDate & endDate,
In my database i have 2 fields start date and end date, and if i search a date who lies between startDate & endDate than it will give me output,
here in my controller file
public function listOfLeave(Request $request)
{
$from = date('1001-01-01');
$to = date('9999-12-31');
$allLeaves = null;
if (
!empty($request->input('name')) ||
!empty($request->input('leaveType')) ||
!empty($request->input('leaveDate')) ||
!empty($request->input('appliedDate')) ||
!empty($request->input('status'))
) {
$flg = false;
if (!empty($request->input('name'))) {
$flg = true;
$allLeaves = LeaveManagement::where('username', 'LIKE', "%{$request->input('name')}%");
}
if (!empty($request->input('leaveType'))) {
if ($flg) {
$allLeaves = $allLeaves->orWhere('typeOfLeave', 'LIKE', "%{$request->input('leaveType')}%");
} else {
$allLeaves = LeaveManagement::where('typeOfLeave', 'LIKE', "%{$request->input('leaveType')}%");
}
}
if (!empty($request->input('leaveDate'))) {
if ($flg) {
$allLeaves = $allLeaves->orWhereBetween('startDate', 'LIKE', "%{$request->input('leaveDate')}%");
} else {
$allLeaves = LeaveManagement::where('startDate', 'LIKE', "%{$request->input('leaveDate')}%");
}
}
if (!empty($request->input('appliedDate'))) {
if ($flg) {
$allLeaves = $allLeaves->orWhere('startDate', 'LIKE', "%{$request->input('appliedDate')}%");
} else {
$allLeaves = LeaveManagement::where('startDate', 'LIKE', "%{$request->input('appliedDate')}%");
}
}
if (!empty($request->input('status'))) {
if ($flg) {
$allLeaves = $allLeaves->orWhere('status', 'LIKE', "%{$request->input('status')}%");
} else {
$allLeaves = LeaveManagement::where('status', 'LIKE', "%{$request->input('status')}%");
}
}
$allLeaves = $allLeaves->orderBy('username', 'ASC')
->orderBy('typeOfLeave', 'ASC')
->orderBy('startDate', 'ASC')
->orderBy('startDate', 'ASC')
->orderBy('status', 'ASC')
->paginate(2);
return view('pages.newleaverequest')->with(['allLeaves' => $allLeaves]);
} else {
$allLeaves = LeaveManagement::orderBy('username', 'ASC')->paginate(5);
}
$allLeaves->appends(['name' => Input::get('name')]);
return view('pages.newleaverequest', compact('allLeaves'));
}
How can i get records who lies between startdate and enddate???
</div>
Ok. I think this should help you:
if (!empty($request->input('leaveDate'))) {
if ($flg) {
$allLeaves = $allLeaves->whereDate('endDate', '<', $request->input('leaveDate'));
} else {
$allLeaves = LeaveManagement::whereDate('endDate', '<', $request->input('leaveDate'));
}
}
if (!empty($request->input('appliedDate'))) {
if ($flg) {
$allLeaves = $allLeaves->whereDate('startDate', '>', $request->input('appliedDate'));
} else {
$allLeaves = LeaveManagement::whereDate('startDate', '>', $request->input('appliedDate'));
}
}
You can use ..->whereBetween('your_column_name_date', [$from ,$to]);
In your query you will need a where clause for that to filter those results. To get data inside a specific range you can use the whereBetween clause.
The syntax will be like:
$allLeaves = $allLeaves->where('startDate', '>', $from)
->where('endDate', '<', $to)
->orderBy('username', 'ASC')
->orderBy('typeOfLeave', 'ASC')
->orderBy('startDate', 'ASC')
->orderBy('startDate', 'ASC')
->orderBy('status', 'ASC')
->paginate(2);
Here is getting records from users table and get all data of given $start and $end date between records. So you can see following simple example and understand how it is works.
$start = '2019-04-01';
$end = '2019-04-18';
$users = User::select("users.*")
->whereBetween('created', [$start, $end])
->get();
dd($users);