Generate CSV file for download and show waiter until ready

Maybe returning a streamed response ?

http://laravel.com/api/5.0/Illuminate/Contracts/Routing/ResponseFactory.html#method_stream

@pmall, I'm new to laravel, so don't now exactly how that works. So what does this stream response, can't find it in the laravel response documentation (http://laravel.com/docs/5.0/responses)

Yes there is not much documentation on this topic. Im not even sure it is suited for the situation.

What I think is strange is your csv takes 10 seconds to generate. Why ?

@pmall, that is something I'm also thinking about. But for small csv files (~200 lines) it is just done within a split second. But some files can contain 50.000 lines. Also the query has to join on three other tables. I haven't find out if the query is taking so long or generating the result. Need to sort out that first because I noticed that the script is also using a lot of resources, about 300MB!!!

I had the same problem on an app of mine (CSV with 80,000+ rows to generate). Sounds like you should try to leverage Queue Jobs on that one.

Basically:

• You send an ajax request to the server to create a queue job that will generate the CSV
• When the job is ready, use a service like Pusher to notify the user and show him the link to download the CSV

This way, the user can continue doing something else until the document is ready.

I had exactly the same problem but although I would prefer to use a queued jb in this instance it wasn't right so I wrote this, not the most elegant but as long as you got JSON being returned then it works.

@section('modalContent')
    <div id="modal-loader" class="modal fade" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true" role="dialog">
        <div class="modal-dialog">
            <div class="modal-content text-center">
                <div class="modal-header text-center">
                    <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
                    <h3 class="modal-title" id="myModalLabel"></h3>
                </div>
                <div class="modal-body">
                    <div class=" loader">
                        <img src="{!! asset('img/preloader.gif') !!}">
                        <p class="spacer-top-2x">This may take up to a minute or 2.</p>
                    </div>
                    <div class="response"></div>
                </div>
                <div class="modal-footer text-center"></div>
            </div>
        </div>
    </div>
@stop

@section('scripts')
    <script>
        $('form').on("submit", function (event) {
            event.preventDefault();
            $.ajax({
                type: "POST",
                url: '{!! URL::route('reports.postReport') !!}',
                data: $(this).serialize(),
                dataType: "json",
                timeout: 0,
                beforeSend: function () {
                    $('#modal-loader h3.modal-title').html('Generating your report...');
                    $('#modal-loader').modal();
                },
                complete: function () {
                    $('#modal-loader .loader').hide();
                },
                success: (function (data) {
                    $('#modal-loader h3.modal-title').html('VanDabbyDozy!');
                    $('#modal-loader div.response').html('<p class="lead ">Your report has been successfully created.</p><div class="download spacer-top-3x "><a class="report-link" href="{!! asset('reports/exports') !!}/' + data.file + '"><i class="fa fa-download fa-5x"></i></a></div>');
                    $('a.report-link').on('click', function (event) {
                        $('div.modal-footer').html('<a class="btn btn-danger" href="{!! URL::route('reports.adhoc') !!}">Run Another Report</a>');
                    });
                    console.log(data.statusCode);
                    console.log(data.statusText);
                }),
                error: (function (response) {
                    $('#modal-loader h3.modal-title').html('Houston, we have a problem!');
                    $('#modal-loader div.response').html('<div class="alert alert-danger"><strong>Error</strong>: ' + response.status + ' (' + response.statusText + ')</div><p>Your report has not been created...');
                    $('div.modal-footer').html('<a class="btn btn-danger" href="{!! URL::route('reports.adhoc') !!}">Run Another Report</a>');
                    console.log(response.statusCode);
                    console.log(response.statusText);
                })
            })
        });
    </script>
@stop 

@foxted, I think that is a good way to solve this issue. Is it also possible to limit the amount of resources the script uses? I actually don't want every script to be able to take 512MB of RAM. If the job is done in the back, it also doesn't matter if it takes 30 seconds to complete. Edit: About the push service, I don't think I want to use a 3rd party service for pushing such notifications. I could set a session when generating the CSV file is ready (or something like that). An AJAX call can check if the session is set for every 5 or 10 seconds.

@bencarter78, Looks nice, maybe a way to do a quick fix!

You should be able to set the memory limit on your worker.

artisan queue:listen has a memory limit setting for example:

Usage:
 queue:listen [--queue[="..."]] [--delay[="..."]] [--memory[="..."]] [--timeout[="..."]] [--sleep[="..."]] [--tries[="..."]] [connection]

Arguments:
 connection            The name of connection

Options:
 --queue               The queue to listen on
 --delay               Amount of time to delay failed jobs (default: 0)
 --memory              The memory limit in megabytes (default: 128)
 --timeout             Seconds a job may run before timing out (default: 60)
 --sleep               Seconds to wait before checking queue for jobs (default: 3)
 --tries               Number of times to attempt a job before logging it failed (default: 0)
 --help (-h)           Display this help message
 --quiet (-q)          Do not output any message
 --verbose (-v|vv|vvv) Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
 --version (-V)        Display this application version
 --ansi                Force ANSI output
 --no-ansi             Disable ANSI output
 --no-interaction (-n) Do not ask any interactive question
 --env                 The environment the command should run under.

EDIT: You could also use a Push Queue service like Iron.io if you want to move your workers out of your server.

@postitief where does the results variable comes from ? is it its creation that takes so much time and resources ?

Im sure there is a way to stream the download maybe someone is more experimented with this.

@pmall, the CSV file being downloaded is actually a copy of a result in a dataTables. I know there are plugins like TableTools for dataTables to download the result. But this one is working with flash, and I don't want to use flash. So I wrote something my own, pretty simple. I'm abusing (don't know if it's the right term) the datatables to generate the result. But instead of returning the result to the dataTable, put the result in a CSV file. The datatable is also doing some filtering and I'm editing some columns. Maybe this is not the right way to do this.

If I just run the query to get al 45.000 rows (~1.000.000 rows in table), it runs in 10ms (in Sequal Pro). So I don't think the query is the issue but the abuse of the dataTables, specially, this part of the code.

$result = Datatables::of($transactions)->make();

I think I have to remove the use of dataTabes.

Are you using eloquent ? What are the queries ?

Generating a lot of eloquent objects can cause trouble.

I'm not using eloquent, but I don't know if datatables does.

Below the whole code in the download method.

$activeSession = Session::get('relatieId');
$betaalmiddelen_id = Session::get('relatieBmId');

# Query klaarmaken
$tanktransacties = DB::table('tanktransacties')
    ->select(array( 'tanktransacties.datumtijd AS datum',
        'tanktransacties.datumtijd AS tijd',
        'betaalmiddelen.tankpas',
        'betaalmiddelen.referentie',
        'tanktransacties.kenteken',
        'stations.naam AS station',
        'producten.omschrijving AS product',
        'tanktransacties.aantal',
        'tanktransacties.nettoprijs',
        'tanktransacties.nettoprijs_incl',
        'tanktransacties.regelprijs',
        'tanktransacties.regelprijs_incl'))
    ->leftJoin('stations', 'stations.id', '=', 'tanktransacties.stations_id')
    ->leftJoin('producten', 'producten.id', '=', 'tanktransacties.producten_id')
    ->leftJoin('betaalmiddelen', 'betaalmiddelen.id', '=', 'tanktransacties.betaalmiddelen_id')
    ->where('tanktransacties.relaties_id','=', $activeSession)
    ->whereBetween('tanktransacties.status', [10, 90]);

# Alleen als betaalmiddelen_id > 0 is, dan ook match op dit id.
if($betaalmiddelen_id > 0){
    $tanktransacties->where('tanktransacties.betaalmiddelen_id', '=', $betaalmiddelen_id);
}

//        dd($tanktransacties->toSql());

# Datatable terugsturen op basis van bovenstaan de query.
$result = Datatables::of($tanktransacties)
    ->editColumn('datum', function($data){ return date("d-m-Y", strtotime($data->datum)); })
    ->editColumn('tijd', function($data){ return date("H:i", strtotime($data->datum)); })
    ->editColumn('aantal', function($data){ return number_format($data->aantal, 2, ',', '.');})
    ->editColumn('nettoprijs', function($data){ return number_format($data->nettoprijs, 2, ',', '.');})
    ->editColumn('nettoprijs_incl', function($data){ return number_format($data->nettoprijs_incl, 2, ',', '.');})
    ->editColumn('regelprijs', function($data){ return number_format($data->regelprijs, 2, ',', '.');})
    ->editColumn('regelprijs_incl', function($data){ return number_format($data->regelprijs_incl, 2, ',', '.');})
    ->make();

$filename = "file.csv";
$handle = fopen($filename, 'w+');
fputcsv($handle, array("Datum", "Tijd", "Kaart", "Referentie", "Kenteken", "Station", "Product", "Aantal", "Prijs per 100 ex", "Prijs per 100 incl", "Bedrag ex", "Bedrag incl"), ";",'"');

foreach($result->getData() as $key => $rows){
    if($key == "data") {
        foreach($rows as $type => $row){
            fputcsv($handle, $row, ";",'"');
        }
    }
}
fclose($handle);
$headers = array(
    'Content-Type' => 'text/csv',
);
return response()->download($filename, 'Tanktransactie overzicht '.date("d-m-Y H:i").'.csv', $headers);