php - Mongo groupby 在 Laravel raw 方法中没有使用不同的时间

Question

下面的代码是从 MongoDB 中获取数据，并在日期范围 (sum(prepayment_amount) and sum(total_prepayment_amount) group by checkin_from date) 内的图表中绘制金额和日期的总和。金额的总和位于 y 轴 中，日期位于 x 轴 中。

从这段代码中，我得到了金额和日期的总和，但问题是，

查看日期和时间2017-10-05T19:07:17Z & 2017-10-05T00:00:00Z。在这种情况下，(sum(prepayment_amount) 和 sum(total_prepayment_amount) 日期分别绘制。

如果日期和时间是 2017-10-05T00:07:17Z & 2017-10-05T00:00:00Z，我得到的结果是正确的。

我不知道问题出在哪里。我添加了预期的结果。任何帮助，将不胜感激。谢谢

Booking.php(模型)

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]);
        }


    }

预订集合

    { "_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) }

预期结果

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

图片

请检查图像，在这里您可以找到问题。为了更好地理解，我还在 x 轴上列出了时间。

注意:图中数据与问题数据不匹配，因为我已将数据库与测试服务器连接。

Answer 1

在 $group 中，您将 $checkin_from 直接用作带时间的日期，因此它按日期和时间分组。

答案是使用 $dateFormat所以我们只有日期没有时间:

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