Order by the latest (most recent) related model [Laravel]

Question

I am using Laravel 5.4 and I have the following relations:

• Forum hasMany Thread (threads())
• Thread hasMany Post (posts())
• Thread belongsTo User (user())
• Post belongsTo User (user())

Currently, in my ThreadsController@index I have the following:

public function index()
{
    $threads = $this->forum->threads()
        ->approved()
        ->withCount(['posts AS approved_replies' => function ($query) {
            $query->where('posts.approved', true)->where('posts.is_starting_thread', false);
        }])
        ->with(['posts' => function ($query) { // Posts
            $query->approved()
                ->with('user') // Author of post
                ->latest();
            }]
        )
        ->with('user') // Author of thread
        ->latest()
        ->paginate(20); 

    return view('forums.threads.index')->with([
        'forum' => $this->forum, 'threads' => $threads
    ]);
}

My index.blade.php should show the listing of threads in which for each thread there will be:

• its author (that's why I have ->with('user'))
• number of replies (that's why I have >withCount(['posts AS approved_replies' => function ($query) { ...)

• Date of the newest (latest) post and its author. That's why:

      ->with(['posts' => function ($query) { // Posts
          $query->approved()
              ->with('user') // Author of post
              ->latest(); // LATEST first
          }]
      )
  

  ... because then in index.blade.php I can access the latest post of each thread in the following way:

  @foreach ($threads as $thread) 
      {{ $thread->posts->first()->created_at; }}
      {{ $thread->posts->first()->user->username; }}
  @endforeach 
  

The problem with this code is that threads are sorted by their created_at, not by the most recent post. What I want to achieve is to order threads by the latest (most recent) post, but I don't know how to do this.

Answer 1

Here's how I'd do it. It's not particularly pretty but should do the job

$this->forum->threads()
    ->approved()
    ->join('posts', 'posts.thread_id', '=', 'threads.id')
    ->selectRaw('threads.*, MAX(posts.created_at) AS latest_post_at')
    ->groupBy('threads.id')
    ->orderByDesc('latest_post_at')
    ->withCount(['posts AS approved_replies' => function ($query) {
        $query->where('posts.approved', true)->where('posts.is_starting_thread', false);
    }])
    ->with(['posts' => function ($query) { // Posts
        $query->approved()
            ->with('user') // Author of post
            ->latest();
        }]
    )
    ->with('user')
    ->paginate(20);

It does a join as the other answer suggests, and then groups by thread, using MAX aggregate function to keep the latest date per group of posts.

Edit:

$this->forum->threads()
    ->approved()
    ->join('posts', 'posts.thread_id', '=', 'threads.id')
    ->select('threads.*', 'posts.created_at AS latest_post_at')
    ->whereNotExists(function ($subquery) {
        return $subquery->from('posts AS later_posts')
            ->whereRaw('later_posts.thread_id = posts.thread_id')
            ->whereRaw('later_posts.created_at > posts.created_at');
    })
    ->orderByDesc('latest_post_at')
    ->withCount(['posts AS approved_replies' => function ($query) {
        $query->where('posts.approved', true)
              ->where('posts.is_starting_thread', false);
    }])
    ->with(['posts' => function ($query) { // Posts
        $query->approved()
            ->with('user') // Author of post
            ->latest();
    }])
    ->with('user')
    ->paginate(20);