How to count transactions per day in the last 1 month on LARAVEL-CodePudding

I have data on transaction table like this:

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

What I want to do is I want to count the number of transactions per day. In other words, the expected output is like this:

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

but the output that comes out is:

[1]

This is the syntax I wrote:

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

Is there a way for me to get the output as expected?

CodePudding user response：

Here is the code for last month chart record.

$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;

Output:

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
  ]
]

Modify based on your project.

CodePudding user response：

In your code, you are getting a count of the result set so it will always return a single row. instead, if you use count in a select statement then you will get the count value by the group.

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

output

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

if the date is not present in the return row then for that date order count will be zero.