Questions & Answers

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

• id
• upload_date
• identifier_code

Person

• id
• name

DocumentPerson (pivot)

• id
• person_id
• person_id
• token

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:

• Add a column called 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)
• Add a 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)

1. It should strictly be a relationship. I'll be using it on various places, it definitely has to be relationship so I can eager load and query it. My next objective involves querying by this relationship so it is imperative.
2. Don't suggest accessors, it won't do well with my case.
3. Don't suggest collection methods, it needs to be done in query.
4. Don't suggest ->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:

1. Get all the users whose first document (according to upload_date) upload_date between 2022-01-01 and 2022-06-08. (along with 10 other scopes and filters)
2. Get all the users whose first document (according to upload_date) identifier_code starts with "Lorem" and id bigger than 100.

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.