Ordering results based on related table with laravel query builder

Question

I have two models / tables: User and Post. A user can have many posts, each post belongs to one user.

I'm trying to return a list of users based on how long ago their most recent post was created, in ascending order.

For example

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   |

I would like to be able to return the following result

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

Since user 3 has their most recent post created the most time ago, followed by user 1, then user 2.

I've tried this so far but it is returning duplicate results for the users:

    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();

I've also tried this which is not returning any duplicate results but the ordering is wrong:

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

I feel it is some sort of combination of the two I'm after but I can't work it out. Any help would be greatly appreciated, many thanks. I'm using Laravel 5.4

Answer 1

Here is a raw MySQL query which should do the trick:

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;

Here is my attempt at the corresponding Laravel query:

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();

Answer 2

I know thats not the exact thing that you want but you can go like this

Post::with('user')->orderBy('created_at', 'DESC')->distinct()->get(['user_id']);

and it will give you this

array:3 [▼
  0 => array:2 [▼
    "user_id" => 1
    "user" => array:13 [▶]
  ]
  1 => array:2 [▼
    "user_id" => 2
    "user" => array:13 [▶]
  ]
  2 => array:2 [▼
    "user_id" => 3
    "user" => array:13 [▶]
  ]
]