如何在LARAVEL上统计最近1个月每天的交易

Question

我在事务表上有这样的数据：

___________________________________________________________________
| id  | cash    | total   | invoice_number | created_at           |
-------------------------------------------------------------------
|  1  | 25000   | 25000   | TR-3435345345  |  2022-07-01 14:24:36 |
-------------------------------------------------------------------

我想做的是计算每天的交易数量。换句话说，预期的输出是这样的：

[1, 0, 0, 0, 0, 0, 0, 0, 0, 0, ...]

但结果是：

[1]

这是我写的语法：

$transaction = Transaction::select('*')->groupBy('created_at')->whereBetween('created_at', [Carbon::now()->firstOfMonth(), Carbon::now()->endOfMonth()])->count();

有没有办法让我得到预期的输出？

Answer 1

这是上月图表记录的代码。

$lastMonth = CarbonPeriod::create(Carbon::now()->subDays(29), Carbon::now());
$lastMonthOrders = [];
foreach ($lastMonth as $date) {
   $lastMonthOrders['days'][] = $date->format("l");
   
   // Here is the count part that you need
   $lastMonthOrders['orders'][] = DB::table('orders')->whereDate('created_at', '=', $date)->count(); 
}
$dashboard_infos['lastMonthOrders'] = $lastMonthOrders;

输出：

array:2 [▼
  "days" => array:30 [▼
    0 => "Friday"
    1 => "Saturday"
    2 => "Sunday"
    3 => "Monday"
    4 => "Tuesday"
    5 => "Wednesday"
    6 => "Thursday"
    7 => "Friday"
    8 => "Saturday"
    9 => "Sunday"
    10 => "Monday"
    11 => "Tuesday"
    12 => "Wednesday"
    13 => "Thursday"
    14 => "Friday"
    15 => "Saturday"
    16 => "Sunday"
    17 => "Monday"
    18 => "Tuesday"
    19 => "Wednesday"
    20 => "Thursday"
    21 => "Friday"
    22 => "Saturday"
    23 => "Sunday"
    24 => "Monday"
    25 => "Tuesday"
    26 => "Wednesday"
    27 => "Thursday"
    28 => "Friday"
    29 => "Saturday"
  ]
  "orders" => array:30 [▼
    0 => 0
    1 => 0
    2 => 0
    3 => 0
    4 => 0
    5 => 1
    6 => 0
    7 => 0
    8 => 0
    9 => 0
    10 => 0
    11 => 0
    12 => 0
    13 => 3
    14 => 0
    15 => 0
    16 => 0
    17 => 0
    18 => 0
    19 => 0
    20 => 0
    21 => 0
    22 => 0
    23 => 0
    24 => 0
    25 => 0
    26 => 0
    27 => 2
    28 => 0
    29 => 0
  ]
]

根据您的项目进行修改。

Answer 2

在您的代码中，您将获得结果集的计数，因此它将始终返回一行。相反，如果在select语句中使用count，则将按组获取计数值。在分组中，您也在考虑整个DateTime，而不是只考虑date(cloumn_name)

-- create a table
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  invoice_number varchar(50) NOT NULL,
  cash INTEGER(11) NOT NULL,
  create_at varchar(50)
);
-- insert some values
INSERT INTO orders VALUES (1, 'fdsgfdsgds11', 100,"2022-07-02 14:24:36");
INSERT INTO orders VALUES (2, 'fdsgfdsgds12', 200,"2022-07-02 15:24:36");
INSERT INTO orders VALUES (3, 'fdsgfdsgds13', 300,"2022-07-02 15:24:36");
INSERT INTO orders VALUES (4, 'fdsgfdsgds14', 400,"2022-07-01 15:24:36");
INSERT INTO orders VALUES (5, 'fdsgfdsgds15', 500,"2022-07-01 15:24:36");
INSERT INTO orders VALUES (6, 'fdsgfdsgds16', 600,"2022-07-03 15:24:36");
INSERT INTO orders VALUES (7, 'fdsgfdsgds17', 700,"2022-08-03 15:24:36");
-- fetch some values
SELECT count(id),date(create_at) FROM orders where date(create_at) between '2022-07-01' and '2022-07-30' group by date(create_at) order by date(create_at);

输出

2|2022-07-01
3|2022-07-02
1|2022-07-03

如果返回行中不存在日期，则该日期的订单计数将为零。通过这种方式，您不需要每天执行一个DB查询。它将减少您的读取操作。否则，您需要每月至少运行30个查询。