显示来自MySQL的最新的5个搜索结果

Question

I have a tool where user enters some search value and that value is stored in another table for logging purposes. Now, I want to display last 5 search results for that user.

After the search is saved, I query it with this function:

public function scopeGetUserSearch($query)
    {
        return $query->select('searchValue')
            ->where('userID', '=', Auth::user()->id)
            ->orderBy('created_at', 'desc')
            ->distinct()
            ->take(5);
    }

I need to distinct the values since user can search same term for many times. (e.g. if user searches for term "Playstation 4" 4 times, it needs to be displayed as once)

And then I display the result in blade:

@foreach($bsUserSearch as $b)
    <div id="brand_box" class="border left" style="min-width: 30px;cursor:pointer;text-align:center;" onclick="$('#company').val('{{$b->searchValue}}');">{{$b->searchValue}}</div>   
@endforeach

Problem is when user searches for "Playstation 4" again, it is not displayed in the first place even though the created_at field is the latest. Or if 5 places is already populated, the term "Playstation 4" won't be shown at all.

So, if I have these search results:

1. XBox 360
2. PC
3. Nintendo
4. PlayStation 4
5. Wii

And if user searches again for "PlayStation 4", I need to have it on the top of the list. Like this:

1. PlayStation 4
2. XBox 360
3. PC
4. Nintendo
5. Wii

Answer 1

Based on this answer, I got this solution

public function scopeGetUserSearch($query)
{
    return $query->select('searchValue', \DB::raw('max(created_at)'))
                 ->where('userID', '=', Auth::user()->id)
                 ->groupBy('searchValue')
                 ->orderBy(\DB::raw('max(created_at), searchValue'), 'desc')
                 ->take(5);
}