Laravel Eloquent; Show the most recent record with LIKE search

Question

I'm building a reporting system, to which I import, on a daily basis, a bunch of files into my database. The issue encountered is I may have duplicate but valid records in my database.

In my database I would have the following, example, data set.

ID        NAME         MATERIAL      PRICE        CREATED_AT
1         Foo          FP1234        3.45         2016-01-01 15:00:00
2         Bar          FP8177        7.21         2016-01-01 15:00:00
3         Foo          FP1234        4.87         2016-01-02 15:00:00
4         Baz          FP1231        3.00         2016-01-02 15:00:00

I would like to know how I can return the above result set, but exclude row 1 because it is the same as row 3, and row 3 is more recent. Adding on to my reasoning, row 1 and 3 are the same, but since row 3 has the more recent created_at timestamp, I would only want to see this.

The tricky thing is I need to be able to paginate on top of this, and using Laravel's Collection method forPage is something I was hoping to avoid.

My problem is

I can use the Laravel orderBy and groupBy if running a search with an equal too operand, however I need to account for like as well.

Answer 1

You can use subqueries to obtain an ordered dataset to perform other functions on.

One solution (I do not know your tableName, or what your LIKE variable ($queryTerm) is passed in as):

DB::table('tableName')->whereIn('tableName.id', function($subquery) {
    $subquery->select('tableName.id')
        ->from('tableName')
        ->orderBy('created_at','DESC');
})
->where('tableName.column', 'LIKE', '%' .$queryTerm. '%')
->where('tableName.column', '=', $anotherQueryTerm)
->groupBy('tableName.MATERIAL')
->get();

The exact same would work with Eloquent, as Eloquent uses the Query Builder underneath to create database queries:

SomeModel::whereIn('tableName.id', function($subquery) {
    $subquery->select('tableName.id')
        ->from('tableName')
        ->orderBy('created_at','DESC');
})
->where('tableName.column', 'LIKE', '%' .$queryTerm. '%')
->where('tableName.column', '=', $anotherQueryTerm)
->groupBy('tableName.MATERIAL')
->get();