Laravel - Join then alias two column-CodePudding

i have a sql to call my table like this :

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

And then i was using this alias to replace nik for column created_by in table media_order by replace it with nickname from table users like this :

->editColumn('created_by', function ($row) {
                        return $row->nama1;           
                      })

And it working, here is the result :

but then i had another column in my table that called traffic_viewed_by which also using nik column in table media_order, something like this :

it still using nik for the table and i dont know how to alias it to make it show using nickname from table users and then show it in my table, i have no clue how to do it, anyone had an solution or maybe alternative way to do it?, any help is really appreciated, thank you!.

CodePudding user response：

To get another user for another column you will need to join the other user as well. And since you are joining users twice, they each need a unique name so SQL can understand what you are trying to do.

$media = MediaOrder::join('users as created_by_user','created_by_user.nik','=','media_order.created_by')
                ->join('users as viewed_by_user','viewed_by_user.nik','=','media_order.traffic_viewed_by')
                ->select('media_order.*','created_by_user.nickname AS nama1', 'viewed_by_user.nickname AS nama2')
                ->where('media_order.group_id','=', auth()->user()->group_id)
                ->get();