Get latest row of each group by with Laravel MongoDB

Question

I have a table called gps in a MongoDB that looks like this:

| id | asset_id | coordinates | created_at |
--------------------------------------------
| 1  | 11       | 23,-26      | 2018-11-05 |
| 2  | 22       | 33,-36      | 2018-10-04 |
| 3  | 33       | 23,-27      | 2018-11-01 |
| 4  | 33       | 31,-26      | 2018-10-05 |
| 5  | 11       | 23,-46      | 2018-11-02 |
| 6  | 22       | 32,-21      | 2018-11-01 |
--------------------------------------------

I am using Laravel and a Laravel MongoDB Library to use for query building.

My goal is to fetch the latest coordinate of each asset using a group by clause like asked in this question only it will have to make use of the Laravel MongoDB Library mentioned earlier. Another example I tried looking at was this post.

Essentially I should have this returned to me:

| id | asset_id | coordinates | created_at |
--------------------------------------------
| 1  | 11       | 23,-26      | 2018-11-05 |
| 3  | 33       | 23,-27      | 2018-11-01 |
| 6  | 22       | 32,-21      | 2018-11-01 |
--------------------------------------------

I am assuming these examples did not work for me because I do not have access to all the Eloquent functions with this library, only a few basic ones as well as the fact that I cannot use SQL syntax in my query builder.

Answer 1

I was able to solve my problem by making use of aggregate calls and following this post here.

This is the code I have ended up using although I don't think it is the most Eloquent way possible:

$result = GPS::raw(function ($collection) {
    return $collection->aggregate([
        [
            '$sort' => [
                'created_at' => -1
            ]
        ],
        [
            '$group' => [
                '_id' => '$asset_id', 
                'gps' => [
                    '$push' => '$$ROOT'
                ]
             ]
        ],
        [
            '$replaceRoot' => [
                'newRoot' => [
                    '$arrayElemAt' => [
                        '$gps', 0
                    ]
                ]
            ]
        ]
    ]);
});

Answer 2

You should try this:

DB::table('gps')->orderBy('created_at','desc')->groupBy('asset_id')->get();