Laravel - sorting an array of collections by the property of their relationship object
Solution 1
First of all: Instead of using Collection::groupBy()
you can just use relations:
$magazines = Magazine::with(['issues' => function($query) {
$query->orderBy('date', 'desc');
}]);
This will give you a collection of Magazines including related issues in an array. The issues
array is sorted by date desc
.
Now you can split the result using two queries with different conditions:
$baseQuery = Magazine::with(['issues' => function($query) {
$query->orderBy('date', 'desc');
}]);
$query1 = clone $baseQuery;
$query2 = clone $baseQuery;
$query1->whereIn('product_code', $productCodes);
$query2->whereNotIn('product_code', $productCodes);
$query1
will return all magazines with product_code
from the array. $query2
will return all the other magazines.
You have now two ways to combine the two results:
1) Use Eloquent::unionAll()
$unionQuery = $query1->unionAll($query2);
$magazines = $unionQuery->get();
2) Use Collection::merge()
$magazines1 = $query1->get();
$magazines2 = $query2->get();
$magazines = $magazines1->merge($magazines2);
In both cases you will get the same result: A collection of magazines. The magazines with product_code
from the $productCodes
array are sorted first. Every Magazine
object contains an array issues
with related Issue
objects.
If you want to get rid of the Magazine
objects and really need the result to look like in your question, you can still do:
$issues = $magazines->keyBy('id')->pluck('issue');
But there is probably no need for that.
Update
If you really need the two parts of the Magazine
collection to be sorted by the latest Issue
.. I don't see any way without using a JOIN or sorting in PHP with a closure. Sorting magazines with a join would also need an aggregation. So i would switch back to your original query extending it with a join and split it in two parts as showed above:
$baseQuery = Issue::join('magazines', 'magazines.id', '=', 'issues.magazine_id');
$baseQuery->orderBy('issues.date', 'desc');
$baseQuery->select('issues.*');
$query1 = clone $baseQuery;
$query2 = clone $baseQuery;
$query1->whereIn('magazines.product_code', $productCodes);
$query2->whereNotIn('magazines.product_code', $productCodes);
And then
$issues1 = $query1->get()->groupBy('magazine_id');
$issues2 = $query2->get()->groupBy('magazine_id');
$issues = $issues1->union($issues2);
or
$issues = $query1->unionAll($query2)->get()->groupBy('optionGroupId');
Solution 2
Code first, step-by-step explanation at the end:
Magazine::all()
->load('issues')
->keyBy('id')
->sort(function ($a, $b) use ($product_codes) {
return
(in_array($b->product_code, $product_codes) - in_array($a->product_code, $product_codes))
?:
(strtotime($b->latestIssueDate) - strtotime($a->latestIssueDate));
})
->map(function ($m) {
return $m->issues->sortByDesc('created_at');
});
This gets you:
- all Magazines
- with each of their Issues eager-loaded
- in a Collection where the keys are each Magazine
id
- which is sorted by 2 criteria: first whether the
product_code
is among the requested codes, then the date of the latest Issue (*) - finally, for each Magazine you want a Collection of its issues, sorted by date descending.
If I understood your requirement correctly, this will give you what you want.
(This assumes the date column is called created_at
.)
(*) for convenience I'm using an attribute accessor:
public function getLatestIssueDateAttribute()
{
// this breaks for Magazines with no Issues. Fix if necessary
return $this->issues->sortByDesc('created_at')->first()->created_at;
}
Solution 3
First thing is with
needs to be an array and when using a subquery, don't call get()
as it will replicate the query for each Issue
giving you a nice little N+1 problem. For the groupBy
simply pass id
as a 2nd parameter to help with the only_full_group_by error.
It should look like this:
$issues = Issue::groupBy('magazine_id', 'id')
->with('magazine')
->orderBy('date', 'desc')
->get();
That will order your Issue
collection by date and then in each $issues->magazine
will be ordered by product_code
if you want to order this in one query by date
and then by then product_code
you need to do a join like:
$issues = Issue::select('issues.*')
->groupBy('magazine_id', 'id')
->join('magazine', 'magazine.id', '=', 'issues.magazine_id')
->with('magazine')
->orderBy('issues.date', 'desc')
->orderBy('magazine.product_code', $productCodes)
->get();
UPDATE
The following should give you grouped magazine collections sorted by date with each magazine collection sorted by product codes in the order specified in the $productCodes array:
$issues = Issue::groupBy('magazine_id', 'id')
->with('magazine')
->orderBy('date', 'desc')
->get()
->groupBy('magazine_id')
->sortBy(function($issue) use ($productCodes) {
$index = array_search($issue->first()->magazine->product_id, $productCodes);
return $index !== false ? $index : $issue->magazine_date;
});
Ludwig
Updated on June 05, 2022Comments
-
Ludwig almost 2 years
I have two tables
magazines
with a fieldproduct_code
, and another tableissues
. They havebelongsToMany
relationship.Magazine model:
public function issues() { return $this->hasMany('App\Issue'); }
Issue model:
public function magazine() { return $this->belongsTo('App\Magazine'); }
Currently I have a query where I get collections of issues grouped by magazine id and ordered by the date of the last issue.
$issues = Issue::orderBy('date', 'desc')->get()->groupBy('magazine_id');
This is how the result of my query looks like:
Collection {#431 ▼ #items: array:23 [▼ 103 => Collection {#206 ▼ #items: array:52 [▶] } 106 => Collection {#216 ▶} 124 => Collection {#452 ▶} 112 => Collection {#451 ▶} 115 => Collection {#450 ▶} 123 => Collection {#449 ▶} 107 => Collection {#448 ▶} 113 => Collection {#447 ▶} 117 => Collection {#446 ▶} 109 => Collection {#445 ▶} 110 => Collection {#444 ▶} 121 => Collection {#443 ▶} 120 => Collection {#442 ▶} 114 => Collection {#441 ▶} 116 => Collection {#440 ▶} 118 => Collection {#439 ▶} 126 => Collection {#438 ▶} 125 => Collection {#437 ▶} 119 => Collection {#436 ▶} 122 => Collection {#435 ▶} 105 => Collection {#434 ▶} 111 => Collection {#433 ▶} 104 => Collection {#432 ▶} ] }
So, since I have 24 magazines, there are 24 collections of
issues
in the array, and eachcollection
ofissues
belongs to onemagazine
. The collections are sorted by the date of the latests issue of each collection and issues inside of each collection are ordered by date as well. So, first collection in the array will be the one which has the latest issue in the tableissues
, the second collection will be the one which has the second latest issue in the same table and so on.Since I will get an array of users subscriptions, which will consist of
product codes
like this:$productCodes = ['aa1', 'bb2', 'cc3'];
I need to expand this query and sort the collections further by the
$productCodes
array that I will get. I need to check the codes from theproductCodes
array in the tablemagazines
where I have theproduct_code
field. The collections of issues grouped bymagazine
, should be then sorted so that the first collections are the ones whosemagazine
that they belong to has the sameproduct_code
as the code in the arrayproductCodes
, and amongst them, the first one would be whose collection has the latest issue by date. Then the rest of the collections should just be sorted by date. How can I make this kind of query?Update
I have tried with a suggested code from @Paul Spiegel in the answers, and now I get an array of collections, with the collections of magazine issues. Issues in each magazine collection are ordered by date, and magazine collections that have the same
product_code
as in the$productCodes
array are at the beginning of the array, but the the array of magazine collections is still not sorted by the date of the latest issue from each of the magazine collections. -
Ludwig over 7 yearsThank you for your effort, but this query doesn't work for me. With my query I was getting an array with 24 collections of issues grouped by magazine id. Now I am getting all the issues in one array. I still need to get a collections of issues grouped by the magazine id, the only thing new that I need to do is to somehow sort those collections of issues and have the collections of issues whose magazine that they belong to has the same product_code as the codes in the productCodes array first in the array.
-
Eric Tucker over 7 yearsWhoops, didn't see that was an array. The updated query should work to sort by the given array
-
Ludwig over 7 yearsI still get all of the issues in one array, and not the array of collections of issues. I also see that I haven't made myself clear of what was I getting from my query. I have corrected that in my question.
-
Eric Tucker over 7 yearsI think I understand, does this last edit solve it? You want Issues grouped by magazine collections and ordered by date and each magazine collection ordered by product_code, correct?
-
Ludwig over 7 yearsThis as it seems to me, sorts the issues inside of each collection by product_code, what I need is to somehow sort collections by product_code.
-
Ludwig over 7 yearsI have tried to further explain what I am trying to achieve here.
-
Eric Tucker over 7 yearsSee if my last edit does the trick, it sort everything in
$productCodes
then by date after. -
Ludwig over 7 yearsUnfortunately it doesn't work, I get an error for undefined variable $issue.
-
Ludwig over 7 yearsThis works for getting the magazines with the same product code first in the array, but the rest of the magazines that don't have the same product code are not ordered by date of their latest issue.
-
Ludwig over 7 yearsJust realized that neither the magazines that have the same product code as in the array product codes or the magazines that don't have the same product code are ordered by the date of their latest issue.
-
Paul Spiegel over 7 yearsI hoped, that sorting by latest issue was not a requirement, but just happened :-) - I will look at it later.
-
Eric Tucker over 7 yearsWhoops, edited.
$issue
should be injected into thesortBy
callback. -
Ludwig over 7 yearsThen I get
Undefined property: Illuminate\Database\Eloquent\Collection::$magazine
-
Ludwig over 7 yearsI have tried your updated suggestions but then I get:
Method getKey does not exist.
-
Paul Spiegel over 7 yearsTry
union()
instead ofmerge()
. The keys of the two arrays are distinct, so there shouldn't be a difference. I have no clue, whymerge()
doesn't work here. -
Ludwig over 7 yearsThis seems to be working, but I don't then the image property is empty on the issue object, is that because I have image field in both magazine and issues table?
-
Paul Spiegel over 7 yearsProbably - yes. Add
$baseQuery->select('issues.*');
-
alepeino over 7 years@PaulSpiegel no, the
load
method will fetch all related models in 1 query. So 2 queries total -
Paul Spiegel over 7 yearsI mean the
getLatestIssueDateAttribute()
method. -
Ludwig over 7 yearsI have just tested it and it works as well. Which method would you performance wise recommend yours or Pauls?
-
alepeino over 7 yearsI'm not sure. I find my version easier to read. As for database optimization, this method only needs 2 queries (thanks to Paul's observation about my previous, suboptimal, version). I'd like to read @PaulSpiegel 's opinion on your question as well
-
Eric Tucker over 7 yearsUpdated again,
$issues
is a collection instead of an object after thegroupBy
-
Paul Spiegel over 7 yearsThe update doesn't change the number of queries. The only difference is that the first version (
latest()
) only works with thecreated_at
column. Put this line\DB::listen(function($query) {echo "\n<pre>{$query->sql}</pre>\n";});
in front of your code - it will print every executed sql query. -
alepeino over 7 years@PaulSpiegel It does. I had tested it with
DB::getQueryLog()
. This$this->issues->sortBy...
operates on the collection of issues, which were previously eager loaded, it doesn't query the DB again. -
Paul Spiegel over 7 yearsSorry.. i've overseen this change:
->issues()->
=>->issues->
. However usually sorting in the db should be faster. But for small data sets it might not matter.