CODEWITHSUNDEEP
phpmysqllaravelmariadb
Pshtiwan KarDo
Pshtiwan KarDo

Reputation: 87

Where JSON in Laravel gives error SQLSTATE[42000]: check the manual that corresponds to your MariaDB server

I have tried to get data from database with laravel Where JSON. But it gives an error. I have added the data like this.

$event = new Event;
$event->scores = json_encode([['user_ids' => [1,2,3,4],'score' => 15]]);
$event->save();

When I want to return the data in database.

Event::where('scores->score',15)->get()

Shows this error:

SQLSTATE[42000]: 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 '>'$."score"' = ?' at line 1 (SQL: select * from `events` where `scores`->'$."score"' = 15)

My MariaDB version is 10.2.1

Upvotes: 1

Views: 783

Answers (2)

Matthew Mathieson
Matthew Mathieson

Reputation: 559

Array & JSON Casting

On your Event model you need to add

class Event extends Model {
      protected $casts = ['scores'=>'array'];
// ...
}

and then when saving the data 

$event = new Event;
// you had what appeared to be an extra array, that could cause the issue?
$event->scores = ['user_ids' => [1,2,3,4],'score' => 15]; 
$event->save();

Then the data will be saved as JSON automatically as long as the column type is TEXT or JSON(new versions of MySQL).

See Array & JSON casting on the Laravel Documentation: https://laravel.com/docs/5.5/eloquent-mutators#array-and-json-casting

Then to retrieve: 

Event::where('scores->score', '15')->get();

Here is the relevant documentation for JSON Where clauses https://laravel.com/docs/5.5/queries#json-where-clauses

Upvotes: 2

ficuscr
ficuscr

Reputation: 7054

Again, pretty confused as to what you are asking. Might help to see structure of the table or what the expected SQL should look like.

If you are trying to return rows based off values contained inside a string of JSON stored on the table... Then you need to do something like...

->whereRaw("JSON_CONTAINS(score, '[15]' )")->get();

Or something along those lines... https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

Upvotes: 0

Related Questions