I want to select documents which are in user given date time period like between :
$from_date : "2017-01-07 09:08:59" To `$to_date : "2017-08-09 09:08:59"`
I'm using laravel framework and jenssegers/laravel-mongodb mongodb driver to run my query, And this is my Raw query :
$normal_banners = BannerView::raw(function ($collection) use ($id, $from_date, $to_date) {
$conditions = [
["camp_id" => ['$eq' => $id]],
['$or' => [
['seat_target_status' => ['$eq' => true]],
['seat_target_status' => ['$eq' => false]]
]],
['camp_target_status' => ['$eq' => false]],
];
if ($from_date) {
$conditions[] = ['created_at' => ['$gte' => $from_date]];
}
return $collection->aggregate([
['$match' => ['$and' => $conditions]],
]);
})->count();
But my problem is that it returns 0 as result; while there are 16 documents in this time period.
I've tried this method to get the count of them but still 0 result :
$normal_banners = BannerView::Where('camp_id', $id)
->where(function ($query) {$query->where('seat_target_status', true)->orWhere('seat_target_status', false);
})
->where('camp_target_status', false)
->where("created_at", ">=", $from_date)
->count();
FYI : I've converted datetime of input to ISODate Which is the mongodb datatype for created_at
field.
$Fromdatetime = $request->input('from_date');
$from_date = new DateTime($Fromdatetime);
$from_date = $from_date->format(DateTime::ISO8601);
mongodb field data type is :
"updated_at": ISODate("2017-04-10T09:35:58.641Z"),
"created_at": ISODate("2017-04-10T09:35:58.641Z")
My input data type is : "2017-01-07T09:08:59+0000"
Any suggestion ?
You have to "put in braces" orWhere function and in your raw query you are testing $to_date with updated_at column, but in Eloquent code you are making between with created_at column
$targeted_banners = BannerView::Where('camp_id', $id)
->where(function($query){$query->where('seat_target_status', true)->orWhere('seat_target_status', false);})
->where('camp_target_status', false)
->where("created_at", ">=" $from_date)
->where("updated_at", "<=", $to_date)
->count();
I've solved this problem using Carbon::createDateFrom method, it create a UTC date time base on my input :
Inputs :
{
"from_date": "2017-03-10",
"to_date": "2017-04-09"
}
Converting input dates :
$from_date_arr = explode("-",$request->input('from_date'));
$from_date = Carbon::createFromDate($from_date_arr[0],$from_date_arr[1],$from_date_arr[2]);
$to_date_arr = explode("-",$request->input('to_date'));
$to_date = Carbon::createFromDate($to_date_arr[0],$to_date_arr[1],$to_date_arr[2]);
And This is the query I run which worked :
$normal_banners = BannerView::Where('camp_id', $id)
->where(function ($query) {$query->where('seat_target_status', true)->orWhere('seat_target_status', false);
})
->where('camp_target_status', false)
->where("created_at", ">=",$from_date)
->where("created_at", "<=",$to_date)
->count();
There is a strange problem still with jessenger driver which whereBetween is not working and we should use two where clause to make it work.
Hope solves others problem.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With