Laravel-获取每种UID类型的最后一个条目 [英] Laravel - Get the last entry of each UID type

问题描述

我有一个表，其中包含1000多种产品的100个条目，每个条目都由一个唯一的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

...

我希望能够获得每个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

在一个数据库调用中有可能吗?

我曾尝试使用DB和Eloquent，但到目前为止我得到的结果都是零或表的全部内容.

安迪

解决方案

这很容易在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;

将其转换为Eloquent会有些工作，但希望这可以为您提供一个良好的起点.

如果您希望created_at可能是NULL 并且某个给定的UID可能只包含空创建的值，则可能需要使用LEFT JOIN.

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

解决方案

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.

这篇关于Laravel-获取每种UID类型的最后一个条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！