archipro / silverstripe-db-json
A Silverstripe CMS module to enable the creation of MySQL 8 JSON column on DataObjects
Installs: 210
Dependents: 0
Suggesters: 0
Security: 0
Stars: 1
Watchers: 2
Forks: 0
Open Issues: 0
Type:silverstripe-vendormodule
Requires
- silverstripe/framework: ^5.0||^4.13
Requires (Dev)
README
MySQL 8 introduced support for native JSON fields. This module adds support for creating and using those fields in Silverstripe CMS.
Installation
composer require archipro/silverstripe-db-json
Usage
Simply reference the DBJson
class in your DataObject's $db
array. This will automatically add a json
field to the database table.
<?php use SilverStripe\ORM\DataObject; use ArchiPro\Silverstripe\DbJson\DBJson; class MyDataObject extends DataObject { private static $table_name = 'MyDataObject'; private static $db = [ 'Payload' => DBJson::class, ]; }
This will create a table with this definition:
CREATE TABLE `MyDataObject` ( `ID` int NOT NULL AUTO_INCREMENT, `ClassName` enum('MyDataObject') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT 'MyDataObject', `LastEdited` datetime DEFAULT NULL, `Created` datetime DEFAULT NULL, `Payload` json NOT NULL, PRIMARY KEY (`ID`), KEY `ClassName` (`ClassName`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Accessing JSON fields
The module automatically adds accessor and mutator methods for all DBJSON fields.
This allows you to assign array values to your JSON fields. Those values will automatically be encoded as a JSON string on the DataObject's record.
When you read the value back from the DataObject, the JSON string is decoded back into an array.
<?php $myDataObject = MyDataObject::create(); $myDataObject->Payload = [ 'key' => 'value', ]; $myDataObject->write(); var_dump($myDataObject->Payload); // array(1) { // ["key"]=> string(5) "value" // }
Filtering results
This module ships with a Json
filter you can use search your JSON column. Internally the filter will use the JSON_CONTAINS
function.
$one = MyDataObject::create(); $myDataObject->Payload = [ 'meta' => [ 'Title' => 'My page', 'Created' => '2024-01-01', 'Author' => 'John Doe', ] ]; $myDataObject->write(); // ... // This will return all objects where the `Author` field in the `meta` array is `John Doe` $list = MyDataObject::get()->filter('Payload:Json', ['$.meta.Author' => 'John Doe']); // This will return all objects where the `meta` array contains the `Title` and `Created` fields with the provided values $list = MyDataObject::get()->filter( 'Payload:Json', ['$.meta' => ['Title' => 'My page', 'Created' => '2024-01-01']] );
You can also use the not
modifier to exclude results.
$list = MyDataObject::get()->filter('Payload:Json:not', ['$.meta.Author' => 'John Doe']);
MySQL 8 provides many native functions to query JSON data. You can use the where
method if you want to use those functions directly.
// The following query will extract the price from the Payload column and only // return records with a price greater than 4 $list = MyDataObject::get()->where([ 'JSON_EXTRACT("Payload", ?) > ?' => ['$.price', 4], ]);
Gotchas
Your are using a custom MySQLSchemaManager
Your MySQLSchemaManager must have a json
method so it it knows how to create json
columns.
This extension will automatically replace the native MySQLSchemaManager with its own MySQL8SchemaManager
.
If you've already defined a custom MySQLSchemaManager
:
- add the
ArchiPro\Silverstripe\DbJson\JsonDatabaseFieldDefinition
trait to your class. - Make sure your custom
MySQLSchemaManager
is loaded after the one provided by this extension.
--- Name: my-custom-db-schema-manager after: name: '#maxime-rainville-silverstripe-db-json' --- SilverStripe\Core\Injector\Injector: MySQLSchemaManager: class: App\Project\MyCustomMySQLSchemaManager