使用Laravel的口才在JSON专栏中搜索

I've been working with Laravel 5.4's eloquent and I've encountered a problem.

我一直在使用Laravel 5.4的雄辩，我遇到了一个问题。

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:

我有一个名为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_access values is present in the database column, template_ids.

我想要做的是搜索数据库列template_ids中是否存在任何$ id_access值。

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.

已经看过这个，但它不适合我。

2 个解决方案

#1

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:

要查看template_ids JSON字段是否包含针数组中的“任何”值，您需要使用需要MySQL 5.7的多个OR'd JSON_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_ids JSON-typed field.

其目标是在其template_ids JSON类型字段中具有任何这些ID的记录。

You might be interested in reading a related Laravel internals proposal.

您可能有兴趣阅读相关的Laravel内部提议。

#2

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()

#1