SQLSTATE [42000]: Syntax error or access violation

Question

Grouping without disabling strict mode of MySQL, the following query works if I disable strict mode, however, I would like not to do it, someone could give me a better suggestion, I have been trying in several ways and I can not do it.

The error thrown is: SQLSTATE [42000]: Syntax error or access violation:… up by digital_cards``id) as aggregate_table) '

$cards = DB::table('digital_cards')
                   ->join('companies', 'digital_cards.company_id', '=', 'companies.id')
                   ->join('profiles' , 'digital_cards.profile_id', '=', 'profiles.id')
                   
                   ->join('company_services', 'companies.id', '=', 'company_services.company_id')
                   ->join('services', 'company_services.service_id', '=', 'services.id')

                   ->where('services.service', 'LIKE', "%$keyword%")
                   ->where('digital_cards.is_public', '=', 1)

                   ->groupBy('digital_cards.id');
                   ->orderBy('companies.name', 'ASC')

                   ->paginate(10);

Answer 1

After getting some help I found the solution.

First off. Why does the error pop up?

The error pops up because when you group by you are not defining how you would like to choose the columns that were joined.
lets say you had these columns after joining tables (t=table, c= column):

t1.id | t1.c1 | t1.c2 | t2.c1 | t2.c2
1.    | hello | car   | alfa  | one
2.    | bye   | bike  | alfa  | one
3.    | hello | car   | beta  | two

Lets say we want to group by t1.c1. The SQL would not know if you want the values for alfa | one or beta | two so it throws error.

Why does 'strict' => false resolve the issue?

When you set strict to false, sql will automatically choose a default rule for picking the joined column values instead of throwing error.

How do I solve the issue without 'strict' => false?

Use MIN or MAX (or any other rules).
I do not know what columns your joined tables have, so here is a simplified version of my code:

$data = Payment::
    leftJoin('videos', 'videos.id', '=', 'payments.video_id')
    ->selectRaw('
            // payment columns
            min(videos.stream_start) stream_start,
            min(videos.keep_archive) keep_archive
        ')
    ->whereIn('payments.video_id', $videos)
    ->groupBy('year', 'month')
    ->orderBy('year', 'desc')
    ->get();

As you can see, I am only picking two columns from the joined table, so I just need to write min(column) on those two and the error disappears even with 'strict' => true (I am not sure if you also need rules for the first table).