How to use groupBy Month and Sum in laravel MongoDB

Question

Query to get monthly orders (total orders per month(count) or total sales per month(sum)): Tried this query, but it is not working. Also tried with other results from StackOverflow, but I didn't understand how it is done with MongoDB query. This is the link to that question : select-sum-column-and-group-by-with-mongodb-and-laravel

$monthly_orders = Order::select(
    DB::raw('sum(total_amount) as sum'),
    DB::raw('YEAR(created_at) year, MONTH(created_at) month'),
)
->groupBy('month')
->get();

When I try to get total amount by using group by customer ID , it is returning sum as null

$monthly_orders = Order::selectRaw('sum(total_amount) as sum, customer_id')
    ->groupBy('customer_id')
    ->pluck('sum', 'customer_id');

Result :

Illuminate\Support\Collection {#2123 ▼
  #items: array:4 [▼
    "6098e5ff5977a25ee96a2a42" => null
    "60dbf87f7d8ffb7cdb2233d2" => null
    "605af409195d8e59e34893f2" => null
    "60ddae4a66fb69678e45f056" => null
  ]
}

John Lobo · Accepted Answer · 2021-07-18 11:19:26Z

2

Try using raw and aggregate

$monthly_orders = Order::raw(function ($collection) {
        return $collection->aggregate([
            [
                '$group' => [
                    "_id" => '$customer_id',
                    'customer_id' => ['$first' => '$customer_id'],
                    'sum' => ['$sum' => '$total_amount']
                ]
            ],

        ]);
    });

you can use pluck

$monthly_orders->pluck('sum','customer_id')

Group by month

 $monthly_orders = Order::raw(function ($collection) {
            return $collection->aggregate([
                [
                    '$group' => [
                       "_id" => ['$month'=>'$created_at'],
                        'customer_id' => ['$first' => '$customer_id'],
                        'sum' => ['$sum' => '$total_amount']
                    ]
                ],
    
            ]);
        });

edited Jul 18, 2021 at 11:19

answered Jul 18, 2021 at 9:55

John Lobo

15.4k2 gold badges18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rosh Over a year ago

Thanks , this is returning the sum based on customer IDs now, Thanks a lot. It would have been of great help if can help me to group by month using created at time stamp

John Lobo Over a year ago

@RoshJ try this "_id" => ['$month'=>'$created_at'],

Rosh Over a year ago

Can u add it to the answer so that i can accept the answer

Collectives™ on Stack Overflow

How to use groupBy Month and Sum in laravel MongoDB

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related