使用laravel查询生成器根据相关表排序结果

Question

我有两个模型/表：User和POST。一个用户可以有多个帖子，每个帖子属于一个用户。

users:
id    |    name    | 
--------------------
1     |   user 1   | 
2     |   user 2   | 
3     |   user 3   | 

posts:
id    |    user_id  |       created_at      |    
---------------------------------------------
1     |      1      |    2018-02-19 08:00   |  
2     |      2      |    2018-02-19 09:00   |
3     |      3      |    2018-02-19 10:00   |
4     |      1      |    2018-02-19 11:00   |  
5     |      1      |    2018-02-19 12:00   |
6     |      2      |    2018-02-19 13:00   |

我希望能够返回以下结果

[
    {
        id: 3,
        name: "user 3"
    },
    {
        id: 1,
        name: "user 1"
    },
    {
        id: 2,
        name: "user 2"
    }
]

因为用户3最近的帖子是在最早之前创建的，然后是用户1，然后是用户2。

到目前为止,我已经尝试过了,但它为用户返回了重复的结果：

    return $this->builder
                ->join('posts', function ($join) {
                    $join->on('users.id', '=', 'posts.user_id')
                         ->orderBy('posts.created_at', 'desc')
                         ->limit(1);
                })
                ->orderBy('posts.created_at', 'asc')
                ->get();

    return $this->builder
                ->join('posts', 'users.id', '=', 'posts.user_id')
                ->groupBy('users.id')
                ->take(1)
                ->orderBy('posts.created_at', 'asc')
                ->get();

Answer 1

下面是一个原始的MySQL查询,它可以实现以下功能：

SELECT
    t1.id, t1.name
FROM users t1
LEFT JOIN
(
    SELECT user_id, MAX(created_at) AS created_at
    FROM posts
    GROUP BY user_id
) t2
    ON t1.id = t2.user_id
ORDER BY
    t2.created_at;

下面是我对相应的Laravel查询的尝试：

DB::table('users')
    ->select('id', 'name')
    ->join(DB::raw('(SELECT user_id, MAX(created_at) AS created_at
                    FROM posts GROUP BY user_id) t'),
    function($join)
    {
       $join->on('users.id', '=', 't.user_id');
    })
    ->orderBy('t.created_at', 'ASC')
    ->get();