Using datatables in octobercms as server side way

Question

I installed datatables in a page of octobercms. But the data is too large so the rendering of datatables is too slow. Datatables suggests that I should use server-side processing, but I don't know how to.

In octobercms, the data is passed through the builderlist.

{% set records = builderList.records %}

Then, I have the html codes to make the table.

<table id="datalist" class="table table-striped mb-3" style="width:100%;">
        <thead>
            <tr>
                <th>app Id</th>
                <th>app Name</th>
                <th>app Ver.</th>
                <th>BV</th>
                <th>Process</th>
                <th>SCRIPT</th>
                <th>Custom</th>
                <th>OS</th>
                <th>Period</th>
            </tr>
        </thead>

        <tbody>
         {% for record in records %}
            <tr>
                <td>{{ record.app_id }}</td>
                <td>
                {% if detailsPage %}
                    <a href="{{ detailsPage|page({ (detailsUrlParameter): attribute(record, detailsKeyColumn) }) }}">
                {% endif %}

                {{ attribute(record, displayColumn) }}

                {% if detailsPage %}
                    </a>
                {% endif %}
                </td>
                <td>{{ record.app_ver }}</td>
                <td>{{ record.byapps_ver }}</td>
                <td>{{ record.app_process }}</td>
                <td>{{ record.app_build }}</td>
                <td>{{ record.app_os_type == 'both' ? 'and+ios' : record.app_os_type }}</td>
                <td>{{ func.dateFormat(record.reg_time) }}</td>
            </tr>

         {% else %}
        <li class="no-data">{{ noRecordsMessage }}</li>
        {% endfor %}
        </tbody>
    </table>

Then here comes the datatable script.

<script type="text/javascript">

$(document).ready(function() {

  var tableId = {{ tableName|json_encode()|raw  }};
  var table = $('#' + tableId).DataTable({
        "paging": true,
        "pageLength": 50,
        "info": false,
        "autoWidth": true,
        "fixedHeader": false,
        "responsive": true,
        "orderClasses": false,
        "dom": 'Bfrtip',
        "buttons": [
            'excel', 'copy'
        ],
      });

How can I change these codes to server-side way? Where do I use the ajax? Please, help me !_!

Pettis Brandon Pettis Brandon · Accepted Answer · 2019-07-24T18:55:43

Well looking into this I do get better response times if I convert my data to their ajax / json feed. I have yet to test with server side processing.

October has a neat route capability that turns the returned data to json (I call it being restful by nature). You can learn more about that here.

To do this you create a routes.php file in your plugin's registration directory.

Here is my route using the destination of "api/products". Also notice the returned array with [ 'data' => [ items ] ] is required by DataTables:

<?php

use Author\Plugin\Models\Product;

Route::get('api/products', function(Request $req) {
$products = Product::all();
foreach ($products as $product) {
    $json = [ $product->product_name, $product->item_number ];
    $product->json = $json;
}
    return [ 'data' => $products->pluck('json') ];
});

Here is an example of the json code it spits out.

{"data":[
    ["Dometic AC Capacitor #3100236169","3100236169"],
    ["3\/4\" Universal Transition #DLE365D2","DLE365D2"]
]}

Here is what my CMS Page looks like now. Pay attention that the route address matches the destination you are loading into the javascript; "example.com/route" or in this case I used "example.com/api/products"

<div>
    <table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Item Number</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Name</th>
                <th>Item Number</th>
            </tr>
        </tfoot>
    </table>
</div>

<script>
$(document).ready(function() {
    $('#example').DataTable( {
        "ajax": "https://example.com/api/products"
    } );
} );
</script>

Using datatables in octobercms as server side way

1 Answers