Laravel - Group By & Key By together

Question

Assuming I have the following MySQL tables to represent pricebooks, items and the relationship between them:

item - item_id|name|...etc
pricebook - pricebook_id|name|...etc

and the following pivot table

pricebook_item - pricebook_id|item_id|price|...etc

I have the correlating Eloquent models: Pricebook, Item and a repository named PricebookData to retrieve the necessary information.

Within the PricebookData repository, I need to get the pricebook data grouped by pricebook id and then keyed by item_id for easy access on client side.

If I do:

Pricebook::all()->groupBy('pricebook_id');

I get the information grouped by the pricebook_id but inside each pricebook the keys are simple numeric index (it arrives as js array) and not the actual product_id. So when returning to client side Javascript, the result arrives as the following:

pricebookData: {1: [{}, {}, {}...], 2: [{}, {}, {}...]}

The problem with the prices arriving as array, is that I can not access it easily without iterating the array. Ideally I would be able to receive it as:

 pricebookData: {1: {1001:{}, 1002: {}, 1003: {}}, 2: {1001:{}, 1002: {}, 1003: {}}}
//where 1001, 1002, 1003 are actual item ids
//with this result format, I could simply do var price = pricebookData[1][1001]

I've also tried the following but without success:

Pricebook::all()->keyBy('item_id')->groupBy('pricebook_id');

The equivalent of what I am trying to avoid is:

$prices = Pricebook::all();
$priceData = [];
foreach ($prices as $price) 
{
   if (!isset($priceData[$price->pricebook_id])) 
   {
      $priceData[$price->pricebook_id] = [];
   }
   $priceData[$price->pricebook_id][$price->item_id] = $price;
}
return $priceData;

I am trying to find a pure elegant Eloquent/Query Builder solution.

Answer 1

I think what you want is

Pricebook::all()
    ->groupBy('pricebook_id')
    ->map(function ($pb) { return $pb->keyBy('item_id'); });

You first group by Pricebook, then each Pricebook subset is keyed by item_id. You were on the right track with

Pricebook::all()->keyBy('item_id')->groupBy('pricebook_id');

unfortunately, as it is implemented, the groupBy resets previous keys.

Update:

Pricebook::all()->keyBy('item_id')->groupBy('pricebook_id', true);

(groupBy second parameter $preserveKeys)