Laravel - Get the last entry of each UID type

Question

I have a table that has 100's of entries for over 1000 different products, each identified by a unique UID.

ID  UID                 MANY COLUMNS    CREATED AT
1   dqwdwnboofrzrqww1   ...             2018-02-11 23:00:43
2   dqwdwnboofrzrqww1   ...             2018-02-12 01:15:30

3   dqwdwnbsha5drutj5   ...             2018-02-11 23:00:44
4   dqwdwnbsha5drutj5   ...             2018-02-12 01:15:31

5   dqwdwnbvhfg601jk1   ...             2018-02-11 23:00:45
6   dqwdwnbvhfg601jk1   ...             2018-02-12 01:15:33

...

I want to be able to get the last entry for each UID.

ID  UID                 MANY COLUMNS    CREATED AT
2   dqwdwnboofrzrqww1   ...             2018-02-12 01:15:30
4   dqwdwnbsha5drutj5   ...             2018-02-12 01:15:317
6   dqwdwnbvhfg601jk1   ...             2018-02-12 01:15:33

Is this possible in one DB call?

I have tried using DB as well as Eloquent but so far I either get zero results or the entire contents of the Table.

Andy

Answer 1

This is easy enough to handle in MySQL:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT UID, MAX(created_at) AS max_created_at
    FROM yourTable
    GROUP BY UID
) t2
    ON t1.UID        = t2.UID AND
       t1.created_at = t2.max_created_at;

Translating this over to Eloquent would be some work, but hopefully this gives you a good starting point.

Edit: You may want to use a LEFT JOIN if you expect that created_at could ever be NULL and that a given UID might only have null created values.

Answer 2

You can use a self join to pick latest row for each UID

select t.*
from yourTable t
left join yourTable t1 on t.uid = t1.uid
and t.created_at < t1.created_at 
where t1.uid is null

Using laravel's query builder it would be similar to

DB::table('yourTable as t')
    ->select('t.*')
    ->leftJoin('yourTable as t1', function ($join) {
        $join->on('t.uid','=','t1.uid')
             ->where('t.created_at', '<', 't1.created_at');
    })
    ->whereNull('t1.uid')
    ->get();

Laravel Eloquent select all rows with max created_at

Laravel Eloquent group by most recent record

Answer 3

SELECT p1.* FROM product p1, product p2 where p1.CREATED_AT> p2.CREATED_AT group by p2.UID

Answer 4

You can achieve this with eloquent using orderBy() and groupBy():

$data = TblModel::orderBy('id','DESC')->groupBy('uid')->get();

Answer 5

SOLVED

Thanks to Tim and M Khalid for their replies. It took me down the right road but I hit a snag, hence why I am posting this solution.

This worked:

        $allRowsNeeded = DB::table("table as s")
            ->select('s.*')
            ->leftJoin("table as s1", function ($join) {
                $join->on('s.uid', '=', 's1.uid');
                $join->on('s.created_at', '<', 's1.created_at');
            })
            ->whereNull('s1.uid')
            ->get();

However I got an Access Violation so I had to go in to config/database.php and set

'strict' => false,

inside the 'mysql' config, which removes ONLY_FULL_GROUP_BY from the SQL_MODE.

Thanks again.

Answer 6

You have to use ORDER BY, and LIMITSQL parameters, which will lead you to an easy SQL request :

for exemple, in SQL you should have something like this :

SELECT *
FROM table_name
ORDER BY `created_at` desc
LIMIT 1

This will returns everything in the table. The results will be ordering by the column "created_at" descending. So the first result will be what you're looking for. Then the "LIMIT" tells to return only the first result, so you won't have all your database.

If you wanna make it with eloquent, here is the code doing the same thing :

$model = new Model;
$model->select('*')->orderBy('created_at')->first();