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.

See also: http://stackoverflow.com/questions/33253778/php-pdo-query-error-on-table-has-json-data-type-mysql-5-7-8-rc — Mike Rockétt, Aug 10 '16 at 05:08
can you show that how data stored in your Table, specially options column ? — Qazi, Aug 10 '16 at 05:24
@Qazi : Options column look like: {"religion": "R'hllor, the Lord of Light", "favorite_color": "red"} — Chiru Adi, Aug 10 '16 at 06:31

score 10 · Answer 1 · answered Aug 10 '16 at 11:46

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.

Thanks, you saved me! – Devendra Verma May 10 '17 at 14:52 — Devendra Verma, May 10 '17 at 14:52
Dude! You freak'n kick ass :) Thanks. – tnylea Jan 18 '18 at 03:01 — tnylea, Jan 18 '18 at 03:01

score 0 · Answer 2 · answered Apr 24 '17 at 10:25

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 '17 at 10:25

Youssef Gad

11
3

Laravel 5.2 query building for JSON on columns

2 Answers2

Linked