Search in JSON column using Laravel eloquent

Question

I worked with Laravel 5.4 eloquent and I ran into a problem.

I have a database table called posts , and in it is a column called template_ids . It saves the values ​​in json_encoded format, for example:

 ["1","25","48"] 

Now I want to apply a filter to my query based on an array of identifiers:

 $id_access = array:3 [ 0 => "1" 1 => "3" 2 => "48" ] 

I am trying to search if any of the values ​​of $id_access present in the database column, template_ids .

I tried:

 Post::where('accessable_to',1)->whereIn('template_ids', $template_access_array)->paginate(3); 

In addition, I tried:

 Post::where('accessable_to',1)->whereRaw("JSON_CONTAINS(template_ids, ".$template_access.")")->paginate(3); 

This one has already been viewed, but it does not work for me.

Answer 1

To find out if the template_ids JSON field contains "any" values ​​in the needle array, you will need to use several OR 'd JSON_CONTAINS , which requires MySQL 5.7:

 $ids = ['1', '3', '48']; Post::where('accessable_to', 1) ->where(function ($query) use ($ids) { $firstId = array_shift($ids); $query->whereRaw( 'JSON_CONTAINS(template_ids, \'["' . $firstId . '"]\')' ); foreach ($ids as $id) { $query->orWhereRaw( 'JSON_CONTAINS(template_ids, \'["' . $id . '"]\')' ); } return $query; }); return Post::paginate(3); 

The Laravel query constructor issues a query like:

 SELECT * FROM "posts" WHERE "accessable_to" = ? AND ( JSON_CONTAINS(template_ids, '["1"]') OR JSON_CONTAINS(template_ids, '["3"]') OR JSON_CONTAINS(template_ids, '["48"]') ) 

Which goal entries contain any of these identifiers in the template_ids JSON field.

You may be interested in reading the relevant Laravel sentence.

Answer 2

What to say:

 $query = Post::where([]); $query->where(function($query, $template_ids){ foreach ($template_ids as $id){ $query->orWhere('searching_field', 'like', '%'.$id.'%'); } }); $query->get()