How to search strings inside Array of arrays using eloquent

Question

So I have a databse column where the content is an Array like this:

[{"tag_name":"example1"},{"tag_name":"example2"}, {"tag_name":"example3"}]

What I am trying to do is to retrieve videos with a certain tag. All the videopages have specific tags, and when a user clicks one of those tags, it is redirected to a view where similar videos with the same tag will be displayed.

Right now I have the following controller:

public function search($tag){
    $tag = urldecode($tag);
    $videos = Video::where('tags', 'LIKE', $tag)->paginate(12);
    $settings = Detail::find(1);
    $ads = Ad::find(1);

    return view('search', ['videos' => $videos, 'settings' => $settings, 'ads' => $ads]);

}

And I have the following view (search.blade.php):

@foreach($videos as $video)

    <div class="col-lg-4 col-xs-12">

        <div class="row justify-content-center">

            <a href="{{ route('videos.videopage', $video->id) }}"><img class="articleImg" src="{{$video->imgurl}}"></a>

        </div>


        <div>
            <p class="float-left">{{$title = substr($video->title, 0, 30) . " ..."}}</p>

            <small class="duration float-right">{{$video->duration}}</small>

            <div class="progress float-right">
                <div class="progress-bar bg-success" role="progressbar" style="width: {{$video->rating}}%" aria-valuenow="{{$video->rating}}" aria-valuemin="0" aria-valuemax="100"></div>
            </div>


        </div>

    </div>  


@endforeach

The problem is that right now, no videos are shown in my view, even tough i get no errors at all. Is it because the columns has an array of arrays?

Pol Lluis · Accepted Answer · 2018-06-02 15:13:13Z

2

I assume it's because your controller is returning 0 videos because tag never match. You are using LIKE statement without escaping your tag variables with %, so you are looking to match something that is exactly like tag, and because you have an array of tags as a column that will never happen.

One solution you could implement is changing your tag to something like this:

public function search($tag){
   $tag = urldecode($tag);
   $videos = Video::where('tags', 'LIKE', '%'.$tag.'%')->paginate(12);
   $settings = Detail::find(1);
   $ads = Ad::find(1);

   return view('search', ['videos' => $videos, 'settings' => $settings, 'ads' => $ads]);
}

Here you can read more about the LIKE operator :D

answered Jun 2, 2018 at 15:13

Pol Lluis

1,1627 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Slaveworx Over a year ago

Works like a charm!! Thank you @GaimZz!

Pol Lluis Over a year ago

Also if you are using mysql, you should know that searching on columns like that might not be efficient

Pol Lluis Over a year ago

I'd be better if you stored your tags on a separate table with a hasMany relation between videos and tags, and then you would need to make a join between the two tables to look for videos with certain tags

Collectives™ on Stack Overflow

How to search strings inside Array of arrays using eloquent

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related