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

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();

CodePudding user response：

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

CodePudding user response：

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();