Need to find something?

DataTables Server Side Processing in WordPress Banner

DataTables Server Side Processing in WordPress

DataTables is a very neat and powerful jQuery plugin. Showcasing data in tables with it is easy and the user experience is great. I recently had to use DataTables for a project to showcase a large number of data. For a large number of data, we will need to use the server side processing method for better performance and efficiency. Let us see how to use DataTables server-side processing in WordPress.

Why Not Existing Plugins?

I thought using WP DataTables would be fine. However, it did not work as expected and was very very slow. So, I decided to do it without a plugin. It has its benefits but the overall performance and experience were not satisfactory for a premium plugin.

The Scenario

We are going to build a table using DataTables on a WordPress site which has a lot of movies (over 4800) as the custom post type (I previously used this example on Simple Ajax Filter or Search for WordPress Post). With all that posts, it is not suitable for us to do WP_Query with no limits. That is why we are going to use the server-side processing method to show the data. You can learn more about the server side processing method of DataTables here.

Initial Setup

For the purpose of dataTables search to work properly, we will need to have an extra custom field on each post where the value is equal to the title. We will later use this metadata in our query. Add the following code to your functions.php file to achieve this.

// Add Post title to custom meta 
add_action( 'transition_post_status', 'duplicate_title', 10, 3 ); 

function duplicate_title( $new, $old, $post ) { 
  if ( $post->post_type == 'movie' ) { 
    update_post_meta( $post->ID, 'd_title', $post->post_title ); 
  } 
}Code language: PHP (php)

In this code, we are running a function every time a movie post type is saved through transition_post_status action. So, each time the post status changes, for example – draft to published, published to draft etc. The post title will be saved on d_title custom field. This will make the process much simpler later when we will run the query.

Once that is done, I am assuming we have the movies posts created with some custom fields like year, rating, language etc. For simplicity, we are going to show 3 columns on our table. These are Movie Name, Year and Rating. So, let us assume we have these posts ready for showing the data. If you want to know how to set them up, you may check my previous post here.

Adding DataTables Resources

The next thing to do is adding DataTables resources. Add the following code to your active theme’s functions.php file.

// Add DataTables Resources
 
function add_datatables_scripts() {
  wp_register_script('datatables', 'https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js', array('jquery'), true);
  wp_enqueue_script('datatables');

  wp_register_script('datatables_bootstrap', 'https://cdn.datatables.net/1.10.13/js/dataTables.bootstrap.min.js', array('jquery'), true);
  wp_enqueue_script('datatables_bootstrap');
}
 
function add_datatables_style() {
  wp_register_style('bootstrap_style', 'https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css');
  wp_enqueue_style('bootstrap_style');

  wp_register_style('datatables_style', 'https://cdn.datatables.net/1.10.13/css/dataTables.bootstrap.min.css');
  wp_enqueue_style('datatables_style');
}
 
add_action('wp_enqueue_scripts', 'add_datatables_scripts');
add_action('wp_enqueue_scripts', 'add_datatables_style');Code language: PHP (php)

Here, we included DataTables Javascript file along with CSS file. For styling, we added Bootstrap’s CSS library and DataTables Bootstrap Javascript file. You may use different libraries for the style. If you already have Bootstrap’s CSS library loaded, you should not need to add the bootstrap.min.css file.

The JS Part

Much similar to our previous tutorial about Ajax filter and search, we are going to add a function and enque script in our shortcode function. Add the following code to your functions.php file

function movie_datatables_scripts() {
  wp_enqueue_script( 'movie_datatables', get_stylesheet_directory_uri(). '/js/movietable.js', array(), '1.0', true );
  wp_localize_script( 'movie_datatables', 'ajax_url', admin_url('admin-ajax.php?action=movie_datatables') );
}Code language: PHP (php)

We will call the “movie_datatables_scripts()” function in our next step. As indicated on the code above, we will need to create a file called movietable.js in our theme’s “js” folder.

On the movietable.js file, add the following code.

$ = jQuery;
$(document).ready(function() {
    $('#movietable').dataTable({
        "processing": true,
        "serverSide": true,
        "ajax": ajax_url
    } );
} );Code language: JavaScript (javascript)

Creating Shortcode

We will be displaying the table using a shortcode. So, let us create the function using the following code. Place the code in your functions.php file.

function movie_datatables() {
  
    movie_datatables_scripts(); 

    ob_start(); ?> 
    
    <table id="movietable" class="table table-striped table-hover"> 
    <thead> 
        <tr> 
            <th>Movie Name</th> 
            <th>Year</th> 
            <th>Rating</th> 
        </tr> 
    </thead> 
    </table> 

    <?php return ob_get_clean(); 
}

add_shortcode ('movie_datatables', 'movie_datatables');Code language: JavaScript (javascript)

Notice that on line 3 we called the “movie_datatables_scripts()” function that we created earlier.

Callback Function

Now we will be setting up the callback function. Add the following piece of code to your functions.php file.

add_action('wp_ajax_movie_datatables', 'datatables_server_side_callback');
add_action('wp_ajax_nopriv_movie_datatables', 'datatables_server_side_callback');


function datatables_server_side_callback() {

  header("Content-Type: application/json");

  $request= $_GET;

  $columns = array(
    0 => 'post_title',
    1 => 'year',
    2 => 'rating'
  );


  $args = array(
    'post_type' => 'movie',
    'post_status' => 'publish',
    'posts_per_page' => $request['length'],
    'offset' => $request['start'],
    'order' => $request['order'][0]['dir'],
  );

  if ($request['order'][0]['column'] == 0) {

    $args['orderby'] = $columns[$request['order'][0]['column']];

  } elseif ($request['order'][0]['column'] == 1 || $request['order'][0]['column'] == 2) {

    $args['orderby'] = 'meta_value_num';

    $args['meta_key'] = $columns[$request['order'][0]['column']];
  }

  //$request['search']['value'] <= Value from search

  if( !empty($request['search']['value']) ) { // When datatables search is used
    $args['meta_query'] = array(
      'relation' => 'OR',
      array(
        'key' => 'd_title',
        'value' => sanitize_text_field($request['search']['value']),
        'compare' => 'LIKE'
      ),
      array(
        'key' => 'year',
        'value' => sanitize_text_field($request['search']['value']),
        'compare' => 'LIKE'
      ),
      array(
        'key' => 'rating',
        'value' => sanitize_text_field($request['search']['value']),
        'compare' => 'LIKE'
      )
    );
  }

  $movie_query = new WP_Query($args);
  $totalData = $movie_query->found_posts;

  if ( $movie_query->have_posts() ) {
    
    while ( $movie_query->have_posts() ) {
    
      $movie_query->the_post();

      $nestedData = array();
      $nestedData[] = get_the_title();
      $nestedData[] = get_field('year');
      $nestedData[] = get_field('rating');

      $data[] = $nestedData;

    }

    wp_reset_query();

    $json_data = array(
      "draw" => intval($request['draw']),
      "recordsTotal" => intval($totalData),
      "recordsFiltered" => intval($totalData),
      "data" => $data
    );

    echo json_encode($json_data);

  } else {

    $json_data = array(
      "data" => array()
    );

    echo json_encode($json_data);
  }
  
  wp_die();

}Code language: PHP (php)

Let me explain the code given above.

  • Line 01 & 02: We are adding necessary ajax action to the callback function.
  • Line 07: We are declaring the header as JSON.
  • Line 09: We will be getting values from $_GET which will be triggered by the JS code we added earlier.
  • Line 11: Setting table columns to key or meta.
  • Line 18 – 24: Our array for the query. We are querying for movie post types that are published. Important things to notice are ‘posts_per_page’‘offset’ & ‘order’. These 3 are needed to set according to the request received from DataTables. You can check the request from the network tab in developer tools (chrome). $request[‘length’] is giving us how many data the table will show. This is set to ‘posts_per_page’ as it works the same way.
    $request[‘start’] is giving us from which element to start the query. So, we used ‘offset’.
    $request[‘order’][0][‘dir’] is giving us the direction for order. ASC or DSC. Here used ‘order’.
DataTables Requests in Browser
  • Line 26 – 35: Here we are checking if the table was requested to be sorted by the column [0] which is our Movie Title column. Again checking if it is for the column [1] or [2].$columns[$request[‘order’][0][‘column’]] in short is getting the key or meta (set in line 10) that should be used to sort the query.
  • Line 39: From this line, we are handling the request for search terms. We are checking if $request[‘search’][‘value’] has any value in it, which indicates if we have a search or not. As we have 3 columns on our table, we will need to search within them. From the search term, our query should look for the match in data and get results. For this, we are going to use ‘meta_query’ and the ‘LIKE’ comparison. Now the function we had set at the very beginning will come in use. As the ‘post_title’ is not a metadata, we can not do ‘meta_query’ with it. So, we are using our ‘d_title’ custom field which has the same value as the title.
  • Line 61: We are counting the total amount of posts. This is a required data for the table.
  • Line 65: From here we have the loop that is storing data to $data array.
  • Line 80 – 85: We add all the required info needed to pass as the result and encode the $json_data array to JSON.
  • Line 95: For query without any results, we are sending an empty array.

The Result

DataTables Server Side Processing in WordPress Demo

Let me know in the comments or live chat if you have any questions. If you have any suggestion, feel free to share.

Last Updated: June 7, 2020

Al-Mamun Talukder

About Al-Mamun Talukder

WordPress Developer. Minimalist Designer. Tech Enthusiast. Music & Movie Freak. Loves Cars. Founder of WolfDevs.

Connect with me: Upwork, GitHub, Facebook, Twitter

11 comments

  • nancy stephanie vidal rodriguez

    Hola he tratado de agregar serverside a mi script pero nome permite me desaparece la parte de paginacion, tengo duda con el ajax que ruta es la que debo de colocar estoy trabajandolo con python django

  • Nice work

  • Great article! I believe wpDataTables now has a server side processing option. Have you had a chance to try it? I’m hoping the performance will be similar now

  • Hey I found this a very useful foundation for my application, the documentation on the datatables website isn’t so great.

    Thank you 🙂

  • Thank you, worked for me 🙂

  • Hi. Is it possible to send a php variable to the query?

    • A
      Al-Mamun Talukder

      Yes. Possible

      • yes it possible for you to tell how. the tutorial is excellent and its quite useful to me. however, i can’t sent a variable to the ajax query from php. can you help me out with that please?

Leave a Reply to Ali (Cancel Reply)