Laravel MongoDB group by

Question

I'm currently using jenseggers-mongodb eloquent in laravel 5 with mongodb 3.0.3. I'm trying to join two different tables as follows:

User Model

public function userpayment() {
    return $this->hasMany('App\Models\Userpayment', 'user_id', '_id');
}

Userpayment model

public function user() {
    return $this->belongsTo('App\Models\User','user_id');
}

I'm using repository pattern

public function __construct() {
    $this->model = new User;
}

return $this->model->with(array('userpayment'=>function($query){
                                $query
                                ->groupBy('dealCodeNumber')
                                ->get();
                    }))
                    ->where('_id',$sid)

                    ->get();

groupBy dealCodeNumber returns NULL.

array:1 [▼
 0 => array:15 [▼
"_id" => "55813f4cbc59a3f1372e9e6f"
"full_name" => "Varathan"
"username" => "shopsyadmin"
"web_url" => ""
"userpayment" => []
]

without groupby returns all.

array:1 [▼
0 => array:15 [▼
"_id" => "55813f4cbc59a3f1372e9e6f"
"full_name" => "Varathan"
"username" => "shopsyadmin"
"userpayment" => array:2 [▼
  0 => array:57 [▼
    "_id" => "55816ce7cc7d4bac0d000029"
    "dealCodeNumber" => "1398410837"
    "inserttime" => "1398411401"
    "status" => "Pending"
  ]
  1 => array:57 [▶]
]

Querylog with groupby

[query] => shopsy_user_payment.aggregate([{"$match":{"user_id":{"$in":["55813f4cbc59a3f1372e9e6f"]}}},{"$group":{"_id":{"dealCodeNumber":"$dealCodeNumber"}

Why groupby returns null?

Answer 1

To group an column it must be selected before grouping. Changing code to below works:

return $this->model->with(array('userpayment'=>function($query){
    $query
        ->select('product_id','user_id','dealCodeNumber')
        ->groupBy('dealCodeNumber')
        ->get();
}))
    ->where('_id',$sid)
    ->get();

Answer 2

Laravel MongoDB groupby does not work like Laravel MySQL groupby. Cause we can use the aggregate function without doing groupby. But in MongoDB, you have to use groupby if you use an aggregate function in your query.

Let's see the example app/Http/Controllers/TutorialController.php

    <?php

    namespace App\Http\Controllers;

    use App\Models\SupplierPayment;
    use Illuminate\Database\Eloquent\Builder;

    class TutorialController extends Controller
    { 

    public function index()
    {   
        $startDate = date('Y-m-01');
        $endDate   = date('Y-m-30');

        return SupplierPayment::raw(function ($collection) use ($startDate, $endDate) {
            return $collection->aggregate([
                [
                    '$match' => [
                        'payment_month' => [
                            '$gte' => intval($startDate),
                            '$lte' => intval($endDate),
                        ],
                    ],
                ],
                [
                    '$group' => [
                        '_id' => '$payment_month', // Group by the 'payment_month' field
                        'total_paid' => ['$sum' => '$total_paid'], // Calculate the sum of 'total_paid'
                    ],
                ],
                [
                    '$sort' => [
                        '_id' => -1, // Sort in descending order based on total_paid
                    ],
                ],
            ]);
        })
    }
}

The original content you will find in this blog: Exploring GroupBy In Laravel 10 With MongoDB