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.