How to eager load "conditional" has-one relationship with the condition referencing to itself

Question

Imagine I had 4 models as follows:

• Year
• Position
• PositionAllowance : This is a pivot table of Position and Year, with the addition of "allowance" i.e. it defines the amount of allowance for each position in a specified year.
• Employee
• EmployeeAchievement : This is used to record employee achievements, which records the current position of the employee and current year time

If I want to get the EmployeeAchievement, how can I eager-load the AllowancePosition based on the year that it has?

Example of data for clear understanding:

> Year
  >> id: 1 , year: 2023
  >> id: 2 , year: 2024

> Position
  >> id: 1 , name: Junior
  >> id: 2 , name: Senior

> PositionAllowance
  >> id: 1 , position_id: 1 , year_id: 1 , allowance: 100    // Year 2023, $100 for Junior
  >> id: 2 , position_id: 1 , year_id: 2 , allowance: 200    // Year 2024, $200 for Junior

> Employee
  >> id: 1 , name: Joe , position_id: 1

> EmployeeAchievement
  >> id: 1 , employee_id: 1 , current_position_id: 1 , year_id: 1   // 2023 Joe should get $100
  >> id: 2 , employee_id: 1 , current_position_id: 1 , year_id: 2   // 2024 Joe should get $200

Once again, how can I get all records of EmployeeAchievement while eager-loading only one AllowancePosition based on its year? If possible, I would like to do it with Eloquent, but I'm open up for all suggestion.

I'm expecting to get something like this:

EmployeeAchievement[0] => id: 1
                          employee => id: 1 , name: Joe
                          ....
                          positionAllowance => id: 1 , allowance: 100

EmployeeAchievement[2] => id: 2
                          employee => id: 1 , name: Joe
                          ....
                          positionAllowance => id: 2 , allowance: 200

Your input is much appreciated. Thanks in advance!

Answer 1

You can use the "with" method:

class EmployeeAchievement extends Model
{
    public function positionAllowance()
    {
        return $this->belongsTo(PositionAllowance::class, 'position_id', 'position_id')
            ->where('year_id', $this->year_id);
    }
}

EmployeeAchievement::with('positionAllowance')->get();