Search in JSON column using Laravel's eloquent
To see if the template_ids
JSON field contains "any" of the values in a needle array, you gonna need to utilize multiple OR
'd JSON_CONTAINS
conditions 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);
Laravel's query builder will produce 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 targets records that have any of those IDs in their template_ids
JSON-typed field.
You might be interested in reading a related Laravel internals proposal.
Faran Khan
Updated on June 18, 2022Comments
-
Faran Khan almost 2 years
I've been working with Laravel 5.4's eloquent and I've encountered a problem.
I have a database table called posts and in that, a column named as
template_ids
. It stores the values injson_encoded
format like:["1","25","48"]
Now, I want to apply a filter to my query based on an array of IDs:
$id_access = array:3 [ 0 => "1" 1 => "3" 2 => "48" ]
What I am trying to do is to search if any of
$id_access
values is present in the database column,template_ids
.I tried:
Post::where('accessable_to',1)->whereIn('template_ids', $template_access_array)->paginate(3);
Also, I tried:
Post::where('accessable_to',1)->whereRaw("JSON_CONTAINS(template_ids, ".$template_access.")")->paginate(3);
Already viewed this, but it's not working for me.
-
Saroj Shrestha over 3 yearsIs it possible to remove one
template_ids
in the above case on bulk? That means remove'1'
from all rows if there is1
in templates_ids columns -
sepehr over 3 years@SarojShrestha This answer might be helpful: Remove array element by value in mysql json