Mongo groupby is not working with different time in Laravel raw method

Question

The below code is to fetch data from MongoDB and plot sum of amounts and dates in a graph within date range (sum(prepayment_amount) and sum(total_prepayment_amount) group by checkin_from date). The sum of amounts is in y axis and date is in x axis.

From this code, I am getting the sum of amounts and date but the problem is,

see date and time 2017-10-05T19:07:17Z & 2017-10-05T00:00:00Z. In this case (sum(prepayment_amount) and sum(total_prepayment_amount) dates are plotting separately.

If date and time is 2017-10-05T00:07:17Z & 2017-10-05T00:00:00Z, I am getting a correct result.

I don't know where is the issue. I have added expected results. Any help would be appreciated. Thanks

Booking.php (model)

protected $dates = ['bookingdate', 'checkin_from', 'reserve_to'];

DashbardController.php

public function show(Request $request)
    {
        if(!empty($request->cabin) && !empty($request->daterange))
        {
            $cabinName              = $request->cabin;
            $daterange              = explode("-", $request->daterange);
            /*$dateBegin              = new \MongoDB\BSON\UTCDateTime(strtotime($daterange[0])*1000);
            $dateEnd                = new \MongoDB\BSON\UTCDateTime(strtotime($daterange[1])*1000);*/
            $dateBegin              = new \MongoDB\BSON\UTCDateTime(new DateTime($daterange[0]));
            $dateEnd                = new \MongoDB\BSON\UTCDateTime(new DateTime($daterange[1]));
            $bookings               = Booking::raw(function ($collection) use ($cabinName, $dateBegin, $dateEnd) {
                return $collection->aggregate([
                    [
                        '$match' => [
                            'is_delete' => 0,
                            'cabinname' => $cabinName,
                            'checkin_from' => ['$gte' => $dateBegin, '$lte' => $dateEnd]
                        ],
                    ],
                    [
                        '$group' =>
                            [
                                '_id' => ['checkin_from' => '$checkin_from','cabinname' => '$cabinname'],
                                'total_prepayment_amount' => ['$sum' => '$total_prepayment_amount'],
                                'prepayment_amount' => ['$sum' => '$prepayment_amount'],
                            ],
                    ],
                    [
                        '$project' =>
                            [
                                'checkin_from' => '$_id.checkin_from',
                                'cabinname' => '$_id.cabinname',
                                'total_prepayment_amount' => 1,
                                'prepayment_amount' => 1
                            ],
                    ],
                    [
                        '$sort' =>
                            [
                                'checkin_from' => 1
                            ],
                    ],
                ]);
            });

            $totalPrepayAmount       = [];
            $prepayAmount            = [];
            $checkinFrom             = [];
            $serviceFee              = [];
            foreach ($bookings as $booking){
                if(!empty($booking->total_prepayment_amount) && !empty($booking->prepayment_amount)) {
                    $checkinFrom[]       = $booking->checkin_from->format('d.m.y');
                    $totalPrepayAmount[] = $booking->total_prepayment_amount;
                    $prepayAmount[]      = $booking->prepayment_amount;
                    $serviceFee[]        = round($booking->total_prepayment_amount - $booking->prepayment_amount, 2);
                }
            }

            $chartData[] =[
                'label'=> __("statisticsAdmin.totalPrepayAmount"),
                'backgroundColor' => 'rgba(255, 99, 132, 0.2)',
                'borderColor'=> 'rgba(255,99,132,1)',
                'borderWidth'=> 1,
                'data' => $totalPrepayAmount,
            ];

            $chartData[] =[
                'label'=> __("statisticsAdmin.prepayAmount"),
                'backgroundColor' => 'rgba(153, 102, 255, 0.2)',
                'borderColor'=> 'rgba(153, 102, 255, 1)',
                'borderWidth'=> 1,
                'data' => $prepayAmount,
            ];

            $chartData[] =[
                'label'=> __("statisticsAdmin.serviceFee"),
                'backgroundColor' => 'rgba(79, 196, 127, 0.2)',
                'borderColor'=> 'rgba(79, 196, 127, 1)',
                'borderWidth'=> 1,
                'data' => $serviceFee,
            ];

            return response()->json(['chartData' => $chartData, 'chartLabel' => $checkinFrom]);
        }


    }

Booking Collection

    { "_id" : ObjectId("58046a49f8f888a80b00002a"), "cabinname" : "Matras-Haus", "checkin_from" : ISODate("2016-09-17T00:00:00Z"), "prepayment_amount" : "20", "total_prepayment_amount" : "25", "bookingdate" : ISODate("2016-09-17T06:06:01Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1), "payment_status" : "1" }
    { "_id" : ObjectId("58183678d2ae67a404431d5c"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2016-10-31T00:00:00Z"), "prepayment_amount" : "30", "total_prepayment_amount" : "40", "bookingdate" : ISODate("2016-11-01T06:30:16Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1), "payment_status" : "0" }
    { "_id" : ObjectId("581b31f3d2ae674d5f431d5b"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2016-10-31T00:00:00Z"), "prepayment_amount" : "35", "total_prepayment_amount" : "45", "bookingdate" : ISODate("2016-11-03T12:47:47Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1) }
    { "_id" : ObjectId("5821af65d2ae67c82154efc5"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2016-09-05T00:00:00Z"), "prepayment_amount" : "40", "total_prepayment_amount" : "45", "bookingdate" : ISODate("2016-11-08T10:56:37Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1) }
    { "_id" : ObjectId("582558d4d2ae679c4d8b4567"), "cabinname" : "Matras-Haus", "checkin_from" : ISODate("2016-09-17T00:00:00Z"), "prepayment_amount" : "20", "total_prepayment_amount" : "23", "bookingdate" : ISODate("2016-08-17T05:36:20Z"), "is_delete" : NumberLong(1) }
    { "_id" : ObjectId("58352c3cd2ae672341ec89e1"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2016-09-05T00:00:00Z"), "prepayment_amount" : "50", "total_prepayment_amount" : "55", "bookingdate" : ISODate("2016-08-05T05:42:20Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1) }
    { "_id" : ObjectId("58352c3cd2ae672341ec887e1"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-10-05T19:07:17Z"), "prepayment_amount" : "50", "total_prepayment_amount" : "55", "bookingdate" : ISODate("2017-08-05T05:42:20Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1) }
    { "_id" : ObjectId("58352c3cd2ae672341ec887e1"), "cabinname" : "Matras-Haus", "checkin_from" : ISODate("2017-10-05T00:00:00Z"), "prepayment_amount" : "50", "total_prepayment_amount" : "55", "bookingdate" : ISODate("2017-08-05T05:42:20Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1) }

Expecting result

prepayment_amount total_prepayment_amount    checkin_from  cabinname

40                       48                  2016-09-17    Matras-Haus

50                       55                  2017-10-05   Matras-Haus

65                       85                  2016-10-31   Kemptner Hütte

90                       110                 2016-09-05   Kemptner Hütte

50                       55                  2017-10-05   Kemptner Hütte

Image

Please check the image, here you can find the problem. For better understanding I have listed time also in the x-axis.

Note: In graph data not match with question data because I have connected a database with a test server.

Answer 1

In the $group you are using $checkin_from directly as a date with time hence it is grouped by date AND time.

The answer is to use $dateFormat so we have only the date without the time:

'_id' => ['checkin_from' => ['$dateFormat' => ['date' => '$checkin_from', format => '%Y-%m-%d']], 'cabinname' => '$cabinname']

Answer 2

You can use this group stag, Where I am convert string into number. total_prepayment_amount and prepayment_amount are string as per provided data they should be integer to $sum.

  [
    '$group' =>[
      '_id' => [
          'checkin_from' => '$checkin_from',
          'cabinname' => '$cabinname'
       ],
      'total_prepayment_amount' => [
           '$sum' => ['$toInt' => '$total_prepayment_amount']
       ],
      'prepayment_amount' => [
         '$sum' => ['$toInt' => '$prepayment_amount']
       ],
    ],
  ],

like this in query for mongodb

total_prepayment_amount: {$sum : { $toInt: "$total_prepayment_amount"}},

Please make sure mongodb ^4.0 (for use $toInt)