CODEWITHSUNDEEP
phpmysqljsonlaravel
DisplayName
DisplayName

Reputation: 129

Laravel search in json for value in query where

I'm trying to build tags system. In my database, I have table topics with column tags. Tags are a JSON object.

My route: 

Route::get('t/{tag}', 'myController@tag')->name('tag');

public function tag( $tag )
{
    return view('tags.tag', ['tag' => $tag, 'topics' => DB::table('topics')->get()]);
}

And then:

@foreach ($topics as $topic)
    @if (in_array($tag, json_decode($topic->tags, true)))
    <tr>
        <td class="col-md-6 col-sm-6">
            <a href="#" title="">
                <strong>{{ $topic->topic_name }}</strong>
            </a>    
        </td>
        <td class="col-md-1 col-sm-1 text-right">
            @foreach (json_decode($topic->tags) as $tag)
                <span class="badge badge-primary">{{ $tag }}</span>
            @endforeach
        </td>
    </tr>
    @endif
 @endforeach

I know this is not a perfect solution and I need to use that if in a query. How can I do this? Thanks in advance. P.S I know about '->where('topics->tags', '..')

But my MySQL is older than 5.7 and I can't use these new functions. Any suggestions?

Upvotes: 0

Views: 627

Answers (1)

Praveen Tamil
Praveen Tamil

Reputation: 1156

Please use casts in your model

protected $casts = [
    'tags' => 'array',
];

It will convert JSON to array when retrieving from DB

Refer https://laravel.com/docs/5.4/eloquent-mutators#attribute-casting

Upvotes: 1

Related Questions