Notice: this article is updated in October 2019, for latest version of QuickAdminPanel generated code.

Our QuickAdminPanel relies on Datatables.net library to show the data. It has quite a lot of settings, options and configurations, so in this article I will show how to use them after downloading your panel.

What is covered in this article:

  1. Edit/Remove Buttons like Copy/CSV/Excel/PDF/Print/Column Visibility
  2. Add/Customize Table Columns: Show ID and Timestamps
  3. Customize Export Columns for PDF/CSV/Excel
  4. Customize Pagination Options
  5. Change Your Language or Translate Some Text Phrases
  6. Change Ordering by Columns

Disclaimer: we will use a panel with our AJAX Datatables module, that uses server-side rendering. If you’re not familiar with it, read about this module and/or watch this video.

Our starting point is a downloaded fresh admin panel with Users list.

For non-QuickAdminPanel users: if you just want to know Datatables tips from this article, then you can download the starting project adminpanel here, install it locally and follow along.

Ok, let’s begin.

For a better demonstration, I’ve added 50 users with “Simple user” role, in database/seeder/UserSeed.php:

factory(App\User::class, 50)->create()->each(function ($u) {
    $u->role()->attach([2]);
});

Here’s how it looks:


Now, let’s change some settings to have different view.

1. How to Edit/Remove Buttons like Copy/CSV/Excel/PDF/Print/Column Visibility

I’m talking about these buttons, see above. How to manage them?

Let’s go to this file: resources/views/layouts/admin.blade.php

Here we see the datatables parameters, and bolded is the array responsible for those buttons:

$.extend(true, $.fn.dataTable.defaults, {
    < ... >

    dom: 'lBfrtip<"actions">',
    buttons: [
        {
            extend: 'copy',
            text: window.copyButtonTrans,
            exportOptions: {
                columns: ':visible'
            }
        },
        {
            extend: 'csv',
            text: window.csvButtonTrans,
            exportOptions: {
                columns: ':visible'
            }
        },
        {
            extend: 'excel',
            text: window.excelButtonTrans,
            exportOptions: {
                columns: ':visible'
            }
        },
        {
            extend: 'pdf',
            text: window.pdfButtonTrans,
            exportOptions: {
                columns: ':visible'
            }
        },
        {
            extend: 'print',
            text: window.printButtonTrans,
            exportOptions: {
                columns: ':visible'
            }
        },
        {
            extend: 'colvis',
            text: window.colvisButtonTrans,
            exportOptions: {
                columns: ':visible'
            }
        },
    ]
};

So, if you want to get rid of those buttons, you can empty that array:

$.extend(true, $.fn.dataTable.defaults, {
    < ... >
    buttons: []
};

The result table will look like this:

You can also remove buttons from array one by one, rearrange them to change the order etc.

If you want to change the button texts (or translate them), you should go to the same file resources/views/layouts/admin.blade.php, a little higher up:

<script>
  let copyButtonTrans = '{{ trans('global.datatables.copy') }}' // Translations in JavaScript
  let csvButtonTrans = '{{ trans('global.datatables.csv') }}'
  let excelButtonTrans = '{{ trans('global.datatables.excel') }}'
  let pdfButtonTrans = '{{ trans('global.datatables.pdf') }}'
  let printButtonTrans = '{{ trans('global.datatables.print') }}'
  let colvisButtonTrans = '{{ trans('global.datatables.colvis') }}'
</script>

As you can see, you have the texts in translation files, so to change the actual translations – go to resources/lang/[language]/global.php:


Finally, you can build your own custom buttons – check Datatables.net official documentation on this.

2. How to Add/Customize Table Columns: Show Timestamps

The most common example for our customers is request to show more columns. By default, we hide columns like timestamps – created_at/updated_at. So what if you want to show them? There are a few steps:

Showing fields in resources/views/users/index.blade.php

Here’s how default table look there:

...
<th>
    {{ trans('cruds.user.fields.roles') }}
</th>

<th>
    {{ trans('cruds.user.fields.created_at') }}
</th>
<th>
    {{ trans('cruds.user.fields.updated_at') }}
</th>

<th>
     
</th>
@stop

@section('javascript') 
...
{ data: 'email', name: 'email' },
{ data: 'email_verified_at', name: 'email_verified_at' },
{ data: 'roles', name: 'roles.title' },
{ data: 'created_at', name: 'created_at' },
{ data: 'updated_at', name: 'updated_at' },

Next – let’s add those columns to be returned from the query.
It’s in controller file’s index() method – app/Http/Controllers/Admin/UsersController.php – line 62:

$table->editColumn('created_at', function ($row) {
    return $row->created_at ? $row->created_at : "";
});
$table->editColumn('updated_at', function ($row) {
    return $row->updated_at ? $row->updated_at : "";
});

Result:


3. How to Customize Export Columns for PDF/CSV/Excel

The most typical change would be to specify the columns for the export. Luckily, all those three buttons above follow the same parameters.

By default, they export all visible columns from the table:

{
    extend: 'csv',
    text: window.csvButtonTrans,
    exportOptions: {
        columns: ':visible'
    }
},

But you can change the columns, by specifying their numbers, what to export:

{
    extend: 'csv',
    text: window.csvButtonTrans,
    exportOptions: {
        columns: [1, 2, 3], // Only name, email and role
    }
},

Another thing you can do is to customize the output for each column.
For example, you want to force that column to have first letter uppercase:

exportOptions: {
    columns: [1, 2, 3],
    format: {
        body: function ( data, row, column, node ) {
            return column === 0 ?
                data.charAt(0).toUpperCase() + data.slice(1) :
                data;
        }
    }
}


Notice that in this example the visible columns are 1, 2 and 3, but the uppercased column is the first one, with index 0. So numbering columns for formatting comes after the column visibility filter.

4. How to Customize Pagination

By default, Datatables “Show X entries per page” dropdown comes with 4 values (10, 25, 50, 100) and default value of 100, so we override that to 25 in our generated code, and also override values for this dropdown:

let dtOverrideGlobals = {
    // ...
    pageLength: 25,
    lengthMenu: [ [10, 25, 50, -1], [10, 25, 50, "All"] ]
};

You can also override the values for this dropdown. Look at this code:

It will produce this result, with 25 as default value, and “All” option represented by “-1” value.

You can even disable the functionality of changing the page length, so leave it as 100 by default:

let dtOverrideGlobals = {
    // ...
    "lengthChange": false,
    // ...

You can also customize the buttons in the footer. By default, they look like this:

There is a property called pagingType:

let dtOverrideGlobals = {
    // ...
    "pagingType": "full_numbers",
    // ...

Here are possible values:

  • numbers – Page number buttons only (1.10.8)
  • simple – ‘Previous’ and ‘Next’ buttons only
  • simple_numbers – ‘Previous’ and ‘Next’ buttons, plus page numbers
  • full – ‘First’, ‘Previous’, ‘Next’ and ‘Last’ buttons
  • full_numbers – ‘First’, ‘Previous’, ‘Next’ and ‘Last’ buttons, plus page numbers
  • first_last_numbers – ‘First’ and ‘Last’ buttons, plus page numbers

5. Change Your Language or Translate Some Text Phrases

By default, we load Datatables.net with English language. That said, the column names, that are in Blade files, are loaded with @lang functionality, so you can translate them in resources/lang/[language] Laravel files.

<th>@lang('global.users.fields.name')</th>
<th>@lang('global.users.fields.email')</th>
<th>@lang('global.users.fields.role')</th>

But how do you translate some hard-coded Datatables words? Like “Show X entries”, “Previous/Next” etc.

If you want to have the whole Datatables in your own language, luckily they have a whole set of files prepared, you just have to load them.

We have this code in resources/views/layouts/admin.blade.php file:

  let languages = {
    'en': 'https://cdn.datatables.net/plug-ins/1.10.19/i18n/English.json',
    'es': 'https://cdn.datatables.net/plug-ins/1.10.19/i18n/Spanish.json'
  };

In other words, we load English language not from our system, but from Datatables CDN. So all you need to do is to change that .json file, choose from this list.

Here’s the result:

If you want to override and translate separate texts, you can do that too.
Let’s return to our config array in resources/views/layouts/admin.blade.php

  $.extend(true, $.fn.dataTable.defaults, {
    language: {
      url: languages['{{ app()->getLocale() }}']
      lengthMenu: "Display _MENU_ records per page",
      zeroRecords: "Nothing found - sorry",
      info: "Showing page _PAGE_ of _PAGES_",
      infoEmpty: "No records available",
      infoFiltered: "(filtered from _MAX_ total records)"
    },

6. How to Change Ordering by Columns

By default, Datatables list is ordered by the first column, ascending. But you can easily override it:

let dtOverrideGlobals = {
    // ...
    "order": [[ 3, "desc" ]]
};

If you want to perform multi-column ordering, it’s a little more complicated.

Let’s say, you want to order by created_at asc (in other words, from oldest to newest), and then another column would be name alphabetically.

Then the code would look like this:

let dtOverrideGlobals = {
    "order": [[ 6, "asc" ]],
    columnDefs: [{
        orderable: false,
        className: 'select-checkbox',
        targets: 0
    }, {
        orderable: false,
        searchable: false,
        targets: -1
    },
    {
        targets: 6,
        orderData: [ 6, 2 ]
    }],

Here’s the result visually:

These are the most common customizations of Datatables list, downloaded from our QuickAdminPanel. But you can perform the same option changes even without our generator, if you use Datatables directly in jQuery.

All those examples are published in a demo Github repository.

For more information, visit official Datatables.net documentation and Laravel Datatables package documentation.