How to get data from three related table with it's model relationships?

Question

I have three tables like these:

Expense:-

1. expense_id
2. user_id

User:-

1. user_id
2. employee_id

Employee:-

1. employee_id
2. first_name
3. last_name

I want to get first_name and last_name from the employee table where expense.user_id = user.user_id, I tried something like bellow, but I did not get the correct data.

Expense model:-

 public function users(){

    return $this->hasOne(User::class,'user_id','user_id');
}

User model:-

public function employee(){
    return $this->hasOne(Employee::class,'employee_id','user_id');
}

And employee model:-

public function users()
{
    return $this->belongsTo(User::class,'employee_id');
}

I called to view like this:-

<td>{{$expense->users->employee->first_name ." " . $expense->users->employee->last_name }}</td>

It shows data, but not as expected data.

Where is my mistake and how it should be? please help! thanks!

Answer 1

I believe you're misusing eloquent relationships.

One To One Relationship.

One To Many Relationship

A one-to-many relationship is used to define relationships where a single model is the parent to one or more child models.

Try this instead.

use App\Models\Employee;
use App\Models\Expense;

// User model.
class User extends Authenticatable
{
    use HasFactory, Notifiable;

    protected $primaryKey = 'user_id';

    protected $table = 'users';

    public function employee()
    {
        return $this->hasOne(Employee::class, "employee_id", "employee_id");
    }

    public function expenses()
    {
        return $this->hasMany(Expense::class, "user_id", "user_id");
    }
    
}

// Expense model
class Expense extends Model
{
    use HasFactory;

    protected $primaryKey = 'expense_id';

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

// Employee model.
class Employee extends Model
{
    use HasFactory;

    protected $primaryKey = 'employee_id';

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

// 'create_users_table' migration.
class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id('user_id');
            $table->unsignedBigInteger("employee_id");
            $table->timestamps();

            $table->foreign("employee_id")
                ->references("employee_id")
                ->on("employees")
                ->onDelete("cascade");
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

// 'create_expenses_table' migration.
class CreateExpensesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('expenses', function (Blueprint $table) {
            $table->id('expense_id');
            $table->unsignedBigInteger("user_id");
            $table->timestamps();

            $table->foreign("user_id")
                ->references("user_id")
                ->on("users")
                ->onDelete("cascade");

        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('expenses');
    }
}

// 'create_employees_table' migration.
class CreateEmployeesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('employees', function (Blueprint $table) {
            $table->id("employee_id");
            $table->string("first_name");
            $table->string("last_name");
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('employees');
    }
}

// Sample query 1.
App\Models\Expense::find(1)
    ->with(
        ["user" => function($query)
        {
            $query->with("employee");
        }])
    ->first();

// Sample output 1.
/*
=> App\Models\Expense {#4172
     expense_id: 1,
     user_id: 1,
     created_at: null,
     updated_at: null,
     user: App\Models\User {#4322
       user_id: 1,
       employee_id: 1,
       created_at: null,
       updated_at: null,
       employee: App\Models\Employee {#4330
         employee_id: 1,
         first_name: "john",
         last_name: "doe",
         created_at: null,
         updated_at: null,
       },
     },
   }

*/

// Sample query 2.
App\Models\Expense::with(
    ["user" => function($query)
    {$query->with("employee");
    }])->get();

// Sample output 2.
/*
=> Illuminate\Database\Eloquent\Collection {#4318
     all: [
       App\Models\Expense {#4320
         expense_id: 1,
         user_id: 1,
         created_at: null,
         updated_at: null,
         user: App\Models\User {#3382
           user_id: 1,
           employee_id: 1,
           created_at: null,
           updated_at: null,
           employee: App\Models\Employee {#4335
             employee_id: 1,
             first_name: "john",
             last_name: "doe",
             created_at: null,
             updated_at: null,
           },
         },
       },
       App\Models\Expense {#4323
         expense_id: 2,
         user_id: 1,
         created_at: null,
         updated_at: null,
         user: App\Models\User {#3382},
       },
     ],
   }
*/

Answer 2

I suggest you redesign your database and relationships this way:

Expense:-

id
user_id

User:-

id

Employee:-

id
user_id
first_name
last_name

For BelongsTo and HasOne relationships, pls use single nouns:

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

class User extends Model
{
    public function employee()
    {
        return $this->hasOne(Employee::class);
    }
}

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

    public function getFullNameAttribute()
    {
        return $this->first_name . ' ' . $this->last_name;
    }
}

Finally, you can access the employee's full name like this:

$expense->user->employee->full_name;

Answer 3

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

class User extends Model
{
    public function employee()
    {
        return $this->belongsTo(Employee::class);
    }
}

class Employee extends Model
{
    public function user()
    {
        return $this->hasOne(User::class);
    }

    public function getFullNameAttribute()
    {
        return $this->first_name . ' ' . $this->last_name;
    }
}

Then access the customer attribute:

<td>{{$expense->user->employee->full_name}}</td>