Laravel Conditional Relationship

Question

Here is the situation: I have 4 models.

• Invoices
• Creditors
• Debtors
• Timelines

These objects are related as such:

• An invoice has a Creditor, a Debtor, and may have a Timeline.
• A Debtor also may have a Timeline.
• A Creditor has a Timeline.

I am trying to write the timeline() relationship on Invoice to take into account the order of priority of these objects. If the Debtor has a Timeline, it is used. Else, if the Invoice has a Timeline, it is used. Lastly, the Creditor's Timeline is used if neither of the above were found.

I have looked around quite a bit, and I am struggling to find a solution to this. In my head, it would be simple, though it is proving not so.

Approaches I have taken to no avail...

Putting a raw select statement into the relationship where the select will return the _rowid of the timeline desired:

return $this->hasOne('Timeline', '_rowid', 'SELECT ...')

Same idea, using DB::raw():

return $this->hasOne('Timeline', '_rowid', DB::raw('SELECT ...'))

Trying to load using selects and joins to the relationship query:

return $this->hasOne('Timeline', '_rowid', 'timeline_rowid')
    ->select('Timelines_Country.*')
    ->join(DB::raw('(select if(Debitor.timeline_rowid is not null, Debitor.timeline_rowid, if(Invoice.timeline_rowid is not null, Invoice.timeline_rowid, Creditor.timeline_rowid)) as timeline_rowid from Invoice join Debitor on Invoice.unique_string_cd = Debitor.unique_string_cd join Creditor on Creditor.id = Invoice.creditor_id where Debitor.timeline_rowid is not null and Invoice.timeline_rowid is not null) temp'), function($join){
        $join->on('Timelines_Country._rowid', '=', 'temp.timeline_rowid');
    })
    ->whereNotNull('temp.timeline_rowid');

The final solution seemed the closest, but it still included the standard relationship query of "WHERE IN (?,?,?)" where the list was the timeline_rowids from the collection of Invoices, which caused certain timelines to still not be found.

Thanks in advance for any help!

Answer 1

Ok. I guess I fundamentally misunderstood how relationships work. Because they are loaded after the model is already instantiated, the array for the wherein part of the relationship query is built from accessing attributes of the model. Once I discovered that, it was actually an easy fix.

I wrote this function, which returns the timeline_rowid based on the relationships:

public function getDerivedTimelineAttribute()
{
    if ($this->debtor && $this->debtor->timeline_rowid)
    {
        return $this->debtor->timeline_rowid;
    }
    else if ($this->timeline_rowid)
    {
        return $this->timeline_rowid;
    }
    else if ($this->creditor->timeline_rowid)
    {
        return $this->creditor->timeline_rowid;
    }
    else
    {
        Log::Debug("can't find timeline", ['cdi' => $this->unique_string_cdi]);
        return 0;
    }
}

and then modified the relationship to read as:

public function timeline()
{
    return $this->hasOne('Timeline', '_rowid', 'derivedTimeline');
}

I hope this helps someone else. Drove me nuts for a while figuring it out!