ThinkPHP: Usage of JSON field type (ORM)

ThinkPHP 5.1 has been officially released for some time, and I will introduce its new features to you one after another. What I want to introduce to you today is a feature that many users may not understand yet: JSON field data support.

But first of all, please note that the support for JSON field data described in this article was introduced from version V5.1.4. It is recommended to make sure to use version 5.1.9 due to the inclusion of security updates.

The definition of JSON fields in this article includes JSON types or character types where the saved data is in JSON format. Therefore, in theory, there are no requirements for database type and version except using JSON field conditional queries.

Db class operates JSON

If you do not use the model class, the Db class provides a json method to specify your data Table JSON format fields. For example, your user table has an info field of JSON type. You can use the following method to manipulate the data.

Data writing

$user['name'] = 'thinkphp';
$user['info'] = [
'email'    => 'thinkphp@qq.com',
'nickname' => '流年',
];
Db::name('user')
->json(['info'])
->insert($user);

The parameter of the json method is an array. The info field is specified in the example. In fact, multiple JSON type fields can be specified.

Data query

Query the entire JSON data.

$user = Db::name('user')
->json(['info'])
->find(1);
dump($user);

The returned query result data will automatically include an array type of info data, which means that the JSON format data has been automatically processed by json_decode.

This method of querying does not strictly require the use of JSON type for the info field

If you need to query based on the value of JSON data, you can use the following method

$user = Db::name('user')
->json(['info'])
    ->where('info->nickname','ThinkPHP')
->find();
dump($user);

The info field must be of JSON type, and MySQL requires version 5.7 to support it

Of course, it can also support multi-level

$user = Db::name('user')
->json(['info'])
    ->where('info->profile->nickname','ThinkPHP')
->find();
dump($user);

Since the attribute type of the JSON field is not It will be obtained automatically, so if it is an integer data query, manual parameter binding is required, for example:

$user = Db::name('user')
->json(['info'])
    ->where('info->user_id', ':user_id')
    ->bind(['user_id' => [10, \PDO::PARAM_INT]])
->find();
dump($user);

Data update

Complete JSON data update

$data['info'] = [
'email'    => 'kancloud@qq.com',
'nickname' => 'kancloud',
];
Db::name('user')
->json(['info'])
    ->where('id',1)
->update($data);

This query does not strictly require the use of JSON type for the info field

If you only update a certain value in the JSON data, you can use the following method:

$data['info->nickname'] = 'ThinkPHP';
Db::name('user')
->json(['info'])
    ->where('id',1)
->update($data);

It is also required that the info field must be of JSON type

Model operation JSON data

If you are using If the model operates on the database, JSON data operations will be even simpler.

We only need to add a json attribute definition to the User model class.

<?php
namespace app\index\model;
use think\Model;
class User extends Model
{
// 设置json类型字段
protected $json = [&#39;info&#39;];
}

The json attribute also supports defining multiple field names. After definition, the following JSON data operations can be performed.

Write data

Use array method to write JSON data:

$user = new User;
$user->name = 'thinkphp';
$user->info = [
'email'    => 'thinkphp@qq.com',
    'nickname '=> '流年',
];
$user->save();

Use object method to write JSON data

$user = new User;
$user->name = 'thinkphp';
$info = new StdClass();
$info->email = 'thinkphp@qq.com';
$info->nickname = '流年';
$user->info = $info;
$user->save();

Query data

The result type is different from the Db class query. The JSON field of the model will be automatically converted into an object.

$user = User::get(1);
echo $user->name; // thinkphp
echo $user->info->email; // thinkphp@qq.com
echo $user->info->nickname; // 流年

It can also support querying JSON field data

$user = User::where('info->nickname','流年')->find();
echo $user->name; // thinkphp
echo $user->info->email; // thinkphp@qq.com
echo $user->info->nickname; // 流年

Same as Db class query, if the JSON attribute you need to query is an integer type, manual parameter binding is required.

$user = User::where('info->user_id',':user_id')
->bind(['user_id' => [10 ,\PDO::PARAM_INT]])
->find();
echo $user->name; // thinkphp
echo $user->info->email; // thinkphp@qq.com
echo $user->info->nickname; // 流年

If you are using version V5.1.11, you can define the attribute type of the JSON field in the model class, and the corresponding type of parameter binding query will be automatically performed.

<?php
namespace app\index\model;
use think\Model;
class User extends Model
{
// 设置json类型字段
protected $json = [&#39;info&#39;];
    
    // 设置JSON字段的类型
    protected $jsonType = [
    &#39;user_id&#39;=>&#39;int&#39;
    ];
}

Attributes without a defined type default to string type, so string type attributes do not need to be defined.

Update data

Updating JSON data also uses objects

$user = User::get(1);
$user->name = 'kancloud';
$user->info->email = 'kancloud@qq.com';
$user->info->nickname = 'kancloud';
$user->save();

If you need to do more complex operations on JSON type fields, you can also Completed by exp expression. This is waiting for everyone to discover more JSON usage.

PHP Chinese website has a large number of free ThinkPHP introductory tutorials, everyone is welcome to learn!

This article is reproduced from: https://blog.thinkphp.cn/784281

The above is the detailed content of ThinkPHP: Usage of JSON field type (ORM). For more information, please follow other related articles on the PHP Chinese website!