title: Laravel Eloquent relationship with group by attributes from two models
body: <p>I have two models <code>InvoiceHeader</code> and <code>InvoiceDetail</code>. <strong>InvoiceHeader</strong> has the following attributes (among others): <code>id</code>, <code>pos_id</code>, <code>pos_description</code> and <code>date</code>. <strong>InvoiceDetail</strong> has the following attributes (among others): <code>id</code>, <code>invoice_header_id</code>, <code>pct</code>, <code>price</code>. There is a One-to-Many relationship between the two models. </p>
  
  <p>I would like to group by <code>pos_id</code> and <code>date</code> from <code>InvoiceHeader</code> and by <code>pct</code> from <code>InvoiceDetail</code>. And with that, I would like to get the sum of the price.</p>
  
  <p>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 <code>pct</code> attribute on the <code>InvoiceDetail</code>. I do something like this:</p>
  
  <pre><code>$invoices = \App\InvoiceHeader::with(['details' =&gt; function ($query) {
          $query-&gt;select('pct')
              -&gt;selectRaw('SUM(price) AS price')
              -&gt;groupBy(['invoice_header_id', 'pct']);
      }])
      -&gt;whereBetween('date', ['2018-01-01', '2018-07-31'])
      -&gt;select(['pos_id', 'pos_description', 'date'])
      -&gt;get();
  </code></pre>
  
  <p>And it is here that I am stuck. I would need to group by <code>pos_id</code>, <code>pos_description</code> on <code>InvoiceHeader</code> and group by <code>pct</code> on <code>InvoiceDetail</code>.</p>
  
  <p>From here on I am taking a route, which I think is not the best / easiest one. I have an Eloquent collection of <strong>InvoiceHeader</strong> with a relation to its <strong>InvoiceDetail</strong> collection. I feel like I should work on this, but since I can't figure out how I transform it into a Laravel collection. 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, however, please do share. Until then, here is what I got.</p>
  
  <p>I make a Laravel collection from the Eloquent collection as such:</p>
  
  <pre><code>$collection = collect();
  foreach ($invoices as $invoice) {
      foreach ($invoice-&gt;details as $detail) {
          $collection-&gt;push([
              'pos_id' =&gt; $invoice-&gt;pos_id,
              'pos_description' =&gt; $invoice-&gt;pos_description,
              'date' =&gt; $invoice-&gt;date,
              'pct' =&gt; $detail-&gt;pct,
              'price' =&gt; $detail-&gt;price,
          ]);
      }
  }
  </code></pre>
  
  <p>This brings me to something like </p>
  
  <pre><code>$collection = [
      [
          'pos_id'          =&gt; 1,
          'pos_description' =&gt; 'Somewhere',
          'date'            =&gt; '2018-02-28',
          'pct'             =&gt; 6,
          'price'           =&gt; 68.94
      ], [
          'pos_id'          =&gt; 1,
          'pos_description' =&gt; 'Somewhere',
          'date'            =&gt; '2018-02-28',
          'pct'             =&gt; 21,
          'price'           =&gt; 99.99
      ], [
          'pos_id'          =&gt; 1,
          'pos_description' =&gt; 'Somewhere',
          'date'            =&gt; '2018-02-28',
          'pct'             =&gt; 21,
          'price'           =&gt; 82.64
      ], [
          'pos_id'          =&gt; 1,
          'pos_description' =&gt; 'Somewhere',
          'date'            =&gt; '2018-03-31',
          'pct'             =&gt; 21,
          'price'           =&gt; 431.45
      ]
  ]
  </code></pre>
  
  <p>This is where I want to get at, or something similar:</p>
  
  <pre><code>$newCollection = [
      [
          'pos_id'          =&gt; 1,
          'pos_description' =&gt; 'Somewhere',
          'date'            =&gt; '2018-02-28',
          'details'     =&gt; [
              [
                 'pct'   =&gt; 6,
                 'price' =&gt; 68.94
              ], [
                 'pct'   =&gt; 21,
                 'price' =&gt; 182.63
              ]
          ]
      ], [
          'pos_id'          =&gt; 1,
          'pos_description' =&gt; 'Somewhere',
          'date'            =&gt; '2018-03-31',
          'details'     =&gt; [
              [
                 'pct'   =&gt; 21,
                 'price' =&gt; 431.45
              ]
          ]
      ]
  ]
  </code></pre>
  
  <p>I tried many functions from the <a href="https://laravel.com/docs/master/collections" rel="nofollow noreferrer">laravel docs</a> use <code>each</code>, <code>groupBy</code>, <code>map</code> on the collection. Also got somewhere from <a href="https://trueagency.com.au/insights/code/laravel-collection-simplify-your-data/" rel="nofollow noreferrer">this link</a>. 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 make me do things too difficult. </p>
  
  <p>Disclaimer: Most of the code is loosely based on working code. To simplify things I simplified some things. </p>