Build database query in Laravel with eloquent or DB

I need your help to build a query in Laravel either eloquent or DB query would do too.

My table name is Users

To see the DB structure, open the following link:

Every user row has 2 columns referral_code and referred_by_code which means every user can refer to someone and earn bonus similarly the same user was also been referred by some one.

I would like to return the information on page with loop in users details along with Username of the user who had referred him to this. To track the same I created those 2 columns in the same table i.e.: referral_code and referred_by_code.

I do not know how to write this in 1 query or how to combine 2 queries and get the desired results.

My controller code looks like below:

$obj_users = User::get();
$codes = [];
$referrers = [];

foreach( $obj_users as $referred_by_code )
{
   //Following fetches all user's referred_by_code's code
   $codes[] = $referred_by_code->referred_by_code;
}

foreach ($codes as $code) 
{
   //Following fetches usernames of the given referred_by_code's codes       
   $referrers[] = User::where('referral_code', $code)->first()->username;
}

return view('users.users', compact(['users', 'paginate', 'referrers']));

The returning $users variable provides me loop of users data but I do not know how to attach those referrer's username to that object.

I tried my level best to describe, please ask incase what I said doesn't make sense, will be happy to provide further clarification.

Best

Solution

You can add into your User model the following relationship:

public function referredBy()
{
    return $this->belongsTo(User::class, 'referred_code', 'referral_code');
}

In your controller you can use:

$users = User::with('referredBy')->get();

return view('users.users', compact('users'));

and later in your view you can use:

@foreach ($users as $user)
   {{ $user->username }} referred by {{ $user->referredBy ? $user->referredBy->username : '-' }}
@endforeach