DoeJohn
1 year ago

Order by the latest (most recent) related model

Posted 1 year ago by DoeJohn

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 [email protected] 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.

Please sign in or create an account to participate in this conversation.