[FEATURE]: Add support for JSON columns

[maxrice]

In our project we're using JSON columns for custom metadata. After adding the column in mySQL and adding it to the $casts property (e.g. $casts = [ 'metadata' => 'json' ]), any attempt to query that column (like $builder->where( 'metadata->item', 'test' )), results in: Uncaught RuntimeException: This database engine does not support JSON contains operations..

It looks like the WordPressGrammar class doesn't have any support for JSON but I see a note there to Extend from MySqlGrammar next major version. Something to add to v4 perhaps?

One possible issue is that WP supports both mySQL and SQLite, not sure if the related Eloquent grammar could be conditionally extended from after detecting what WP is running.

[dimitriBouteille]

Hi @maxrice

Thanks you for opening this issue. WordPressGrammar is created to fix this issue https://github.com/dimitriBouteille/wp-orm/issues/66, this fix is temporary for 3.x.

As indicated in the PHPDoc, this class will be modified to extend from MySqlGrammar. This correction is planned for the v4.

Before making this fix, I will work on the database tests to minimize the risk of regression.

[maxrice]

@dimitriBouteille thank you. I did test pulling in the related JSON functions from the MySQL grammar but Eloquent was double-quoting the JSON attribute name when using the standard $builder->where( 'some_json_column->some_attribute', 'some_value' ) syntax, so for our project I've just reverted to using whereRaw() for the moment.

[dimitriBouteille]

Hi @maxrice

The issue is now fixed and the fix will be available in next major release (v4). If you want, you can test the fix in the alpha v4.0.0-alpha.1:

composer require dbout/wp-orm v4.0.0-alpha.1

As you say, Eloquent add double-quoting in the JSON attribute condition, I don't know why. I have deleted this double quote, you can find more details in this pull request: https://github.com/dimitriBouteille/wp-orm/pull/93

I let you re-open the issue if there is still a bug :)

Best

[maxrice]

@dimitriBouteille wizard, thanks so much. I'll give the alpha a try.