I have a table with date format YYYY-MM-DD and fields are:

user | assign_date | removal_date   A |  2020-01-01 | null   B |  2020-01-01 | 2020-01-05 C |  2020-01-10 | null  

2020欧洲杯手机版注册How using Laravel Query Builder or simple SQL query I can get users to compare assign_date and removal date with selected_date?The example of the needed results.

selected date | users  2020-01-03 | A,B (user C has not recruited yet)2020-01-06 | A  (user C has not recruited yet and user B has already been fired)2020-01-11 | A,C  (user C has already recruited, but user B has already been fired)
  • I think you can use eloquent and query builder – TsaiKoga 2 days ago

2020欧洲杯手机版注册It seems you have nullable date type field.

So you can filter like this:

For Eloquent:

Model::where('assinged_date', '<=', $selected_date)       ->where(function($q) use ($selected_date) {            $q->whereNull('removal_date')              ->orWhere('removal_date', '>=', $selected_date);         })

For QueryBuilder:

DB::table('table_name')->where('assinged_date', '<=', $selected_date)                     ->where(function($q) use ($selected_date) {                          $q->whereNull('removal_date')                            ->orWhere('removal_date', '>=', $selected_date);                       })
  • I try check it in tinker like this: $date = new Carbon\Carbon('2019-12-26'), RefCommittee::where('assignment_date', '<=', $date)->where(function($q) use ($date) { $q->whereNull('removal_date')->orWhere('removal_date', '>=', $date);}) and it returned Illuminate\Database\Eloquent\Builder {#3158} with empty – Maxim Stogniy 2 days ago
  • please put a default date value for removal_date to avoid error with your query. – Qonvex620 2 days ago
  • What is a default value should be for a removal_date ? For example? After all, an user can be hired and never fired – Maxim Stogniy 2 days ago
  • @MaximStogniy can u post your record that you think that it need to be displayed. – TsaiKoga 2 days ago
  • @MaximStogniy It seems you are using column date type to compare with carbon's timestamp, try to use $date = (new Carbon\Carbon('2019-12-26'))->format('Y-m-d') – TsaiKoga 2 days ago

2020欧洲杯手机版注册You can use this one

Model::where('assinged_date', '<=', $selected_date)   ->orWhere('removal_date', '>=', $selected_date);   ->get();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy2020欧洲杯手机版注册

Not the answer you're looking for? Browse other questions tagged or ask your own question.