Show last different 5 search results from MySQL

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:

XBox 360
PC
Nintendo
PlayStation 4
Wii

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

PlayStation 4
XBox 360
PC
Nintendo
Wii

Same thing. The `updated_at` has default value as `CURRENT_TIMESTAMP` — harunB10, May 11 '17 at 13:39

score 1 · Answer 1 · edited May 23 '17 at 12:10

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);
}

edited May 23 '17 at 12:10

Community

1
1

answered May 11 '17 at 14:18

yazfield

1,233
11
18

This just eliminates the search term from the query set. From the example above, the "PlayStation 4" is removed from the list and replaced by next item (6th one). – harunB10 May 11 '17 at 15:09
@harunB10 sorry I'm not following you, you want the latest distinct searched values right? I tried the query on a similar table and got the correct results, could you explain how this isn't working so that I could reproduce the problem? – yazfield May 11 '17 at 20:01
It works for new entries (not distinct ones). But when I enter the search that has already been saved in the table, it should be shown in the first place, but instead it becomes eliminated and replaced with the next search term. – harunB10 May 12 '17 at 07:41

Show last different 5 search results from MySQL

1 Answers1