Complex Query for a Report

Don't make things harder than they have to be read this: http://laravel.io/forum/04-04-2015-looping-through-collection I've run accounts receivable an accounts payable reports this way.

So using the example from your link will this not cause another SQL Query call to the database for Category each time it goes through the loop?

@foreach($cats as $cat) {{Category::where('id', '=', $cat->category_id)->first()->cat_name}}QTY @foreach($items as $item) @if($item->category_id == $cat->category_id) {{$item->product_id}} - {{$item->prod_name}} {{$item->total}} @endif @endforeach  Total - {{$cat->total}} @endforeach

Since I need to get the License Group for the Agency that means I need to go through Agency->orders->orderdetails->license->licensegroup->name, then afterthat spit out the orderdetails that pertain to that license group. Sample report below.

ACME REPORT PAGE 1
DATE: 6/24/2015 License Issued Report
{{ $agency->name }} = AGENCY 1
{{ $licenseGroup->name }} = BINGO

All the bingo orders for that agency
License Number = {{ $orderDetail->license_number }}
Licensee Name = {{ $orderDetail->order->organization->name }}
Address = {{ $orderDetail->order->organization->address1 }}
...
Total Fee = {{ $orderDetail->fee }}


ACME REPORT PAGE 2
DATE: 6/24/2015 License Issued Report
{{ $agency->name }} = AGENCY 1
{{ $licenseGroup->name }} = SMALL GAMES

All the small game orders for that agency
License Number = {{ $orderDetail->license_number }}
Licensee Name = {{ $orderDetail->order->organization->name }}
Address = {{ $orderDetail->order->organization->address1 }}
...
Total Fee = {{ $orderDetail->fee }}



Then this repeats for the next agency,

A little stuck.

It is no different than pagination each time you paginate a new page you're making a call to the database again. Imagine a large database where you're doing accounts receivable with many many customers it is more efficient to loop through the database and deal with one customer at a time that's the way it works in other words in your foreach you're only dealing with the current customer at a time or rather current line items at a time you're not dealing with all records at one time and some of the eloquent queries you actually have to query the whole database everything first and then pass it to the view. The way I do it is query as you go similar to pagination, only dealing with a few records at a time.
Yes even with an eloquent query you still foreach but that foreach already has all database records and I try to avoid that. I guess you can say its query / retrieve as needed as you go.
This has been kinda a problem with MVC and reports having to query for many records which is stored in memory then foreach through them.
For a report I like to retrieve first just that section of data I need then foreach through it.

@jlrdw do you have a sample of one of your accounts receivable. I also might have over several thousands of records I need to return as well if someone runs a report on the entire database. Still kinda new with Laravel and having a hard time trying to figure this out.

No different from the example above except of course I order by company, then the details by date of invoice. Remember we are talking reports. I know there are other ways of doing this but this technique works for me looping as you go. For year end reports I cheated I would dump everything to local computer use my local environment MySQL I had a odbc source, and I ran year-end reports using Microsoft Access which made for real good reports for printing.

Also if you have many of thousands at one time which I did not you may have to look into the docs and look up chunk. There's also reporting addons you can use with laravel, but for me I am NOT going to use an add on when I can simply write my own reports it's just some basic looping some CSS and some HTML to place things on the page where I need them.
I've even seen with some people download and use addons for tables like data tables I can write my own html table.
You have to remember Visual FoxPro which I used years back, access, and dbase could deal with thousands and thousands of records at one time whereas online you cannot deal with that many efficiently you have to do it in chunks.
Me, if I had that many I would do a through c, d through f, etc. A lot of this is just common sense sometimes there are several ways of doing it.
But for thousands you will have to do only so many at a time not all at one time.

So giving the example from http://laravel.com/docs/5.1/eloquent#retrieving-multiple-models Chunk the orderDetails?

OrderDetail::chunk(500, function ($orderDetails) {
    foreach ($orderDetails as $orderDetail) {
        //
    }
});

I have to apologize here I have never used chunk I do it the way I showed but see if there's a laracast on using chunk.

@jlrdw I have solved my problem. Thank you very much for your help. Here is what I did in my view, i hope it might help someone else

 @foreach($agencies as $agency)
    @foreach($licenseGroups as $licenseGroup)
        @foreach($licenseGroup->licenses as $license)
                        <?php $orderDetails = \App\OrderDetail::with([
                            'order.person.state',
                            'order.organization.state'
                            ])
                            ->whereHas('license', function ($q) use ($license)
                            {
                                $q->whereId($license->id);
                            })
                            ->whereHas('order.agency', function ($q) use ($agency)
                            {
                                $q->whereId($agency->id);
                            })
                            ->whereBetween('created_at', [\Carbon\Carbon::parse($startDate), \Carbon\Carbon::parse($endDate)])
                            ->get(); ?>
               @foreach($orderDetails->sortBy('license_number') as $orderDetail)
               {{ out the data I needed }}
              @endforeach
         @endforeach
     @endforeach
@endforeach