Sid Heart
Sid Heart

Reputation: 743

SQLSTATE[42000]: Syntax error or access violation: 1055

I am using Laravel 5.5 i added this dependency for making simple search engine

https://github.com/nicolaslopezj/searchable

but getting problem when i try to search anything

SQLSTATE[42000]: Syntax error or access violation: 1055 'myreview.movies.name' isn't in GROUP BY (SQL: select count() as aggregate from (select movies., max((case when LOWER(movies.name) LIKE car then 150 else 0 end) + (case when LOWER(movies.name) LIKE car% then 50 else 0 end) + (case when LOWER(movies.name) LIKE %car% then 10 else 0 end) + (case when LOWER(movies.description) LIKE car then 150 else 0 end) + (case when LOWER(movies.description) LIKE car% then 50 else 0 end)

i have already tried with change mysql database i am using mariadb 10.2 after change to mysql 5.7 its working fine

but i have mariadb on my cpanel server

help me regard this thank you

Upvotes: 3

Views: 7811

Answers (2)

ultrajohn
ultrajohn

Reputation: 2597

The SQL reason why the query fails is that only columns which appeared in the GROUP BY clause can be used in the SELECT clause of an SQL statement that uses an aggregate function. The accepted answer in this SO post explained this adequately.

Upvotes: 0

M Danil Rafiqi
M Danil Rafiqi

Reputation: 584

  1. Open config/database.php
  2. Change value strict from true to false

    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => false,
        'engine' => null,
    ],
    
  3. Save

You can read full in my post => How to Fix SQLSTATE[42000]: Syntax error or access violation: 1055

Upvotes: 10

Related Questions