如何在拉拉维尔雄辩的原始方法中从 mongo db 集合中获取状态字段的计数?

Question

如何在 laravel 雄辩的原始方法中编写分组依据和计数状态？ 我正在使用mongodb。

出于测试目的，我使用 mysql 进行了计数(结果和查询如下(，但我不知道如何在 mongodb 中计数。

请检查下面给出的集合和代码。谢谢

.SQL

SELECT count(*), status, date FROM booking WHERE date BETWEEN '2017-08-01' AND '2017-08-31' group by status, date order by date

结果

count status date 3 1 2017-08-01 1 2 2017-08-01 2 2 2017-08-02 1 3 2017-08-02 1 1 2017-08-03 1 2 2017-08-03 1 1 2017-08-04 1 2 2017-08-04 1 3 2017-08-04 1 3 2017-08-05 2 2 2017-08-06 2 3 2017-08-07 1 1 2017-08-08 1 2 2017-08-08 1 3 2017-08-08 1 1 2017-08-09 1 2 2017-08-09

控制器.php

$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'],
],
],
[
'$project' =>
[
'checkin_from' => '$_id.checkin_from',
'cabinname' => '$_id.cabinname',
],
],
[
'$sort' =>
[
'checkin_from' => 1
],
],
]);
});

蒙戈系列

{ "_id" : ObjectId("598c07a4f2017f945660bc1d"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-01T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(1) }
{ "_id" : ObjectId("598c07b5f2017f945660bc1e"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-01T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(2) }
{ "_id" : ObjectId("598c07c3f2017f945660bc1f"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-01T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(1) }
{ "_id" : ObjectId("598c07cdf2017f945660bc20"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-01T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(1) }
{ "_id" : ObjectId("598c07f5f2017f945660bc21"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-02T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(2) }
{ "_id" : ObjectId("598c07fef2017f945660bc22"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-02T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(2) }
{ "_id" : ObjectId("598c0802f2017f945660bc23"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-02T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(3) }
{ "_id" : ObjectId("598c08a4f2017f945660bc24"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-03T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(2) }
{ "_id" : ObjectId("598c08acf2017f945660bc25"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-03T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(1) }
{ "_id" : ObjectId("598c08bcf2017f945660bc26"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-04T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(3) }
{ "_id" : ObjectId("598c08c3f2017f945660bc27"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-04T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(2) }
{ "_id" : ObjectId("598c08cef2017f945660bc28"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-04T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(1) }
{ "_id" : ObjectId("598c08dff2017f945660bc29"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-05T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(3) }
{ "_id" : ObjectId("598c08eef2017f945660bc2a"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-06T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(2) }
{ "_id" : ObjectId("598c08f3f2017f945660bc2b"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-06T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(2) }
{ "_id" : ObjectId("598c0901f2017f945660bc2c"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-07T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(3) }
{ "_id" : ObjectId("598c0905f2017f945660bc2d"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-07T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(3) }
{ "_id" : ObjectId("598c090ef2017f945660bc2e"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-08T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(2) }
{ "_id" : ObjectId("598c091af2017f945660bc2f"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-08T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(3) }
{ "_id" : ObjectId("598c091ff2017f945660bc30"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-08-08T00:00:00Z"), "is_delete" : NumberLong(0), "status" : NumberLong(1) }

Answer 1

下面是带有计数和分组依据的原始查询。

控制器.php

$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','status' => '$status'],
'count' => ['$sum' => 1]
],
],
[
'$project' =>
[
'checkin_from' => '$_id.checkin_from',
'cabinname' => '$_id.cabinname',
'status' => '$_id.status',
'count' => 1,
],
],
[
'$sort' =>
[
'checkin_from' => 1
],
],
]);
});