Laravel: Syntax Error or Access Violation: 1055 Error when Using WhereIn and GroupBy

For a specific row data retrieval, Laravel allows us to use both WhereIn and GroupBy in the same query. However, this can sometimes result in a "Syntax error or access violation: 1055 Error".

Causes of the Error

This error occurs when:

• Strict mode is enabled in MySQL settings.
• The id field, which is used in the WhereIn condition, is not included in the GroupBy field list.

Solutions

Disable Strict Mode

You can disable MySQL's strict mode by setting 'strict' => false in the 'mysql' array of the config/database.php file.

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

Add Modes to MySQL Options

Alternatively, you can leave strict mode enabled and add specific modes to the 'modes' option of the 'mysql' array:

'mysql' => [
    ...
    'strict' => true,
    'modes' => ['STRICT_TRANS_TABLES'],
    ...
],

Incorporate the GroupBy Field in WhereIn

Ensure that the id field used in the WhereIn condition is also included in the GroupBy field list. This will prevent the error from occurring.

$loadids = explode("#@*", $reciptdet->loading_id);
$loadingdatas = DB::table('loading')
    ->groupBy(['vehicle_no', 'id'])
    ->whereIn('id', $loadids)
    ->get();

By implementing these solutions, you can resolve the "Syntax error or access violation: 1055 Error" when using WhereIn and GroupBy in the same query.