Laravel + Mongo DB latest unique entries from database

Question

I'm working on a Laravel + Mongo DB project where we store data like this:

_id                      | machinename | SoftwareKey  | created_at
-----------------------------------------
5cf6c4a7fdce2778e833dd88 | VM007       | TestSoftware | 2017-01-30 13:22:39
-----------------------------------------
5cf6c4a7fdce2778e833324  | VM008       | TestSoftware | 2017-01-30 13:23:42
-----------------------------------------
5cf6c4a7fdce27783244355  | VM009       | TestSoftware | 2017-01-30 13:25:54
-----------------------------------------
5cf6c4a7fdce27723424345  | VM007       | TestSoftware | 2017-01-30 13:25:
-----------------------------------------
5cf6c4a7fdce23432432745  | VM008       | TestSoftware | 2017-01-30 13:25:54
-----------------------------------------
5cf6c4a7fdce27732467876  | VM009       | TestSoftware | 2017-01-30 13:25:33

I need to adapt the method that returns all entries from this table to return only the latest entry for a machine name, basically to return this part:

_id                      | machinename | SoftwareKey  | created_at
-----------------------------------------
5cf6c4a7fdce2778e833dd88 | VM007       | TestSoftware | 2017-01-30 13:22:39
-----------------------------------------
5cf6c4a7fdce2778e833324  | VM008       | TestSoftware | 2017-01-30 13:23:42
-----------------------------------------
5cf6c4a7fdce2778324      | VM009       | TestSoftware | 2017-01-30 13:25:54

The index method:

public function index() {
    $hills = Hill::all();

    return fractal()
        ->collection($hills)
        ->transformWith(new HillTransformer)
        ->toArray();
    }

I found this question and I tried to adapt my method:

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Hill;
use App\Transformers\HillTransformer;
use DB;

class HillController extends Controller
{
    public function index() {
       $hills = DB::select("SELECT *
       FROM hills
       JOIN (SELECT machinename, MAX(created_at) created_at
               FROM hills
           GROUP BY machinename) machinenames
         ON machinename = machinenames.machinename
        AND created_at = machinenames.created_at");

        return fractal()
            ->collection($hills)
            ->transformWith(new HillTransformer)
            ->toArray();
    }
}

But I get this exception:

"message": "Call to a member function prepare() on null", "exception": "Symfony\Component\Debug\Exception\FatalThrowableError", "file": "/home/vagrant/code/hilltracking/vendor/laravel/framework/src/Illuminate/Database/Connection.php", "line": 326,

Is there another way to accomplish this? Thanks!

Btw. I'm using laravel-mongodb and fractal libraries.

Laravel + Mongo DB latest unique entries from database

0 Answers0