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 );
    }
}

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');

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') );
}

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
    } );
	
} );

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');

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();
}

Let me explain the code given above.

  • Line 01 & 02: We are adding necessary ajax action to the callback function.
  • Line 06: We are declaring the header as JSON.
  • Line 08: We will be getting values from $_GET which will be triggered by the JS code we added earlier.
  • Line 10: Setting table columns to key or meta.
  • Line 16 – 22: Our array for the query. We are queriying 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).

    DataTables Requests in Browser

    $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’.

  • Line 24 – 33: 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 37: 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 of the title.
  • Line 59: We are counting the total amount of posts. This is a required data for the table.
  • Line 61: From here we have the loop that is storing data to $data array.
  • Line 74 – 81: We add all the required info needed to pass as the result and encode the $json_data array to JSON.
  • Line 85: 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.

    Sharing is caring!