Load model one to many relation eloquent way without primary key but on multiple overlapping fields

Question

I'm working on an older project that I've been tasked to speed up certain parts of while we work on a complete re-write since the code is just badly maintained, poorly written and outdated for what it's suppose to do.

I stumbled into an issue to the core of the project and because of this I can't change it without breaking almost everything else. So I need to load a "relation" the eloquent way (using Planning:with('availability') but there isn't a real foreign ID, it rather laps with multiple fields.

Would there be a way to load it all in one query with the overlapping fields rather than have it load separately creating an n+1 problem?

+--------------+-----------------+
| Planning     | Availability    |
+--------------+-----------------+
| planning_id  | availability_id |
| date         | date            |
| startHour    | startHour       |
| stopHour     | stopHour        |
| candidate_id | candidate_id    |
| section_id   | section_id      |
+--------------+-----------------+

From the above example you can see the overlapping fields are date, startHour, stopHour, candidate_id and section_id.

I tried get...attribute but that still loads with n+1, I tried including it with ->with(['availabilities']) but that doesn't work since I ask for the model and not the relation:

Edit for more clarity:

Planning Model:

public function availabilities()
{
    return Availability::where('section_id', $this->section_id)
        ->where('candidate_id', $this->candidate_id)
        ->where('planningDate', $this->planningDate)
        ->where('startHour', $this->startHour)
        ->where('stopHour', $this->stopHour)
        ->get();
}

public function availabilities2()
{
    return $this->hasMany('App\Models\Availability', 'candidate_id', 'candidate_id')
}

Controller:

$plannings = Planning::with(['availabilities'])->get();

$plannings = Planning::with(['availabilities2' => function ($query) {
    // $this is suppose to be Planning model but doesn't work
    $query->where('section_id', $this->section_id)
        ->where('planningDate', $this->planningDate)
        ->where('startHour', $this->startHour)
        ->where('stopHour', $this->stopHour);

    // ---- OR ---- //
    // Don't have access to planning table here 
    $query->where('section_id', 'planning.section_id')
        ->where('planningDate', 'planning.planningDate')
        ->where('startHour', 'planning.startHour')
        ->where('stopHour', 'planning.stopHour');
}])->get();

Answer 1

First of all to be able to load my relation I took one of the keys that matched and took the one which had the least matches which in my case was section_id.

So on my Planning model I have a function:

public function availabilities()
{
    return $this->hasMany('App\Models\Availability', 'section_id', 'section_id');
}

This way I can load the data when needed with: Planning:with('availability').

However since I had a few other keys that needed to match I found a way to limit this relation by adding a subquery to it:

$planning = Planning::with([
    'availabilities' => function ($query) {
        $query->where('candidate_id', $this->candidate_id)
            ->where('startHour', $this->startHour)
            ->where('stopHour', $this->stopHour);
    },
    // Any other relations could be added here
    ])
    ->get();

It's not the best way but it is the only way I found it not getting too much extra data, while also respecting my relationship

Answer 2

When you want to use multiple fields in where() method you most insert a array in the where() method: This document can help you

• change your code to this:

return Availability::where([
    ['section_id', $this->section_id],
    ['candidate_id', $this->candidate_id],
    ['planningDate', $this->planningDate],
    ['startHour', $this->startHour],
    ['stopHour', $this->stopHour]
])->firstOrFail();