Level 13
use whereHas and change "=<" to "<=" and no need of latest($date) just do latest()
Apr 4, 2017
10
Level 9
How to fetch only fetch the latest relationship
I'm trying to optimise an eloquent query loading a lot of relationships with a lot of data.
The eloquent query fetches a list of documents with all it's relationships:
$query = Document::with(['relationship_a' => function ($q) use ($date) {
$q->where('date', '=<', $date)->latest('date');
}])
->with(['relationship_b' => function ($q) use ($date) {
$q->where('date', '=<', $date)->latest('date');
}])
...
->whereIn('document.id', [a lot of ids]);
I'm only interested in the newest record of every relationship.
My current query will fetch all the records of each relationship. It's imported this data isn't fetched to save memory / enhance performance. I tried with group by's, limits, max and so on. None of them worked as I expected.
with(['relationship_b' => function ($q) use ($date) { $q->limit(1) }])
Will one fetch one relationship for the first document (not all the documents
with(['relationship_b' => function ($q) use ($date) { $q->groupBy('date') }])
Won't allow me to order the relationship by date
with(['relationship_b' => function ($q) use ($date) { $q->where('date', max(...)); }])
Won't work because every relationship can have a different date
Level 9
WhereHas will also fetch multiple records for each relationship. (I use latest because the field has a different name in my table).
It's importend to only fetch one record for each relationship per document.
Level 54
Try this:
$query = Document::with([
'relationship_a' => function ($q) {
$q->latest()->take(1);
},
'relationship_b' => function ($q) {
$q->latest()->take(1);
}])
...
->whereIn('document.id', [a lot of ids]);
Level 9
This will have the same result as ->limit(1). This will won't fetch relationships for all the documents.
Level 13
if u want only 1 then u can use array_slice in view and for latest value u can mention in model where u defined relationship like
public function relationship_a(){
return $this->hasMany(Relationshipa::class)->latest()
}
Level 9
Array_slice will work, but this will fetch the relationships in the first place. I don't want to this because of performance reasons. Some relationships have 1000 of records.
1000 Documents * (1000+ relationship_a + 1000+ relationship_b) = a lot of data
I want:
1000 Documents * (1 relationship_a + 1 relationship_b)
Level 54
This may not be possible with eager loading...
However, I believe max() works on dates in sql, so you could write a query with joins that only selects the max date (newest) for each of your joined relationships, and then group by the document so that you get each document you're after and exactly one of each joined relation.
the sql might look something like this:
SELECT d.*, a.*, b.*
FROM documents d
LEFT JOIN relationa a ON a.doc_id = d.id AND a.date = max(a.date)
LEFT JOIN relationb b ON b.doc_id = d.id AND b.date = max(b.date)
GROUP BY documents.id
That's probably still not quite right, but should be heading in the right direction.
Obviously you'd want to only select the relevant fields.
Level 9
I tried the above query but I can't get it to work. I always get the error
#1111 - Invalid use of group function
(I'm working with MySQL)
Level 7
@bramr, did you ever figure this out? I'm in the same boat with basically the same issue – I need the oldest/first related record, and continue to either not get those records or get the Invalid use of group function error you mentioned.
Level 102
@whoisthisstud as this thread is 4 years old, I think your best bet is to create a new thread describing your problem and showing your code. You can reference this thread of course
Please or to participate in this conversation.