I have two tables magazines with a field product_code, and another table issues. They have belongsToMany 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 each collection of issues belongs to one magazine. 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 table issues, 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 the productCodes array in the table magazines where I have the product_code field. The collections of issues grouped by magazine, should be then sorted so that the first collections are the ones whose magazine that they belong to has the same product_code as the code in the array productCodes, 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.
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');
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;
});
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:
idproduct_code is among the requested codes, then the date of the latest Issue (*)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;
}