如果有新帖子，请在菜单上标记

Question

First of all, I'm pretty new (noob) on Laravel, and it's awesome. I'm making a blog, and everything is great (In my humble and inexperienced opinion).

I want to display a notification on the navbar if there are articles posted less than 24 hours ago. I've made it, but I'm sure there's is a much better way, because three more SQL queries are needed (One for each category)

In my controller

$newPostEntrevistas = FALSE;
$newPostAcercaDeMi = FALSE;
$newPostEstiloDeVida = FALSE;

$lastPostInterviews = Post::where('category', 'Interviews')->orderBy('created_at', 'desc')->first(['created_at']);
$lastPostAboutMe = Post::where('category', 'About me')->orderBy('created_at', 'desc')->first(['created_at']);
$lastPostLifestyle = Post::where('category', 'Lifestyle')->orderBy('created_at', 'desc')->first(['created_at']);

    if ($lastPostInterviews->created_at->diffInHours() < 24) {
        $newPostInterviews = TRUE;
    }

    if ($lastPostAboutMe->created_at->diffInHours() < 24) {
        $newPostAboutMe = TRUE;
    }

    if ($lastPostLifestyle->created_at->diffInHours() < 24) {
        $newPostLifestyle = TRUE;
    }

Then I pass it to a view like this

$posts = Post::orderBy('created_at', 'desc')->paginate($this->paginationNumber());
        return view('pages.index', [
            'posts' => $posts,
            'newPostInterviews' => $newPostinterviews,
            'newPostboutMe' => $newPostAboutMe,
            'newPostLifestyle' => $newPostLifestyle,
        ]);

And in the view I catch it like this (In a partial, actually)

<nav>
    <div class="nav-wrapper">
        <ul>
            <li><a href="/"><i class="fa fa-home"></i> Home</a></li>
            <li>
                <a href="/interviews">
                    Interviews
                    @if($newPostInterviews)
                        <i class="fa fa-exclamation-circle"></i>
                    @endif
                </a>
            </li>
            <li>
                <a href="/lifestyle">
                    Lifestyle
                    @if($newPostLifestyle)
                        <i class="fa fa-exclamation-circle"></i>
                    @endif
                </a>
            </li>
            <li>
                <a href="/about-me">
                    About me
                    @if($newPostAboutMe)
                        <i class="fa fa-exclamation-circle"></i>
                    @endif
                </a>
            </li>
        </ul>
    </div>
</nav>

As I said, it works fine. But I want to know your opinions on performance and other (and better) ways to achieve that or good practices, etcetera.

Result:

It's in spanish, I translated every variable and route to make it easier for you to understand the idea.

Answer 1

If it's useful for someone I optimized it like this:

$newPosts = DB::table('posts')
              ->where('created_at', '>=', Carbon::now()->subHours(24)->toDateTimeString())
              ->groupBy('category')
              ->select('category', DB::raw('COUNT(category) as number'))
              ->pluck('number', 'category');

The result is something like:

[
  'Interviews' => 5,
  'Lifestyle' => 7,
  'About me' => 1,
]

And in the view

<nav>
    <div class="nav-wrapper">
        <ul>
            <li><a href="/"><i class="fa fa-home"></i> Home</a></li>
            <li>
                <a href="/interviews">
                    Interviews
                    @if(isset($newPosts['Interviews']))
                        <span class="badge">{{ $newPosts['Interviews'] }}</span>
                    @endif
                </a>
            </li>
            <li>
                <a href="/lifestyle">
                    Lifestyle
                    @if(isset($newPosts['Lifestyle']))
                        <span class="badge">{{ $newPosts['Lifestyle'] }}</span>
                    @endif
                </a>
            </li>
            <li>
                <a href="/about-me">
                    About me
                    @if(isset($newPosts['About me']))
                        <span class="badge">{{ $newPosts['About me'] }}</span>
                    @endif
                </a>
            </li>
        </ul>
    </div>
</nav>

That way only one query is used and you can show the number of the new posts.

Thanks to willvincent ar Laracasts.