Boom! Okay, now that we have that out of the way, not long after I first started using jQuery DataTables I Googled for a way to "explode" rows. I finally found something like this example, which has been expanded upon quite a bit since those days. It now mentions the possiblity of exactly what I decided to do, which was to not use static data in each row like it does, but to put a placeholder in each "exploded" (expanded) row and then AJAX in the content (this keeps the DOM lightweight by not loading all of the "child" content, regardless of how large it may be, for each row before the user actually requests it). Let's explore a working example of that method, which I've used many times in a production enviornment.

I'm using the Employees Sample Database for this and other examples. This happens to appear to be the same example database they are using at least a part of in the above example, but I'm not going to repeat it. Instead, let's go a bit further by putting all 300,000+ employees in one place. How the heck do we do that without using server-side DataTables and forcing the poor user to search and scroll all day? Well, we have departments, and a table to join together employees and their department. Let's see what happens when we do these JOINs and a GROUP BY:

 SELECT count(de.emp_no), d.dept_no, d.dept_name FROM departments d INNER JOIN dept_emp de ON d.dept_no = de.dept_no 
INNER JOIN employees e on de.emp_no = e.emp_no GROUP BY d.dept_no;
count(de.emp_no)dept_nodept_name
20211 d001 Marketing
17346 d002 Finance
17786 d003 Human Resources
73485 d004 Production
85707 d005 Development
20117 d006 Quality Management
52245 d007 Sales
21126 d008 Research
23580 d009 Customer Service

Not too bad, but how do we display those individual tables? That's not too bad either. You can put any content inside of the child row, even another table. Here's how we would nest the employees inside their departments:


Department #Department Name

There's a lot going on behind the scenes here. First, we are starting out with a completely empty table:


<table id="employees" class="compact hover stripe">
<thead>
<tr><th>Employee #<th>Name</th><th>Title<
</thead>
</table>

Then, we are initializing it as a DataTable from the Document Ready Function:


var table = $('#employee-dept').DataTable({
		"ajax" : { 
			"url" : '../main/php/serverHTML.php?type=JSON&content=employee-dept',
	        "cache": false,
            "contentType": "application/json",
		},
        "columns" :  [  
                        { "className":      'details-control',
                          "orderable":      false,
                          "data":           null,
                          "defaultContent": ''
                         },
                         {"data": 'dept_no'},
				         { "className" : 'all',
                        		 'data': 'dept_name', 'sType': 'string', 'visible': true}],
		"scrollX" : true,
		"searching": false,
		"lengthChange": false
		
	});

That AJAXes in our content as JSON data from a database query (similar to the one above) and puts it into the columns. We're also adding the className of details-control as per the linked DataTables site example above, which requires their simple CSS example:


td.details-control {
    background: url('/main/img/Orb-plus-icon.png') no-repeat center center;
    cursor: pointer;
}

tr.shown td.details-control {
    background: url('/main/img/Orb-minus-icon.png') no-repeat center center;
}

We are also completely following that example, until we get to expanding the row:


       else {
	            // Open this row
	        	//get dept no
	        	var data = row.data();
	        	var deptNo = data['dept_no'];
	        	row.child( format(row.data()) ).show();
	        	var empTable = initEmployeeTable (deptNo);
	            tr.addClass('shown');
	        }

At that point, we deviate from it in that we get the dept_no for later use and then use a much simpler format function:


function format (d) {
	return '<div id=\"'+d.dept_no+'\"></div>';
		  
}

All that does is add a placeholder div for the content we are going to AJAX in for the internal table, which keeps the DOM more lightweight than using static content for the interal table. This really isn't vital with only nine rows in the external table, but gets really important with a large number of rows in that table. Next, we call the initEmployeeTable function with the deptNo we just got to fill in that table:


function initEmployeeTable (deptNo) {
	$.ajax ({
		   url: '../main/php/serverHTML.php?type=HTML&content=employee-table',
		   beforeSend: showLoadingImage (deptNo),
		   contentType : 'html'
	   })
	   .done (function (content ) {
	   $('#'+deptNo).html(content);	   
	   var table = $('#employees').DataTable({
		"ajax" : { 
			"url" : '../main/php/serverHTML.php?type=JSON&content=employees&dept='+deptNo,
	        "cache": false,
            "contentType": "application/json",
		},
        "columns" :  [  {"data": 'emp_no' },
                        {'data': 'name'},
                        {'data': 'title'},
                        {'data': 'salary'}
	                 ],
	                 "scrollX": true
    
	   });
    });
}

All we did in that long function was just AJAX in the HTML for the table itself and (when the AJAX was .done) init the DataTable and get the JSON content for it, all in one pass. Then, boom, you're done (sorry, couldn't resist).

It's likely obvious, but worth pointing out that you really can get and use almost any content that looks and works decently inside of each row. I've used anything from row details to forms inside of DataTables, and it's all quite functional and a good user experience. Play around with it and see what works for you!