I've been using latestOfmany() for my hasMany() relation to define them as hasOne() for quite a while now. Lately I've been in need of the similar application but for belongsToMany() relationships. Laravel doesn't have this feature unfortunately.
My codebase as follows:
Document
Person
DocumentPerson (pivot)
My objective is: define relationship for fetching the first document (according to upload_date) of Person. As you can see it's a many-to-many relationship.
What I have tried so far:
public function firstDocument()
{
return $this->hasOne(DocumentPerson::class)->oldestOfMany('document.upload_date');
//this was my safe bet but oldestOfMany() and ofMany() doesn't allow aggregating on relationship column.
}
public function firstDocument()
{
return $this->belongToMany(Document::class)->oldestOfMany('upload_date')
}
public function firstDocument()
{
return $this->belongToMany(Document::class)->oldest()->limit(1);
}
public function firstDocument()
{
return $this->hasOneThrough(Document::class, DocumentPerson::class, 'id', 'document_id', 'id', 'person_id')->latestOfMany('upload_date');
}
At this point I'm almost positive current relationship base doesn't support something like this, so I'm elaborating alternative methods to solve this. My two choices:
first_document_id on Person table, go through that with belongsTo() simple and fast performance-wise. But downside is I'll have to implement so many event-listeners to make sure it is always consistent with actual relationships. What if Document's upload_date is updates etc. (basically database inconsistency)order column on pivot (document_person) table, which will hold order of related Documents by upload_date. This way I can do hasOne(DocumentPerson::class)->oldestOfMany('order');//or just ofMany() and be done with it. This one also poses the risk of database inconsistency.It's fair to say I'm at a crossroads here. Any idea and suggestion is welcomed and appreciated. Thank you. Please read the restrictions to prevent suggesting things that are not feasible for my situation.
Restrictions:
(Please)
->limit() or ->take() or ->first(), those are prone to cause inconsistent results with eager loading.Update 1
Q: Why first document of a person has to be a relationship ?
A: Because further down the line I'll be querying it in various different instances. Example queries where it'll be utilized:
These are just to name a few, there are many cases where I really gotta query it in various fashions. This is the reason that I desperately need it to be a relationship, so I can query it with ease using Person::whereHas('firstDocument',function($subQuery){ return $subQuery->someScope1()->anotherScope2()->where(...); }
If I only needed to display it, yeah sure eager loading with closure would do well, or even collection methods, or accessors would suffice. But since ability to query it is the need, relationship is of the essence. Keep in mind Person table has around 500k record, hence the need for querying it on the database layer.
Alright here's the solution I've elected to go with (among my choices, explained in the question). I implemented the "adding order column on pivot" table. Because it scales better and is rather flexible compared to other options. It allows for querying the last document, first document, third document etc. Whilst it doesn't even require any aggregate functions (Max, min like ->latestOfMany() applies) which is a performance boost. Given these constraints this solution was the way to go. Here's how I applied it in case someone else is thinking about something similar.
Currently the only noticeable downside to this approach is inability to access any additional pivot data.
Added new column for order:
//migration
$table->unsignedTinyInteger('document_upload_date_order')->nullable()->after('token');
$table->index('document_upload_date_order');//for performance
Person.php (Model)
//... other stuff
public function personalDocuments()
{//my old relationship, which I'll still keep for display/index purposes.
return $this->belongsToMany(Document::class)->withPivot('token')->where('type_slug','personal');
}
//NEW RELATIONSHIP
public function firstDocument()
{//Eloquent relationship, allows for querying and eager loading
return $this->hasOneThrough(
Document::class,
DocumentPerson::class,//pivot class for the pivot table
'person_id',
'id',
'id',
'document_id')
->where('document_upload_date_order',1);//magic here
SomeService.php
public function determineDocumentUploadDateOrders(Person $person){
$sortLogic=[
['upload_date', 'asc'],
['created_at', 'asc'],
];
$documentsOrdered=$person->documents->sortBy($sortLogic)->values();//values() is for re-indexing the array keys
foreach ($documentsOrdered as $index=>$document){
//updating through pivot tables ORM model
DocumentPerson::where('id',$document->pivot->id)->update([
'document_upload_date_order'=>$index+1,
'document_id'=>$document->id,
'person_id'=>$document->pivot->person_id,
]);
}
}
I hooked determineDocumentUploadDateOrders() into various event-listeners and model events so whenever association/disassociation occurs, or upload_date of a document changes I simply call determineDocumentUploadDateOrders() with corresponding Person and this way it is always kept in sync with actual.
Implemented it fully and it is providing consistent results with great performance. Of course it brought a bit of an overhead with keeping it in sync. But nonetheless, It did the job whilst meeting the requirements. Honestly I found this approach far more reliable than some in-official eloquent relationships and similar alternatives.