search

flightphp / active-record

Micro Active Record library in PHP, supports chain calls, events, and relations.
https://docs.flightphp.com/awesome-plugins/active-record
MIT License
7 stars 3 forks source link

Support for JSON fields in MySQL and MariaDB #15

Closed Billtec closed 1 month ago

Billtec commented 1 month ago

It seems that the JSON_* functions (like JSON_ARRAY, JSON_SEARCH) used to support JSON fields in new version of MySQL anb MariaDB cannot be used in current version of active-record.

Is there any workaround?

n0nag0n commented 1 month ago

Do you have an example of how you’re trying to use it exactly?

Billtec commented 1 month ago

For example, I would like to query sth. inside a JSON type data field and the SQL should be SELECT JSON_QUERY(data,'$.info') FROM user WHERE JSON_VALUE(data, '$.info.birthyear') = 1990;.

Ideally it can be done by using $user->select("JSON_QUERY(data,'$.info')")->eq("JSON_VALUE(data, '$.info.birthyear')", 1990)->find(); . However, since active record with automatically add the table name before each field, the SQL will be formed like SELECT user.JSON_QUERY(data,'$.info') FROM user WHERE user.JSON_VALUE(data, '$.info.birthyear') = 1990;. Apparently it is not a valid SQL.

n0nag0n commented 1 month ago

I believe this PR solves your problem. https://github.com/flightphp/active-record/pull/16 Let me know if it doesn't!