JSON column compatibility between MySQL and MariaDB

[mepoohsta]

My code

        $company = Company::find(1);
        var_dump($company->size);

Code where error. \vendor\rinvex\attributes\src\Traits\Attributable.php

static::$entityAttributes->put($morphClass, app('rinvex.attributes.attribute')->whereIn('id', $attributes)->orderBy('sort_order', 'ASC')->orderBy("name->\${$locale}", 'ASC')->get()->keyBy('slug'));

[andrewnk]

I had the same error. Since I don't need to worry about translations, I just had to override the method and change orderBy("name->\${$locale}", 'ASC') to orderBy("name", 'ASC').

The new line is:

static::$entityAttributes->put($morphClass, app('rinvex.attributes.attribute')->whereIn('id', $attributes)->orderBy('sort_order', 'ASC')->orderBy("name", 'ASC')->get()->keyBy('slug'));

[mepoohsta]

@andrewnk Maybe you can help me with #38, please?

[julienmonty]

Hi everyone,

Same error here (fresh Laravel 5.6 install). Not sure if overriding a method is a good solution as it happens with a fresh install, just by following the docs. And it removes the translation feature...

Is this a 5.6 compatibily issue ?

I'll try with the dev branch to see if it's the same.

EDIT : I'm using mariaDB, i'll also check if it happens with MySQL as well. EDIT 2 : working fine with SQLite EDIT 3 : same error with MySQL EDIT 4 : my bad, it's working with MySQL, so this problem really seems MariaDB related... I could not find a working syntax for now... @mepoohsta Could you please reopen this issue ?

[IsraelOrtuno]

@julienmonty could you specify which version of MariaDB you are using?

[IsraelOrtuno]

@andrewnk could you specify too?

[julienmonty]

I think I found were the problem is. MariaDB does not provide a shortcut for the JSON_EXTRACT() function like MySQL (->). https://mariadb.com/kb/en/library/json_extract/

So I get it working by changing orderBy("name->\${$locale}", 'ASC') by orderByRaw("JSON_EXTRACT(name, '$.${locale}') ASC")

I could not test it with MySQL for now but as the function exists in the docs, it should be working as well. https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-extract

I could send a PR if needed.

[IsraelOrtuno]

I was thinking about a conflict between that when you said MySQL was working properly. Would be great if you make sure why is happening and can submit a PR 😃

[andrewnk]

It looks like @julienmonty figured out the issue, but just to follow up @IsraelOrtuno I am using MariaDB 10.2.14

[IsraelOrtuno]

What about this https://github.com/rinvex/attributes/blob/master/database/migrations/2017_01_19_040614_create_attributes_table.php#L54-L56

This is not considering MariaDB 10.2+ which already supports JSON, right?

cc @Omranic

[joaolisboa]

Why was this closed? Although MariaDB supports JSON the current syntax being used is still not valid AFAIK. I only know of JSON_EXTRACT like it has been mentioned here.

I really hate having to keep my own repo to go around this issue.

[Omranic]

Possible duplicate #67, however if you believe this is still an issue, feel free to re-open with more details on how to reproduce it with latest stable releases.