MongoDB Raw query to select documents which are in given datetime period

Question

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 ?

score 4 · Answer 1 · answered Apr 12 '17 at 05:56

4

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();

answered Apr 12 '17 at 05:56

Autista_z

2,491
15
25

There is no difference between created_at and update_at But you are right I should check a single field for date time check , Let me try your approach. – Mohammad_Hosseini Apr 12 '17 at 06:01
@Autisa_z I've tried your approach - the or statement part is working and thanks for it, but the date check still not working and I get 0 result. – Mohammad_Hosseini Apr 12 '17 at 06:05
@Mohammad_Hosseini and what is format of the columns created_at/uploated_at? – Autista_z Apr 12 '17 at 06:09
They are ISODate : "updated_at": ISODate("2017-04-10T09:35:58.641Z"), "created_at": ISODate("2017-04-10T09:35:58.641Z") – Mohammad_Hosseini Apr 12 '17 at 06:13
Well, then I think, that this is the problem, you are trying to compare DateTime format with ISODate format. – Autista_z Apr 12 '17 at 06:17
@Austisa_z any idea how to fix it ? – Mohammad_Hosseini Apr 12 '17 at 06:26
I've fixed the datetime formate : $Fromdatetime = $request->input('from_date'); $from_date = new DateTime($Fromdatetime); $from_date = $from_date->format(DateTime::ATOM); When I echo $from_date I get : "2017-01-07T09:08:59+00:00", But still I get 0 result . – Mohammad_Hosseini Apr 12 '17 at 06:37
I never worked with ISO dates, but ist problem the ending of date? in database you have "T09:35:58.641Z" but the variable is "T09:08:59+00:00". – Autista_z Apr 12 '17 at 06:42
I've checked that too , I've manually set "from_date": "2017-04-10T09:35:58.641Z" And run the code again but still no luck. – Mohammad_Hosseini Apr 12 '17 at 06:51

score 4 · Accepted Answer · answered Apr 12 '17 at 10:20

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.

MongoDB Raw query to select documents which are in given datetime period

2 Answers2