Laravel Mongodb Raw mongo query with date

Question

I am working on laravel 5.1 and using jessenger mongodb package. I am using raw query to fetch data but i am confused how to use date with that as currently it is returning null result.

 $resultSet = DB::connection('mongodb')->collection('wc_mycollection')->raw(function ($collection){
            return $collection->aggregate([
                [
                    '$match'=>[
                        'created_at'=>[
                            '$gte' => Previous day midnight,
                            '$lt' => Current Time
                        ]
                    ]
                ],
                [
                    '$group' => [
                        '_id' => '$some_id',

                    ]
                ]
            ]);
        });

what should i do?

After you are assigning date param, print the query and see if the date parameter is setting properly, if yes then try to convert that timestamp to mongo based time format. Look Here: https://docs.mongodb.org/v3.0/core/shell-types/#mongo-shell-date-type — Gaurav Dave, Feb 11 '16 at 09:51

chridam · Answer 1 · 2016-02-11T10:12:56.253

1

There is a really nice date handling package in laravel called Carbon that you could use with your queries. If you want to get records from start of today, use Carbon's startOfDay() property or to get the previous date midnight, use Carbon::yesterday()->endOfDay().

Joining all of this together you can construct your pipeline as:

$previousDayMidnight = Carbon::yesterday()->endOfDay(); // or $startOfToday = Carbon::now()->startOfDay()
$currentTime = Carbon::now();
$result = DB::collection('wc_mycollection')->raw(function($collection)
{
    return $collection->aggregate(array(
        array(
            '$match' => array(
                'created_at' => array(
                    '$gte' => $previousDayMidnight, // or $startOfToday
                    '$lt' => $currentDateTime
                )
            )
        ),
        array(
            '$group' => array(
                '_id' => '$some_id',
                'count' => array(
                    '$sum' => 1
                )
            )
        )   
    ));
});

Another approach would be to natively use MongoDate objects, you could try

$start = new MongoDate(strtotime(date('Y-m-d H:i:s', '-1 days')));
$end = new MongoDate(strtotime(date('Y-m-d H:i:s')));
$result = DB::collection('wc_mycollection')->raw(function($collection)
    {
        return $collection->aggregate(array(
            array(
                '$match' => array(
                    'created_at' => array(
                        '$gte' => $start, 
                        '$lt' => $end
                    )
                )
            ),
            array(
                '$group' => array(
                    '_id' => '$some_id',
                    'count' => array(
                        '$sum' => 1
                    )
                )
            )   
        ));
    });

edited Feb 11 '16 at 10:12

answered Feb 11 '16 at 08:38

chridam

100,957
23
236
235

I am getting empty set.my collection looks like this : { "_id" : ObjectId("55e6eff42f706e30047b23c8"), "war_id" : 3, "topic_id" : 5, "point_id" : 7, "point" : 1, "comment_id" : "55e6efdd2f706e0d047b23ca", "user_id" : 11, "upvote_id" : 3, "updated_at" : ISODate("2015-09-02T12:47:48.191Z"), "created_at" : ISODate("2015-09-02T12:47:48.191Z") } – Sameer Sheikh Feb 11 '16 at 09:06
One way you can debug a pipeline that's giving you unexpected results is to run the aggregation with just the first pipeline operator. If that gives the expected result, add the next. In the answer above, you'd first try aggregating just the `$match`; if that works, add the `$group`. This can help you narrow down which operator is causing issues. – chridam Feb 11 '16 at 09:10
Also, to help you with debugging, just check to see if those date variables are giving you the correct dates as well as checking with your actual MongoDB dates. – chridam Feb 11 '16 at 09:12
The match operator is causing the issue – Sameer Sheikh Feb 11 '16 at 09:17
Can you debug the `$match` operator by changing the date variables in the range query, see if some dates match? – chridam Feb 11 '16 at 09:20
i dont know why but it is always giving empty set whenever i try. – Sameer Sheikh Feb 11 '16 at 09:43
Did you properly install the [**Carbon**](https://github.com/briannesbitt/Carbon#installation) library? If you can't use Carbon then you can, as an alternative, try creating the dates from `MongoDate` constructor, as shown in my updated answer. – chridam Feb 11 '16 at 10:14

score 1 · Answer 2 · answered Feb 11 '16 at 10:02

1

Try this option:

'$match'=>[
      'created_at'=>[
             '$gte' => new Date("2016-10-02T00:00:00.000Z"),
             '$lt' => new Date("2016-11-02T00:00:00.000Z")
      ]
]

See, if that works.

answered Feb 11 '16 at 10:02

Gaurav Dave

6,838
9
25
39

score 0 · Answer 3 · answered Mar 04 '21 at 17:09

0

This worked for me:

$start = new \MongoDB\BSON\UTCDateTime(new \DateTime("-8 days"));

answered Mar 04 '21 at 17:09

Jannie Theunissen

28,256
21
100
127

Laravel Mongodb Raw mongo query with date

3 Answers3