Laravel search in json for value in query where

Question

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?

Why wouldn't you be able to use where()?

ggdx
– ggdx

2017-10-10 11:54:03 +00:00
Commented Oct 10, 2017 at 11:54 — ggdx
– ggdx, Commented Oct 10, 2017 at 11:54
.. or the best way is to update mysql?

DisplayName
– DisplayName

2017-10-10 11:54:13 +00:00
Commented Oct 10, 2017 at 11:54 — DisplayName
– DisplayName, Commented Oct 10, 2017 at 11:54

Praveen Tamil · Accepted Answer · 2017-10-10 11:57:32Z

1

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

answered Oct 10, 2017 at 11:57

Praveen Tamil

1,16611 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Laravel search in json for value in query where

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related