menu

Questions & Answers

LatestOfMany() of BelongsToMany() relationship

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.

Comments:
2023-01-17 18:39:13
So just to understand the cause of your issue a little better, what exactly is not consistent about return $this->belongToMany(Document::class)->oldest()->limit(1); ? Do you have timestamps?
2023-01-17 18:39:13
Yeah I do. It's hard to pin-point how it causes inconsistency. But I've saw various SO posts about it, also have experienced it first hand too. But from what I've gathered limit on relationships and eager loading doesn't go well together.
2023-01-17 18:39:13
Thanks for your elaboration. Your question is really well formed, everything I think of, I find in your question as: Been there done that xD So just to go a bit deeper into the rabbit hole: Why do you need a user's first document? I see in your question that upload_date can change, and therefor the first document can change. Is that correct? Maybe you can give a bit info about the usecase
2023-01-17 18:39:13
Exactly, when an upload_date changes User's first document might change as well. Thing is it is a business requirement unfortunately, so there is no avoiding it I'm afraid. Currently I've elected to implement the 'order' column solution, it requires some delicate handling to keep it in sync. Hopefully this solution will suffice and won't cause a racket. Thank you for your interest. Will post the results after the dust settles down.
2023-01-17 18:39:13
Thanks again. I see that it is required, so let's work with that in mind. Why exactly does it need to be a relationship? I see that eager loading it would prevent the n+1 query issue, but you can make a custom query to load in all the first documents, with a list of person-id's you provide. That would still load in all the first-document-data with only 1 query, but won't be a relationship, rather a static function on the document-model. Would that be an acceptable idea? At least it removes the requirement to maintain relationships, while still 1 db call
2023-01-17 18:39:13
updated the post to include reasoning behind relationship requirement
2023-01-17 18:39:13
Thanks for the update. I do not see any other method than creating your own relationship class and assign that one to this relationship. I have never done that before tho, so am not sure if it is technically possible, given your constraints.
2023-01-17 18:39:13
I see, I'm glad for your efforts and considerations nonetheless.
2023-01-17 18:39:13
how about return $this->hasOneThrough(Document::class,DocumentPerson::class,'‌​person_id', 'id','id', 'document_id')->oldest('upload_date');
Answers(1) :

I have encountered a similar situation years back.

the best workaround on a situation like this is to use @staudenmeir package eager limit

Load the trait use \Staudenmeir\EloquentEagerLimit\HasEagerLimit; on both model (parent and related model)

then try the code below

public function firstDocument() {
    return $this->documents()->latest()->limit(1);
}

public function documents() {
    return $this->belongsToMany(Document::class);
}

just to add, Eager loading with limit does not work with built laravel eloquent, you would have to build your own raw queries to achieve it which can turn into a nightmare. that eager limit package from staudenmeir should have been merge with laravel source code 😆