Laravel how to get User Profile field from separate table

Question

I am new to Laravel and Eloquent is quite a challenge for me to understand. I have a User and a UserProfile model and table. UserProfile table has user_id (FK to user's id), 'key, and value fields.

I want to get the values of all the UserProfile fields associated with the user_id. Here is my code but nothing works. I am sure I am making some stupid mistakes but still learning :) Laravel.

UserProfile Model

class UserProfile extends Model
{
    public $timestamps = FALSE;

    protected $fillable = [
        'user_id',
        'key',
        'value',
    ];

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

User Model method

public function profileFields(){

    return $this->hasMany(UserProfile::class);

}

UserProfile Migration

public function up()
{
    Schema::create('user_profiles', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->bigInteger('user_id')->unsigned();
        $table->string('key', 191);
        $table->longText('value');

        $table->foreign('user_id', 'user_profile_uid_fk')
              ->references('id')
              ->on('users')
              ->onDelete('cascade');

        $table->unique(['user_id', 'key'], 'user_profile_unique_key');
    });
}

I am trying to get the profile fields for the user using User::findOrFail(10)->profileFields but it is giving me property not defined exception.

Need Help: Can anyone help me to make it work so I can get all user_profiles fields from the profile table?

Error Output (Tinker)

>>> User::findOrFail(10)->profileFields

Illuminate/Database/QueryException with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'velvetpaper.user_user_profile' doesn't exist (SQL: select user_profiles.*, user_user_profile.user_id as pivot_user_id, user_user_profile.user_profile_id as pivot_user_profile_id from user_profiles inner join user_user_profile on user_profiles.id = user_user_profile.user_profile_id where user_user_profile.user_id = 10)'

Are you sure that it's failing from that line? Because Eloquent doesn't throw an exception when you try to access an undefined property, it simply returns null. — Chin Leung
@ChinLeung I have updated question with output. I realized there is something wrong with relation as it is searching for user_user_profile that sounds weired. :S — Code Lover
Are you sure you've used the hasMany relation in your App\User model instead of a belongsToMany? It would make sense for Laravel to search in user_user_profile table if you've used a belongsToMany. — Chin Leung

Julius Fasema Julius Fasema · Accepted Answer · 2020-03-05T13:24:05

you can use join to implement this stuff. like this... this is query builder

$data['profile']=DB::table(usertable)
->where('usertable.id',$id)
->leftjoin('userprofiletable','usertable.id','=','userprofiletable.user_id')
->first();

return view('profile_view',$data)

//view

$profile->fullname
$profile->sex
$profile->phone
...