laravelio/laravel.io

use Illuminate\Database\Eloquent\Model;

final class Thread extends Model
{
    //
    public static function feedQuery(): Builder
    {
        return static::with([
            'solutionReplyRelation',
            'likesRelation',
            'repliesRelation',
            'repliesRelation.authorRelation',
            'tagsRelation',
            'authorRelation',
        ])
            ->leftJoin('replies', function ($join) {
                $join->on('threads.id', 'replies.replyable_id')
                    ->where('replies.replyable_type', static::TABLE);
            })
            ->orderBy('latest_creation', 'DESC')
            ->groupBy('threads.id')
            ->select('threads.*', DB::raw('
                CASE WHEN COALESCE(MAX(replies.created_at), 0) > threads.created_at
                THEN COALESCE(MAX(replies.created_at), 0)
                ELSE threads.created_at
                END AS latest_creation
            '));
    }
    //
}

use App\Models\Tag;
use App\Models\Thread;
use App\Models\User;

class ThreadsController extends Controller
{
    //
    public function overview()
    {
        $threads = [];
        $filter = $this->getFilter();

        if ($filter === 'recent') {
            $threads = Thread::feedPaginated();
        }

        if ($filter === 'resolved') {
            $threads = Thread::feedQuery()
                ->resolved()
                ->paginate(20);
        }

        if ($filter === 'unresolved') {
            $threads = Thread::feedQuery()
                ->unresolved()
                ->paginate(20);
        }

        $tags = Tag::orderBy('name')->get();
        $topMembers = User::mostSolutionsInLastDays(365)->take(5)->get();
        $moderators = User::moderators()->get();

        return view('forum.overview', compact('threads', 'filter', 'tags', 'topMembers', 'moderators'));
    }
    //
}