Laravel 5.2 query building for JSON on columns

Question

I am using MySql 5.7.14 and laravel 5.2.
My Table 'user' JSON look like this:

[{
    "id": "1",
    "options": "{\"religion\": \"R'hllor, the Lord of Light\", \"favorite_color\": \"red\"}"
}]

when i query through the laravel as:

$ii =\DB::table('user')->where('options->favorite_color', 'red')->get();

i am facing error as

SQLSTATE[HY000]: General error: 2036 (SQL: select * from `user` where `options`->"$.favorite_color" = red)

and when i run the same query as

SELECT * FROM `user` WHERE options->"$.favorite_color"='red'

in phpmyadmin its not giving any error it working fine. Can anyone tell me what may be the issue? Thanks in advance.

can you show that how data stored in your Table, specially options column ? — Qazi
– Qazi, Commented Aug 10, 2016 at 5:24
@Qazi : Options column look like: {"religion": "R'hllor, the Lord of Light", "favorite_color": "red"} — Chiru Adi
– Chiru Adi, Commented Aug 10, 2016 at 6:31

Chiru Adi · Accepted Answer · 2016-08-10 11:46:17Z

10

I got the solution. To work with MySQL 5.7.8 and above using laravel 5.2 need to add the PDO options to the your connections in config/database.php as follows.

 'mysql' => [
  'driver'    => 'mysql',
  'host'      => env('DB_HOST', 'localhost'),
  'database'  => env('DB_DATABASE', 'db_name'),
  'username'  => env('DB_USERNAME', 'root'),
  'password'  => env('DB_PASSWORD', ''),
  'charset'   => 'utf8',
  'collation' => 'utf8_unicode_ci',
  'prefix'    => '',
  'strict'    => false,
   'options'   => array(
           PDO::ATTR_CASE => PDO::CASE_LOWER,
           PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
           PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
           PDO::ATTR_STRINGIFY_FETCHES => true,
           PDO::ATTR_EMULATE_PREPARES => true,
       ),

],

and then run php artisan config:cache. Its works well. vendor/laravel/framework/src/illuminate/Database/Connectors/Connector.php PDO Options are like

protected $options = [
    PDO::ATTR_CASE => PDO::CASE_NATURAL,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
    PDO::ATTR_STRINGIFY_FETCHES => false,
    PDO::ATTR_EMULATE_PREPARES => false,
];

PDO::ATTR_STRINGIFY_FETCHES & PDO::ATTR_EMULATE_PREPARES Need To Be Overwritten in database.php.

answered Aug 10, 2016 at 11:46

Chiru Adi

6475 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Kalmikov Over a year ago

Works like a charm.

Youssef Gad · Accepted Answer · 2017-04-24 10:25:23Z

0

check Your Php Version and if its not 7.0 or 7.1

Then Point it to Correct Version

sudo update-alternatives --set php /usr/bin/php7.1

Worked For Me

answered Apr 24, 2017 at 10:25

Youssef Gad

113 bronze badges

Collectives™ on Stack Overflow

Laravel 5.2 query building for JSON on columns

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related