使用Laravel的口才在JSON列中搜索 [英] Search in JSON column using Laravel's eloquent
问题描述
我一直在使用Laravel 5.4的口才,但是遇到了问题.
I've been working with Laravel 5.4's eloquent and I've encountered a problem.
我有一个名为 posts 的数据库表,其中有一个名为template_ids
的列.它以json_encoded
格式存储值,例如:
I have a database table called posts and in that, a column named as template_ids
. It stores the values in json_encoded
format like:
["1","25","48"]
现在,我想基于ID数组将过滤器应用于查询:
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"
]
我想做的是搜索数据库列template_ids
中是否存在任何$id_access
值.
What I am trying to do is to search if any of $id_access
values is present in the database column, template_ids
.
我尝试过:
Post::where('accessable_to',1)->whereIn('template_ids', $template_access_array)->paginate(3);
我也尝试过:
Post::where('accessable_to',1)->whereRaw("JSON_CONTAINS(template_ids, ".$template_access.")")->paginate(3);
已经查看过此,但是它对我不起作用.
Already viewed this, but it's not working for me.
推荐答案
要查看template_ids
JSON字段是否在针阵列中包含任意"值,则需要利用多个OR
d JSON_CONTAINS
条件MySQL 5.7:
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的查询生成器将生成如下查询:
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"]')
)
哪些目标记录在其template_ids
JSON类型的字段中具有这些ID中的任何一个.
Which targets records that have any of those IDs in their template_ids
JSON-typed field.
您可能有兴趣阅读相关的Laravel 内部提案.
You might be interested in reading a related Laravel internals proposal.
这篇关于使用Laravel的口才在JSON列中搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!