So in Laravel 5 there's the handy thing called JSON Where Clauses using MySQL's new ability to store and fetch JSON stored in a column:
User::where('meta->colors', 'red')->get()
would return all rows, where colors in the column meta would be set to red.
现在假设colors不是一个字符串，而是一个包含多种 colored颜色 的数组(colors => ['red', 'blue', 'green']).
What would be an efficient way to retrieve all rows, where colors contains e.g. the value red?
JSON_CONTAINS()正是你想要的:
JSON_CONTAINS(target, candidate[, path])Indicates by returning 1 or 0 whether a given candidate JSON document is contained within a target JSON document, or—if a path argument was supplied—whether the candidate is found at a specific path within the target. — 12.16.3 Functions That Search JSON Values
Currently, Laravel's query builder does not provide a corresponding API. There's an open internals proposal for it though.
In the meantime, you can execute a raw query:
\DB::table('users')->whereRaw(
'JSON_CONTAINS(meta->"$.colors", \'["red"]\')'
)->get();
Which would return all users that have "red" in their meta->colors JSON field. Note that the -> operator requires MySQL 5.7.9+.
You can also call the whereRaw() directly on an Eloquent model.
从5.6版本开始，Laravel的查询生成器包含一个新的whereJsonContains方法.