使用 Laravel 的 eloquent 在 JSON 列中搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44669673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search in JSON column using Laravel's eloquent
提问by Faran Khan
I've been working with Laravel 5.4's eloquent and I've encountered a problem.
我一直在使用 Laravel 5.4 的 eloquent，遇到了一个问题。
I have a database table called postsand in that, a column named as template_ids. It stores the values in json_encodedformat like:
我有一个名为posts的数据库表，其中有一列名为template_ids. 它以如下json_encoded格式存储值：
["1","25","48"]
Now, I want to apply a filter to my query based on an array of IDs:
现在，我想根据一组 ID 将过滤器应用于我的查询：
$id_access = array:3 [
0 => "1"
1 => "3"
2 => "48"
]
What I am trying to do is to search if any of $id_accessvalues is present in the database column, template_ids.
我想要做的是搜索$id_access数据库列中是否存在任何值，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.
已经看过这个，但它对我不起作用。
回答by sepehr
To see if the template_idsJSON field contains "any" of the values in a needle array, you gonna need to utilize multiple OR'd JSON_CONTAINSconditions which requires MySQL 5.7:
要查看template_idsJSON 字段是否包含针数组中的“任何”值，您将需要利用需要 MySQL 5.7 的多个OR'dJSON_CONTAINS条件：
$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:
Laravel 的查询构建器将生成如下查询：
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_idsJSON-typed field.
其目标记录在其template_idsJSON 类型的字段中具有任何这些 ID 。
You might be interested in reading a related Laravel internals proposal.
您可能有兴趣阅读相关的 Laravel内部提案。
回答by Liuver Reynier Durán Pérez
What about to say:
想说什么：
$query = Post::where([]);
$query->where(function($query, $template_ids){
foreach ($template_ids as $id){
$query->orWhere('searching_field', 'like', '%'.$id.'%');
}
});
$query->get()
