Ambigous column name, cannot get table by group

I have SQL code to call my table in my Laravel controller like this:

elseif(auth()->user()->group_id){
                $media = MediaOrder::memberOf(auth()->user()->group_id)
                ->join('users','users.nik','=','media_order.created_by')
                ->select('media_order.*','users.nickname')
                ->get();

But when I tried it it come up with an ambiguous column error, like this:

LOG.error: SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Ambiguous column name 'group_id'. (SQL: select [media_order].*, [users].[nickname] from [media_order] inner join [users] on [users].[nik] = [media_order].[created_by] where [group_id] = 6) {"userId":"101","exception":{"errorInfo":["42000",209,"[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Ambiguous column name 'group_id'."]}

Does anyone has an solution to this? For joining between tables is working, but I just don't know how to use where to get the table based on group_id.

Thank you!.

CodePudding user response：

It seems like both the media_order and users tables have a group_id column, so perhaps something like the following will allow you to distiguish between them...

$media = DB::table('media_order')
  ->join('users','users.nik','=','media_order.created_by')
  ->select('media_order.*','users.nickname')
  ->where('media_order.group_id', '=', auth()->user()->group_id)
  ->get();