Level 70
Normally, the first param of where() is the name of table column. You just correct that.
where('justPutYourTableColumnName', $reply->id)
Aug 29, 2017
6
Level 3
Query JSON column on MariaDB?
My webhost's MySQL version is 10.0.32-MariaDB. Whenever I try to query a JSON column like so:
where('data->model', $reply->id)
I get the following error:
Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$."model"
This is because my webhost's MySQL version apparently doesn't support JSON column types. (query works on my Homestead but not on my webhost)
How else am I meant to query a JSON column? Is changing my MySQL version the only option I have? (not even sure if that's possible with my shared hosting)
Level 3
@tisuchi I'm trying to query a JSON column. This is an example of what my data column contains:
{"model":14,"notifying":2,"message":"<b>EGN_Gaming<\/b> liked your post in <b>Introduce Yourself!<\/b>.","action":"https:\/\/egn-gaming.com\/forums\/reply\/14"}
I'm trying to query "model" by using data->model which is the correct syntax for JSON where clauses:
https://laravel.com/docs/5.4/queries#json-where-clauses
However my issue is that the MySQL version I'm using doesn't support that syntax, so I'm looking for an alternative solution.
Level 28
According to the MariaDb website, JSON columns are not available unit version 10.2. It appears that your version is below that. Is it possible that is the problem? https://mariadb.com/
Level 3
@Drfraker Yes that's the problem. I'm just wondering if there are any alternatives or if the only solution is to update my MySQL version.
Level 1
You can get access to json column using 'whereRaw' method and supported JSON functions.
Smth like
whereRaw('JSON_EXTRACT(`data`, \'$.model\') = ' . $reply->id)
Level 24
Laravel 5.8 will support JSON queries on MariaDB (https://github.com/laravel/framework/pull/25517).
Please or to participate in this conversation.