DataTables Ajax Request Data

<div class="row">
   <div class="col-xs-12">
      <div class="row">
         <div class="col-sm-12">
            <table id="table-list" class="display" style="width: 100%">
               <thead>
               <tr>
                  <th>col 1</th>
                  <th>col 2</th>
                  <th>col 3</th>
                  <th>col 4</th>
                  <th>col 5</th>
                  <th>col 6</th>
                  <th>col 7</th>
                  <th>col 8</th>
                  <th>col 9</th>
                  <th>col 10</th>
               </tr>
               </thead>
               <tbody>
               </tbody>
            </table>
         </div>
      </div>
   </div>
</div>

var actualTable = $('#table-list');
var table = actualTable.DataTable({
	order: [[1, 'asc']],
	serverSide: true,
	processing: true,
	ajax: {
		url: '/path/to/your/server/that/handle/the/ajaxRequest',
		type: "POST",
		data: function (d) {
			var yourObject = {};
			yourObject.param1= "param1";
			yourObject.param2= "param2";
			yourObject.param3= "param3";
			d.yourObject = yourObject;
		},

		error: function (xhr, error, thrown) {
			alert('Error occurred. Please try again.');
		}
	},
	lengthMenu: [[100, 500, 1000], [100, 500, 1000]],
	dom: 'lrtip',
	pageLength: 500,
});

The request will be in form data.

• draw – the number of time that table has been draw. You need to responds the same value in your reply or datatable will not update the items on the table.
• start – the starting page number, kind of like the offset in SQL
• length – the max. length of items request, kind of like the limit in SQL
• columns[i][data] – The mapping name of the column. Default is number, start with 0 for the first column. (i start with 0, the index of the column.)
• columns[i][name] – …
• columns[i][searchable] – …
• columns[i][orderable] – …
• columns[i][search][value] – …
• columns[i][search][regex] – …
• order[i][column] – The index of column that has order in the table. Since it is possible to have multiple table ordered in datatable, this is an array.
• order[i][direction] – 'acs', or 'desc'
• search[value] – the value search in the datatable search box.
• search[regex] – true or false

For columns[] field, each column has their own values. In our example, we have ten columns, so our columns array will looks like:

• columns[0][name] :0
• columns[0][searchable] :
• columns[0][orderable] :true
• columns[0][search][value] :
• columns[0][search][regex] : false
• .
• .
• .
• columns[9][name] :9
• columns[9][searchable] :
• columns[9][orderable] :true
• columns[9][search][value] :
• columns[9][search][regex] : false

For order[] array, if we have order the table, we will have that array submit to the backend as well. This will be use for helping us to order the search query in SQL by using 'order by …'

Here we will user groovy with grails as our language. It should be easy to convert to any other language.

In your backend function, you should first get the draw id of the table, and the start, and length of the table. We use start and length as offset and limit in our SQL.

int draw = Integer.parseInt(params.get("draw") as String)
int offset = Integer.parseInt(params.get("start") as String)
int limit = Integer.parseInt(params.get("length") as String)

In this example, we also need to get the order and direct of the column. I.e. is the column sorted?

List<Integer> orders = []
List<String> directions = []
int iOrderCounter = 0
boolean done = false
while (!done) {
	def x = params.get("order[" + iOrderCounter + "][column]")
	def y = params.get("order[" + iOrderCounter + "][dir]")
	if (x == null) {
		done = true
	} else {
		orders.add(Integer.parseInt(x as String))
		directions.add(y as String)
		iOrderCounter++
	}
}

Do not forget to get our actual search parameters! The parameters name is related to how you setup the data object in the datatable frontend javascript. We have used yourObject and param1, param2, param3… You can have a more creative one.

String sParam1 = params.get("yourObject[param1]")
String sParam2 = params.get("yourObject[param2]")
String sParam3 = params.get("yourObject[param3]")

First you need to have the total count of the result without the offset, and limit. We need to tell the user how many data would have result for such search. Of course, we do not need to obtain all of them in the memory, we only need to get the one that need to display on the page. If the user change the page number, we will do the query all over again with different offset value. Then we get the list with offset, limit, and the columns' orders, and direction.

long totalCount = yourGrailService.yourSearchTotalCountWithOffsetLimit(param1, param2, param3)
List<ResultingItem > list = yourGrailService.yourSearchWithOffsetLimit(param1, param2, param3, offset, limit, orders, directions)

Your SQL, well, it really depending on you… It could be something like…

StringBuilder sb = new StringBuilder()
sb.append("select t.item1, t.item2, t.item3, t.item4, t.item5, t.item6, t.item7, t.item8, t.item9, t.item10 from MyTable t "+
"where " +
"t.item1 > param1 and " +
"(t.item2 = param2 or t.item3 = param3)")

int iSize = orders.size()
if (iSize > 0) {
   sb.append("order by ")
   for (int i = 0; i < iSize; i++) {
      switch (orders.get(i)) {
         case 0: sb.append("t.item1 "); break
         case 1: sb.append("t.item2 "); break
         case 2: sb.append("t.item3 "); break
         case 3: sb.append("t.item4 "); break
         case 4: sb.append("t.item5 "); break
         case 5: sb.append("t.item6 "); break
         case 6: sb.append("t.item7 "); break
         case 7: sb.append("t.item8 "); break
         case 8: sb.append("t.item9 "); break
         case 9: sb.append("t.item10 "); break
      }
      sb.append(directions.get(i)).append(",")
   }
   sb.setLength(sb.length() - 1)
}
List<Object[]> list = MyDomainObject.executeQuery(sb.toString(), [:], [readOnly: true, max: limit, offset: offset]) as List<Object[]>

Assume we obtained a list result from the SQL, here is how we build the resulting JSON.

List<Map> data = []
for (ResultingItem item : list) {
   Map map = [0 : item.i1,
            1 : item.i2,
            2 : item.i3,
            3 : item.i4,
            4 : item.i5,
            5 : item.i6,
            6 : item.i7,
            7 : item.i8,
            8 : item.i9,
            9 : item.i10
   ]
   data.add(map)
}

def responseData = [draw           : draw,
               recordsTotal   : totalCount,
               recordsFiltered: totalCount,
               data           : data]
render responseData as JSON

The frontend datatable will catch it as JSON, and display the result on the table correspondingly.