Laravel - Join then alias two column

Question

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!.

Roboroads · Accepted Answer · 2021-12-17 09:41:22Z

1

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();

answered Dec 17, 2021 at 9:41

Roboroads

1,7571 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vtych Over a year ago

hello, the code is working perfectly thank you!, but there are some issue like, there are some NULL value in my traffic_viewed_by, so the table only get those row which had a value in traffic_viewed_by, is there a way to make those row shows up?, thanks again.

Roboroads Over a year ago

You can just add a ->whereNotNull('media_order.traffic_viewed_by')

Vtych Over a year ago

Here is what i've been trying but sadly it still get row which traffic_viewed_by not NULL, and aplogize im wrong

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

Roboroads Over a year ago

Then - is it null or an empty string you are saving in traffic_viewed_by? If the first is the case ->where('media_order.traffic_viewed_by', '!=', '')

Vtych Over a year ago

it was NULL when the order were created, so when the user traffic is viewed the order then the value of nik will be saved into traffic_viewed_by then it will be updated in database.

|

Collectives™ on Stack Overflow

Laravel - Join then alias two column

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related