Download the PHP package liqueurdetoile/cakephp-orm-json without Composer

Cake-orm-json plugin

This branch is for CakePHP ^3.7|^4.0 and supports PHP ^7.2|^8.0 For previous CakePHP versions, please use v1 of this plugin

This plugin extends usual CakePHP ORM operations with JSON fields. It embeds a special datfield notation that allow to easily target a path into a JSON field data. With it, you can :

• select, order and filter queries : $q = $table->find('json')->where(['jfield->darn.deep.key' => true])->all()
• apply data types inside JSON data
• easily access, mutate and delete JSON data in entity : $e->get('jfield->darn.deep.key')
• use JSON data as foreign keys for associations (quite extreme indeed and not really recommended but it can be useful at margin)

Relational databases are not primarily designed to handle non-schemed data and using JSON data fields can issue really bad performances. Nevertheless the newest releases of engines have also shown significant improvements in dealing with JSON data and raising of NoSQL has created different needs and constraints.

Caution : As with version 2.0.0, it only works with Mysql databases >= 5.7.8. Setup is done to allow adding other engines to this plugin and I hope to release it at least for MariaDB and SQLite, maybe PostgreSQL. Any help would be very appreciated though :smile

• Cake-orm-json plugin
  • Installation
    • Install plugin
    • Recommended setup
      • Embeds DatFieldAwareTrait in models
      • Embeds DatFieldBehavior in models
      • Enbeds DatFieldTrait with entities
  • Datfield format
  • Usage
    • Quick guide
    • Selecting fields
    • Filtering and ordering data
    • Using aggregation and functions
    • Marshaling data
    • What brings DatFieldTrait within entities ?
      • For PHPStan users
    • Using JSON data types
    • Using JSON data types
      • Registering JSON data type permanently
      • Registering JSON data types for a single query
    • Linking models together
      • Limitations with CakePHP 3.x
  • Advanced setup
    • Use the upgraded driver for all models
    • Enable or disable upgraded driver per model
    • Use upgraded driver per query
    • Some tricky things to know
      • API reference
  • Difference from v1.x
  • Changelog
  • Disclaimer

Installation

Install plugin

You can install the latest version of this plugin into your CakePHP application using composer.

The base namespace of the plugin is Lqdt\OrmJson.

Important : If you plan to use this plugin with Cakephp 3.x, you must enable compatibility mode by adding this line to your config/bootstrap.php in order to setup required classes alias from Cakephp 4.x :

Recommended setup

This plugin is working by cloning the used connection in order to upgrade its driver and insert a translation step that will allow to parse datfield notation into a suitable form that can then be used by cakePHP ORM. Obviously, adding this layer if not using datfield notation is pretty useless though resource consuming.

There's many ways to setup the plugin in order to optimize things but we recommend this one as it will fit most of use cases :

• Add DatFieldBehavior that have JSON fields without upgrading table connection, and add DatFieldTrait to their corresponding entities;
• Add DatFieldAwareTrait to models without JSON fields but which uses associations relying on datfield foreign keys;
• Always call find('datfields') or find('json') when querying if using datfield notation to ensure that translation is correctly enabled.

Keep in my mind that you keep full control on using regular or upgraded connection. If you have some performance issues with this setup, please check advanced setup for more informations.

Embeds DatFieldAwareTrait in models

Usually, you will use this trait in models that needs to be linked to another model with a foreign key living in JSON data. The trait allows you to link models based on datfield foreign key(s) and to easily switch between regular or upgraded connection.

Embeds DatFieldBehavior in models

Behavior brings up all of the convenience of DatFieldAwareTrait and takes care of marshaling datfield notation when creating/patching entities. The behavior is targetted to models which contains JSON fields. It can also be used to store permanent JSON data types when marshaling or persisting data.

You can pass ['upgrade' => true] as behavior config options to request an immediate connection upgrade for the model.

The behavior can be used without DatFieldTrait in entities and vice-versa.

Enbeds DatFieldTrait with entities

Datfield trait brings up tools to access and manipulate with ease the content of JSON fields. Obviously, it's only useful with entities that contain JSON fieds.

The trait can be used without DatFieldAwareTrait or DatFieldBehavior in models and vice-versa.

Datfield format

In order to work with inner JSON data, we need to know which field to use and which path to use in this field. You can, obviously use SQL fragments and/or native Mysql JSON functions but, believe me, it's very prone to error, needs securing user input, and, well, what about using an ORM if we have to write raw SQL each time ?

This plugin solves this difficulty by providing quick syntax to target JSON data. In fact, this version brings up two ways and you can choose or mix which one will best suit your way.

This plugin introduced the datfield format (contraction of dot and at field) whick looks like : tt>path@[Model.]fieldModel part is optional if no name conflict may occurs.

Since v2, this plugin also supports a more object way which looks like : [Model.]field->path

For instance, let's say you have a JSON field named position, exposing two keys lat and lon in a Locations model.

For query operations or with special entity getters/setters, You may consider using 'lat@position' or 'position->lat' to easily manipulate and access the lat data in the position field.

Datfields become especially handy when accessing deep nested keys : 'lastknown.computed.lat@position' (or 'position->lastknown.computed.lat') will target 'position['lastknown']['computed']['lat']' value in data.

It also partially supports JSON path with (as now) the syntax used by Mysql itself to target arrays : [Model.]field->path[*].prop will target the prop key of all items stored in the array at path.

Usage

Quick guide

DatField notation can be used in any statements involving fields :

In short : just target and use JSON data as you would do with regular fields by using datfield notation. If you know some troubles, feel free to open an issue as needed.

Selecting fields

You can easily select specific paths in your data among paths with a regular select statement. Without alias provided, it will create a composite key from datfield <model if provided>_<field>_<path with dot replaced by underscore> :

You can also use field alias to control key in result :

enableAutoFields will work fine to expose some data while loading all other fields :

Filtering and ordering data

Filtering or ordering with datfields can be done like with any other fields and by any usual means of the ORM. Expressions will be automatically translated to usable ones in SQL. You can use datfields at any place.

Note : When filtering on null values, default behavior is to consider that any record that don't have the target path in its JSON field also fulfills IS NULL condition. To avoid this, you can pass ['ignoreMissingPath' => true] as query option to target only records that have the path in their JSON field with a value set to null.

There's some caveats with data types not natively supported by JSON format, like datetime, but it can be handled by using JSON data types.

Using aggregation and functions

Datfield are also fully supported and can be used as any regular fields.

Marshaling data

Note : Marshaling datfields does not require to upgrade connection

In some cases, you may want to use datfield notation in data provided to createEntity or patchEntity methods and there's no trouble in doing so :

When patching entities, the default behavior is to consider that the whole JSON structure is provided in data. Therefore, all previous data is lost and gone. To avoid this, you can either pass jsonMerge as true in patchEntity options or call jsonMerge on the resulting entity (if using DatFieldTrait) or through table :

You can fine tune which field(s) should be merged by passing an array of the JSON fields name to jsonMerge option or method : ['data'] for instance.

What brings DatFieldTrait within entities ?

Note : Using this trait does not require to upgrade connection nor adding DatFieldAwareTrait or DatFieldBehavior to model.

All regular methods are replaced when an entity uses this trait to support datfield notation. Their behavior remains the same and they still can be used for any regular field.

To get/set data with datfield, simply use get or set, array-like syntax or curly syntax :

Dirty state is available at path level and field level :

Note : If you call setDirty('attributes', false), all currently dirty paths inattributes will be cleared as well.

For PHPStan users

If you're using PHPStan and curly syntax to access your data, you will obviously have errors about accessing undefined properties on entities. To cope with these, this plugin provides a service to check if owning field exists in entity class and if it is typed as an array. To enable the service, simply add this snippet in your phpstan.neon.dist or whatsoever configuration file :

Using JSON data types

There's some caveats when dealing with data types inside JSON. By itself JSON type handles natively null and usual scalar types : boolean, integer, float or string, plus arrays and objects of previous. Troubles may begin when you want to handle other types stored in JSON and the perfect example is datetime.

Usually, datetime/time/date/timestamp fields are mapped to a FrozenTime object in cakePHP and a registered type takes care of handling needed castings. Most of the time, this type is inferred from reflected schema and it's working out of the box.

If a datetime is nested in some JSON data, it can't work like this as it is merely a string. When dealing with some usual string representations of datetimes, like Mysql one, ISO8601 or timestamps, it can be absolutely fine to simply do nothing as ordering will work. You only have to take care to pass the right string format when saving data.

Nevertheless, you miss all the convenience that brings datetime data type for manipulating values. Moreover, if you have some nasty formats, queries may lead to wrong results. Due to JSON versatility, many APIs make use of custom string formats and it can be tricky to handle them.

To ease troubleshooting these things, DatFieldBehaviorallow to define JSON data types permanently and/or per query. Because of JSON versatility, it extends regular typemaps by allowing the use of callbacks to cast data instead of multiplicating data types.

Using JSON data types

Note : Connection must be upgraded in order to support JSON data types.

JSON data types are stored within an upgraded schema alongside regular fields that is created when upgrading connection. Therefore you will get errors if upgrade is not done before setting them up.

When registering JSON data type, you can either only provide a regular data type as string or an extended one to register callbacks for one or more of casting operations between :

• marshal: Callback will be called when marshaling data.
• toPHP: Callback will be called when processing fetched data
• toDatabase: Callback will be called when persisting data

All callbacks will receive the target value as first argument, the whole row data as second argument and the query (if available in operation) as third argument.

If a callback is provided for a given operation (marshal, toPHP or toDatabase) alongside a regular data type, only callback will be applied to data. This way, you can override given data type operations instead of creating a new one.

Registering JSON data type permanently

When using DatfieldBehavior, you can easily and permanently register JSON types that will persist through each queries. :

Please note that all JSON data types will be lost if connection is downgraded as regular schema will be restored.

Registering JSON data types for a single query

You can also register JSON data type per query by providing a jsonTypeMap option. In case of conflict, it overrides any JSON data type stored in the model.

You can as well provide callbacks by using full syntax.

Linking models together

Special upgraded associations are available both in DatFieldAwareTrait and DatFieldBehavior

The plugin allows to use datfield notation to reference a foreignKey and links tables on this basis. It will not be as efficient as regular foreign keys that will indexed but it can be handy in some edge cases.

In order to use datfield as foreign key, simply use datfield counterpart of any association and use dafield notation for foreign key option and/or targetFoeignKey option :

All others options and functionnalities remains the same.

The counterparts list :

• belongsTo <=> datFieldBelongsToMany
• hasOne <=> datFieldHasOne
• hasMany <=> datFieldHasMany
• belongsToMany <=> datFieldBelongsToMany

Note : No need to say that connection must be upgraded for these queries to work.

In MySQL, you may use virtual columns to index JSON data as a more efficient solution.

Limitations with CakePHP 3.x

The only limitation is that you cannot use link, unlink or save associated data when models are joined by datFieldBelongsToMany or datFieldBelongsToManyThrough. This is due to an heavy refactoring of how it is handled by CakePHP since version 4 release and there's now way to handle both with this plugin.

Advanced setup

This plugin contains :

• Lqdt\OrmJson\Database\Driver\DatFieldMysql driver: The driver will traverse all query parts in order to translate datFields in clauses to their MySQL counterpart, usually JSON_EXTRACT. Traversal can be disabled at runtime by providing [useDatFields => false] in query options;
• Lqdt\OrmJson\ORM\DatFieldAwareTrait: The trait is providing convenient methods to upgrade/downgrade connection driver and table schema at will and brings up special associations to allow linking models on JSON data;
• Lqdt\OrmJson\Model\Table\DatFieldBehavior: The behavior does exactly the same thing that DatFieldAwareTrait, plus handling marshalling with datfields when using newEntity, patchEntity or their plural counterparts;
• Lqdt\OrmJson\Model\Entity\DatFieldTrait: The trait overrides all regular accessors, mutators and utilities to handle datfield notation within entities while keeping full compatibility for regular fields.

Depending on what you're aiming for, you have different alternatives when using this plugin.

• Use datfield notation and/or JSON data types to query database or persist data : You must ensure that the model(s) that will rely on datfield notation for querying are using the upgraded connection;
• Use datfield notation when patching data : You must have embedded DatFieldBehavior in the model;
• Use datfield notation to manipulate data in entities: You must have added DatFieldTrait to related entities classes;
• Use datfield notation to link models : You must use the special associations methods provided by DatFieldAwareTrait or DatFieldBehavior;

It's up to you to find the right balance based on your needs between the connection upgrade step overhead and the datfield translation step overhead.

Use the upgraded driver for all models

Obviously, you can simply use upgraded driver in your connection configuration. This can be a real good option if all of your models will mostly use datfield notation. You can still disable datfield translation by providing ['useDatFields' => false] as query option to avoid useless translation process when not using datfields.

Enable or disable upgraded driver per model

With addition of DatFieldAwareTrait or DatFieldBehavior to a model, you can enable/disable upgraded connection at runtime by using Model::useDatFields()/Model::useDatFields(false). If you want to permanently use upgraded connection in the model, simply call Model::useDatFields() in the initialize hook or add the behavior with ['upgrade' => true] as option. You can still disable datfield translation per query by providing ['useDatFields' => false] as query option.

Caution : As model instances are stored as singleton in a registry, I do recommend to cut off upgraded driver after all datfield queries are settled.

Use upgraded driver per query

It's probably the most usual case as datfield queries will mostly be occasional. With addition of DatFieldAwareTrait or DatFieldBehavior to a model, simply call find('datfields') or find('json') and the query will be provided with an upgraded connection though model connection remains genuine. You cannot use permanent JSON data types this way but still can provide jsonTypeMap option in query.

Some tricky things to know

Lastly, you may face some issues with nested queries, when joining data. If doing a single query, CakePHP will logically populate query connection with the driver of the root model. In contrary, when launching subqueries, connection configuration of dependent models wii be used.

For instance, say you have a Vehicles model that has many Locations model. Upgraded driver is permanently used in Locations but not in Vehicles.

If you begin to have Mysql syntax errors with unparsed datfields, it means that you have some datfields used in a not upgraded connection.

API reference

See API reference

Difference from v1.x

In previous versions, we've tried to convert clauses within Query by dedicating the JsonQuery that extends it to bring up functionnalities. It worked very well but it was still limited to Query overrides and support for other engines was clearly impossible.

From version 2.0.0, translation is done at MySQL driver level. The behavior now creates an upgraded connection with the new driver that is able to translate any datfield notation before native CakePHP ORM processing.

CakePHP makers are great guys because they meant to plan many overrides that makes this plugin feasible.

Version 2.x is a quite a breaking change from 1.x as JsonQuery is nor needed nor available anymore. Similarly, you don't need any jsonXXX methods on entities. Regular mutators, accessors and magic properties will work well with datfields.

Migrating is quite simple though, simply stick to regular query statements and use select, order, where instead of previous ones jsonSelect, jsonOrder, jsonWhere. In entities, use regular accessors and mutators to cope with data in JSON.

Changelog

v2.0.0

• BREAKING CHANGE : Replace JsonQuery logic by a dedicated database driver that handles seamlessly the parsing of dat fields
• BREAKING CHANGE : Replace JsonXXX entity methods and use regular accessors and mutators
• Add v2 datfield notation support '[Model.]field->path'
• Completely rework and optimize query translations of datfield syntax
• Fully rework DatFieldBehavior and DatFieldTrait for entities
• Migrate CI to Github Actions
• Upgrade test environment
• Add a bunch of tests for a wide variety of situations

v1.5.0

• Full rework of jsonWhere to replace previous conditions array parsing by a full QueryExpression build that allows the use of query expressions callbacks

v1.4.0

• Add support to optionally fetch back an associative array instead having flattened keys when selecting statements

v1.3.0

• Add support for dot seperator and dotted aliases in select operations
• Add support for sorting on datfield value
• Add support to accept regular database fields into json specific select, where and order statements

v1.2.0

• Add support for aliases in jsonSelect and json.fields option through associative arrays

v1.1.0

• Add support for newEntity and patchEntity through a beforeMarshal event and jsonmerge

v1.0.0

• Add Lqdt\OrmJson\ORM\JsonQuery to support basic formatting of fields names and conditions
• Add Lqdt\OrmJson\Model\Behavior\JsonBehavior to enhance tables with JSON cool stuff
• Add Lqdt\OrmJson\Model\Entity\JsonTrait to enhance entities with JSON cool stuff
• Only supports Mysql

Disclaimer

By this time, the plugin only translates datfield notation to a suitable format to perform Mysql queries using CakePHP ORM.

The Mysql way of querying cannot be used as is in other RDBMS. However, the logic can be ported to other systems, especially those working with TEXT.

This plugin exclusively relies on Mysql JSON_EXTRACT to perform finds. Other JSON functions are not implemented but can be useful (see Mysql reference).