SQLSTATE[42000]: Syntax error or access violation: 1055 'school.students.name' isn't in GROUP BY

[gregoriochiko]

I got error while I'm using this trait normally. Here's my model:

namespace App;

use Illuminate\Database\Eloquent\Model;
use Nicolaslopezj\Searchable\SearchableTrait;

class Student extends Model
{
    use SearchableTrait;

    protected $searchable = [
        'columns' => [
            'students.name' => 15,
            'students.email' => 10
        ]
    ];
}

And here's my controller:

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Student;

class StudentController extends Controller
{
    public function search(Request $request)  // related to 'students.search' route
    {
        return Student::search($request->query('q'))->get();  // I simplified this function here.
    }
}

And this is how I call the controller from unit test:

use Tests\TestCase;
use Illuminate\Foundation\Testing\RefreshDatabase;

class StudentTest extends TestCase
{
    use RefreshDatabase;

    protected $user;

    public function testSearchStudents()
    {
        $this->actingAs($this->user, 'api')
            ->get(route('students.search', ['q' => 'jack']))
            ->assertJsonStructure([    // I also simplified this section, the point is I compare the given structure exact with what the model should gives
                'id',
                'name'
            ]);
    }

But when I run vendor/bin/phpunit tests/Unit I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'school.students.name' isn't in GROUP BY (SQL: ...

I've tried to remove the column name from the $searchable columns, but it still gives the exactly same error.

[lperry65]

Having the same issue.

$users = User::search('fred', null, true, true)->first();

class User extends Authenticatable { use Notifiable, hasRoles, SearchableTrait;

protected $searchable = [
    /**
     * Columns and their priority in search results.
     * Columns with higher values are more important.
     * Columns with equal values have equal importance.
     *
     * @var array
     */
    'columns' => [
        'users.first_name' => 10,
        'users.last_name' => 10,
        'users.username' => 10,
        'users.qualification' => 10,
        'users.email' => 10
    ]
];

}

[maxdaniel98]

Temporary solution: set strict mode to false.

'mysql' => [
            'driver' => 'mysql',
            [...]
            'strict' => false,
        ],

[jozeflambrecht]

Is there a "structural" solution?

[gpressutto5]

Same error here 😢

[SaphiLC]

Temporary solution: set strict mode to false.

'mysql' => [
            'driver' => 'mysql',
            [...]
            'strict' => false,
        ],

this fixed the problem, is it safe to use tho?

[ssheduardo]

Anyone update about this error?

[shanks25]

getting same error , using laravel 5.8

[functioneelwit]

Temporary solution: set strict mode to false.

'mysql' => [
            'driver' => 'mysql',
            [...]
            'strict' => false,
        ],

this fixed the problem, is it safe to use tho?

According to this post its ok to set strict mode te false with some conditions.

https://stackoverflow.com/questions/42104412/what-is-the-use-of-strict-in-laravel-config-database

[gpressutto5]

It's not wise to disable the strict mode. Never trust yourself.

[functioneelwit]

@gpressutto5: I guess you have a point. Maybe it's a little more secure to only allow grouping by one column like so:

'mysql' => [
       ...
       ....
       'strict' => true,
       'modes' => [
            //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
        ],
 ]

Source: https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error