select sum column and group by with Mongodb and Laravel

Question

I have a issue with my query:

I want to

SELECT "user_info" with SUM "amount" and GROUP BY "user_id"

I am using Laravel 5 and jenssegers/laravel-mongodb

Thank you so much.

score 3 · Answer 1 · answered Jun 16 '16 at 05:36

3

http://laravel.io/forum/10-05-2014-raw-select-using-jenssegers-laravel-mongodb

check link above or use this as i write.

$total = DB::table('user_info')->sum('amount')->groupBy('user_id')->get();

answered Jun 16 '16 at 05:36

Tejas Mehta

791
1
6
15

1

Thank Tejas, If i use MYSQL: It like: `SELECT user_id, ..., SUM("amount") as total_amount FROM table WHERE ... AND ... GROUP BY user_id;` I use your code and it doesn't work with me. I have some problem: `- Field "amount" save by STRING type (can not SUM in normal)` `- Some WHERE in query.` Thanks. – Dzung Nguyen Jun 16 '16 at 06:15

score 2 · Answer 2 · answered Jun 16 '16 at 06:43

For better performance use the underlying MongoDB driver's aggregation framework methods as this uses the native code on the MongoDB server rather than the .groupBy() methods which basically wraps mapReduce methods.

Consider the following aggregation operation which uses the $group pipeline and the $sum operator to do the sum aggregation:

db.collectionName.aggregate([
    {
        "$group": {
            "_id": "$user_id",
            "user_info": { "$first": "$user_info" },
            "total": { "$sum": "$amount" }
        }
    }
]);

The equivalent Laravel example implementation:

$postedJobs = DB::collection('collectionName')->raw(function($collection) {
    return $collection->aggregate(array(
        array(
            "$group" => array(
                "_id" => "$user_id",
                "user_info" => array("$first" => "$user_info")
                "total" => array("$sum" => "$amount")
            )
        )   
    ));
});

select sum column and group by with Mongodb and Laravel

2 Answers2

Linked