I'm trying to create a relationship between my 'users', 'profiles' and 'countries' table.

• Users table has the columns: id - name - email - password
• Profiles table has the columns: id - user_id - country_id
• Countries table has the columns: id - name - code

The problem is that currently my code is matching a user id directly to the id in the country table (user one gets country one, user two - country two, etc). But what I would like to do is match the country_id in the profiles table to the country id in the countries table (so user 1 has a profile with a country_id of 5 which get the county with the id of 5 in the countries table). Any help correcting this would be much appreiated.

My User Model

class User extends Authenticatable implements MustVerifyEmail
{
    use HasFactory, Notifiable, Commenter;

    public function country()
    {
        // if I remove 'id' I get the error "Unknown column 'countries.user_id'"
        return $this->hasOne(Country::class, 'id');
    }

    public function profile()
    {
        return $this->hasOne(Profile::class);
    }
    
}

My Profile Model

class Profile extends Model
{
    use HasFactory;

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function country() {
        return $this->belongsTo(Country::class);
    }
}

My Country Model

class Country extends Model
{
    use HasFactory;

    public function user()
    {
        return $this->hasMany(Profile::class);
    }
}

My UserController

class UserController extends Controller
{
    public function singleUserData($userId) 
    {
        $singleUserData= User::where('id', $userId)->with('country','profile')->get();

        return view('single-user-data', ['singleUserData' => $singleUserData]);
    }

    public function allUsersData() {
        
        $allUsersData = User::with('profile','country')->get();

        return view('all-users-data', ['allUsersData' => $allUsersData]);
    }
}

CodePudding user response：

Your relations are slightly out.

Your Profile model is correct - each Profile belongs to one Country and belongs to one User.

It's your User and your Country models which aren't correct. Your User doesn't 'haveOne' Country as there's no direct relationship between Users and Countries - it goes through the Profile model. So what you're looking for there is, instead, that your User uses the 'hasOneThrough' relationship to find its Country.

And, in reverse, each Country will have (potentially) many Users, but not directly - again through the Profile model, so your Country needs to use the 'hasManyThrough' relationship to find its Users.