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.

score 0 · Answer 1 · 2018-11-05T08:16:39.207

0

You should try this:

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

edited Nov 05 '18 at 08:16

answered Nov 05 '18 at 07:30

This will pull through every record, not the latest of each asset, and won't order it by the created_date so it will not be able to help me. – Jason Hodkinson Nov 05 '18 at 07:51
This will still pull through every record though, the problem is that I need to order by the created date and group by the asset_id at the same time. However when I try and group by the asset_id, it doesn't keep the order of the created_at columns – Jason Hodkinson Nov 05 '18 at 08:13
Unfortunately this does not help either, it seems to be merging records together rather than showing all of them as part of a group. I think I need to do something with the raw MongoDB collection. Maybe something like this [question](https://stackoverflow.com/questions/24142931/how-to-convert-mongodb-aggregation-query-to-laravel-mongodb-by-jenssegers) – Jason Hodkinson Nov 05 '18 at 09:35

score 0 · Accepted Answer · answered Nov 05 '18 at 10:23

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

Get latest row of each group by with Laravel MongoDB

2 Answers2