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:
And if user searches again for "PlayStation 4", I need to have it on the top of the list. Like this:
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);
}