Crud: [Bug] Export funcionality only exports the visible rows on ajax tables.

[alexgmin]

On non-ajax tables it works fine.
Another thing is that the actions column probably shouldn't be on the export result by default, since the user doesn't normally want the text of some buttons on the exportation.

[tabacitu]

Damn. You're right. I do think making the export work for AJAX tables would be A LOT of work and basically involve wither rewriting the functionality or coding an alternative one just for the AJAX tables.

Will check and come back to you.

Thanks!

[OwenMelbz]

I'm just going to close this up for the time being to keep the place smaller, its been added to this list https://github.com/Laravel-Backpack/CRUD/issues/285

[tabacitu]

I've given this some thought, and I can't find a non-breaking solution. I guess this just needs to be a documented caveat of AjaxDataTables. ANOTHER documented caveat, until we rewrite ajaxdatatables and switch to it entirely.

[b8ne]

I was just doing some datatables work and came across this, havent tested anything but had a thought.

1. Create a method based off search() but returns a json object. Maybe search() does this already? I havent checked.
2. Access the new method via ajax.
3. In list.blade.php add

exportOptions: {
    columns: [':visible'],
    format: {
        body: function ( data, row, column, node ) {
            // ACCESS JSON MODEL BASED OFF ROW AND COL
            return result;
        }
    }
}

Thoughts?

[tabacitu]

Hi @b8ne ,

Hmm... sounds like a very interesting solution. I'd love it if it works. I'll schedule time to prototype it - see if it works. Thank you, I'll reopen the issue for this.

Cheers!

[AbbyJanke]

This is actually an issue with dataTables as well. We had a solution for it. See issue #714

[lloy0076]

If someone submits a PR, I suggest reopening this issue 😄

[lloy0076]

Per comment I've reopened.

[tabacitu]

Hi guys,

Following up on this, I concluded that search() does return a JSON, so bene's solution wouldn't provide better performance than a regular AJAX call. In the end, in order to export all rows, you'll need all rows :-)

So I think the simplest solution to this is the best - when picking how many rows are shown on page, have an "All" option. This would be a very slow page for huge dbs, obviously, but it would provide intuitivity for the Export buttons:

• export buttons usually export what you see right now; so for example if you only want to export a search result, or a certain page of the results, you can do that;
• using the export buttons you _should_ be able to export all results, including using AjaxDataTables, but in order to do that, you need to show "All" results per page; even if a user doesn't know that, I bet he'll figure it out when he takes a look at his export and sees just 20 results instead of 2000; the solution should be intuitive - go to the same page, pick "All" results per page, click export again;

I've already pushed this solution into dev - https://github.com/Laravel-Backpack/CRUD/commit/ef863f1c05b983dd1c52f406281b741e95cc1b25, so I'll close this issue. Please let me know if you don't agree by reopening the issue and commenting.

Thanks! Cheers!

[AbbyJanke]

@tabacito this was something we discussed in the gitter channel today actually, and i had suggested that perhaps a new button be included called "export" so instead of just exporting all visible, it exports all the data by calling a new URL that forces a download. Thoughts?

[lloy0076]

The problem with an "All" button is that it might break the browser (IE was infamous for crashing on large datasets, I doubt that Edge is any better and although it seems that FireFox and Chrome cope, it's still silly).

Is there a way to do the export with the data without having to send it to the client to render?

[CHOMNANP]

Well, I am curious how to get this work as well :D

[tabacitu]

@AbbyJanke that's totally an option. If someone doesn't like DataTable's export buttons, he can always create a button called "Export", that does whatever he wants it to do... In most custom settings, in fact, I think that's faster than bending DataTables export to your will.

@lloy0076 - true. It couls be pretty hit-and-miss for millions of entries, because of some browsers. Can't test in IE/Edge myself but it wouldn't surprise me :-) I don't think there's a way without rendering - the problem isn't actually the rendering, it's the fact that it needs all that information onpage, since the files are generated with JavaScript. So the ajax call still needs to be made, for ALL entries... Come to think about it, that would be a problem for a PHP script too. If the DB is large, it would still take a lot of time to generate the file. So you'd need a "loading" spinner...

I think exporting millions of entries is a bit beyond the scope of Backpack. Sure, it would be great to have it, but I don't see how we can do that with the tools at hand. We could choose to code an export functionality ourselves, and it would be top-notch, with backend rendering not front-end, but it would be a huge effort. I think that can come in v4 or v5, when we decide to ditch DataTables altogether. I expect we'll outgrow it at some point, and I bet we can code something simpler and better using Vue or Angular.

[lloy0076]

@tabacitu said:

I expect we'll outgrow it at some point, and I bet we can code something simpler and better using Vue or Angular.

Something better could be written in both Vue or Angular - true. I'm not sure an Angular version would be simpler though.

[tabacitu]

@CHOMNANP it's now launched, so a composer update should get it for you.

[Spodnet]

I know a few people have come across the issue of not exporting more than the current page, my solution was to override the exportData function in datatables, then pass in the All page size to fetch the data.

@if ($crud->exportButtons())
              // fix export
              $.fn.DataTable.Api.register( 'buttons.exportData()', function ( options ) {
                  if ( this.context.length ) {
                      var data = table.ajax.params();

                      // set length to all
                      data.length = -1;

                      var jsonResult = $.ajax({
                          url: "{!! url($crud->route.'/search') . '?' . Request::getQueryString() !!}",
                          method: 'POST',
                          data: data,
                          async: false
                      });

                      var headings = $("#crudTable thead tr th")
                          .map(function() {
                              return this.innerText;
                          }).get();

                      return {
                          body: jsonResult.responseJSON.data,
                          header: headings
                      };
                  }
              });
          @endif

This needs to be defined before the datatable is initialised. I've not passed this onto the testers yet, but initial tests by me look to be exporting all data correctly

[tabacitu]

@Spodnet that sounds great!! Let me know what your testers find - if this works as expected, I’d love to have it as default.

[tabacitu]

@Spodnet what happened to your solution - did you end up using it as-is? My tests show that there are still problems with this:

• for large data sets, the export still fails; it’s js after all;
• the URL did not take into account any filters applied after the page is loaded, but that’s an easy fix (below);
• the URL did not reset the start - if you were on a subsequent page it started from there; easy fix below;
• [this is a BIG one] the results are HTML, and they are no longer parsed; so the export will actually print the HTML tags, which I bet is something that nobody wants or expects;

So right now, this doesn’t look usable to me. I’m not sure a 100% solution with DataTables is even possible - I think the best we can do is move the export functionality to PHP. That will would allow us to make huge files fast, then deliver them to the user. That’s a lot of work, but… I don’t see any other way.

    // force export buttons to pull in all filtered entries, not just the current page
    $.fn.DataTable.Api.register( 'buttons.exportData()', function ( options ) {
        if ( this.context.length ) {
            var data = crud.table.ajax.params();

            // set length to all
            data.length = -1;
            data.start = 0;

            var jsonResult = $.ajax({
                url: crud.table.ajax.url(),
                method: 'POST',
                data: data,
                async: false
            });

            var headings = $("#crudTable thead tr th")
                .map(function() {
                    return this.innerText;
                }).get();

            return {
                body: jsonResult.responseJSON.data,
                header: headings
            };
        }
    });