Laravel Eloquent relationship with group by attributes from two models

I have two models InvoiceHeader and InvoiceDetail. InvoiceHeader has the following attributes (among others): id, pos_id, pos_description and date. InvoiceHeader has the following attributes (among others): id, invoice_header_id, pct, price. There is a One To Many relationship between the two models.

I would like to group by pos_id and date from InvoiceHeader and by pct from InvoiceDetail. And with that I would like to get the sum of the price.

From what I found it is not possible to group by both models at once since no actual joins are being made with Eloquent. So I figured that I could group by the pct attribute on the InvoiceDetail. I do something like this:

$invoices = \App\InvoiceHeader::with(['details' => function ($query) {
        $query->select('pct')
            ->selectRaw('SUM(price) AS price)
            ->groupBy(['invoice_header_id', 'pct']);
    }])
    ->whereBetween('date', ['2018-01-01', '2018-07-31'])
    ->select(['pos_id', 'pos_description', 'date'])
    ->get();

And it is here that I am stuck. I would need to group by pos_id, pos_description on InvoiceHeader and group by pct on InvoiceDetail.

From here on I am taking a route, which I think is not the best / easiest one. However, since it is the only one that got me a bit closer to what I got I figured I could get on with it. If someone has a better suggestion, please do share.

I make a Laravel collection from the Eloquent collection as such:

$collection = collect();
foreach ($invoices as $invoice) {
    foreach ($invoice->details as $detail) {
        $collection->push([
            'pos_id' => $invoice->pos_id,
            'pos_description' => $invoice->pos_description,
            'date' => $invoice->date,
            'pct' => $detail->pct,
            'price' => $detail->price,
        ]);
    }
}

This brings me to something like

$collection = [
    [
        'pos_id'          => 1,
        'pos_description' => 'Somewhere',
        'date'            => '2018-02-28',
        'pct'             => 6,
        'price'           => 68.94
    ], [
        'pos_id'          => 1,
        'pos_description' => 'Somewhere',
        'date'            => '2018-02-28',
        'pct'             => 21,
        'price'           => 99.99
    ], [
        'pos_id'          => 1,
        'pos_description' => 'Somewhere',
        'date'            => '2018-02-28',
        'pct'             => 21,
        'price'           => 82.64
    ], [
        'pos_id'          => 1,
        'pos_description' => 'Somewhere',
        'date'            => '2018-03-31',
        'pct'             => 21,
        'price'           => 431.45
    ]
]

This is where I want to get at, or something similar:

$newCollection = [
    [
        'pos_id'          => 1,
        'pos_description' => 'Somewhere',
        'date'            => '2018-02-28',
        'details'     => [
            [
               'pct'   => 6,
               'price' => 68.94
            ], [
               'pct'   => 21,
               'price' => 182.63
            ]
        ]
    ], [
        'pos_id'          => 1,
        'pos_description' => 'Somewhere',
        'date'            => '2018-03-31',
        'details'     => [
            [
               'pct'   => 21,
               'price' => 431.45
            ]
        ]
    ]
]

I tried many functions from the laravel docs use each, groupBy, map on the collection. Also got somewhere from this link. But I don't seem to be able to get what I want to accomplish. I feel like I am missing some easy steps which makes me do things too difficult.

Disclaimer: Most of the code is loosely based on working code. To simplify things I simplified some things.

via Chebli Mohamed