Laravel / Laravel 6 / Laravel Eloquent ORM / MySQL / MySQL 5.7 / Web应用开发 / Web框架 / 数据库
0

在 Laravel 6 中，使用 whereJsonContains 查询 json 类型字段中的数据(结构为数组)

由永夜 · 2022/12/27

1、在 MySQL 5.7 中，json 类型字段中的数据是数组，其值为：[365]。如图1

在 MySQL 5.7 中，json 类型字段中的数据是数组，其值为：[365]

图1

2、参考：查询构造器 – Where 语句 – JSON Where 语句：https://learnku.com/docs/laravel/6.x/queries/5171#35d9d9 。可以使用 whereJsonContains 来查询 JSON 数组。如图2

参考：查询构造器 - Where 语句 - JSON Where 语句：https://learnku.com/docs/laravel/6.x/queries/5171#35d9d9 。可以使用 whereJsonContains 来查询 JSON 数组

图2

3、代码实现如下

$themeSaasTaskId = 365;
$themeSaasTasks = ThemeSaasTask::whereJsonContains('theme_task_ids', $themeSaasTaskId)->get();
print_r($themeSaasTasks);
exit;

4、打印查询结果如下，符合预期

Illuminate\Database\Eloquent\Collection Object
(
    [items:protected] => Array
        (
            [0] => Modules\ThemeStoreDB\Entities\ThemeSaasTask Object
                (
                    [table:protected] => theme_saas_task
                    [attributes:protected] => Array
                        (
                            [id] => 2
                            [type] => update_theme
                            [theme_task_ids] => [365]
                            [created_at] => 2022-12-21 08:06:49
                            [updated_at] => 2022-12-21 08:06:51
                        )
 
                    [fillable:protected] => Array
                        (
                        )
 
                    [connection:protected] => mysql
                    [primaryKey:protected] => id
                    [keyType:protected] => int
                    [incrementing] => 1
                    [with:protected] => Array
                        (
                        )
 
                    [withCount:protected] => Array
                        (
                        )
 
                    [perPage:protected] => 15
                    [exists] => 1
                    [wasRecentlyCreated] => 
                    [original:protected] => Array
                        (
                            [id] => 2
                            [type] => update_theme
                            [theme_task_ids] => [365]
                            [created_at] => 2022-12-21 08:06:49
                            [updated_at] => 2022-12-21 08:06:51
                        )
 
                    [changes:protected] => Array
                        (
                        )
 
                    [casts:protected] => Array
                        (
                        )
 
                    [dates:protected] => Array
                        (
                        )
 
                    [dateFormat:protected] => 
                    [appends:protected] => Array
                        (
                        )
 
                    [dispatchesEvents:protected] => Array
                        (
                        )
 
                    [observables:protected] => Array
                        (
                        )
 
                    [relations:protected] => Array
                        (
                        )
 
                    [touches:protected] => Array
                        (
                        )
 
                    [timestamps] => 1
                    [hidden:protected] => Array
                        (
                        )
 
                    [visible:protected] => Array
                        (
                        )
 
                    [guarded:protected] => Array
                        (
                            [0] => *
                        )
 
                )
 
        )
 
)

5、生成的 SQL 如下，如图3

生成的 SQL 如下

图3

1	`select` `*` `from` `theme_saas_task` `where` json_contains(`theme_task_ids`, `'365')`

Share

标签： Eloquent\Collection json JSON Where 语句 json_contains MySQL 5.7 Where 语句 whereJsonContains 查询构造器

您可能还喜欢...

发表回复取消回复

要发表评论，您必须先登录。

这个站点使用 Akismet 来减少垃圾评论。了解你的评论数据如何被处理。