5. SUM, CONCAT, and other SQL Functions

Many times we want add SUM() for totals, to combine information using CONCAT(), and other SQL functions. Although these functions are not specific to CakePHP-DataTables they are frequently asked about. Let's build on the previous examples using our User model. This time we will also include a UserHour model which the User model has a hasMany relation to. To accomplish this we'll use CakePHPs virtualFields attribute.

In the example below we will combine User.first_name and User.last_name and create a sum of a users hours.

class UsersController extends AppController {

    public $components = array('DataTable');

    function index(){

        $this->User->UserHour->virtualFields = array(
            'total_hours' => 'SUM(UserHour.hours)',
        );
        $this->User->virtualFields = array(
            'full_name' => 'CONCAT(User.first_name," ",User.last_name)',
        );

        $this->paginate = array(
            'link' => array(
                'fields' => array(
                    'User.username', 'User.full_name', 'User.email', 'User.created', 'User.active'
                )
                'UserHour' => array(
                    'fields' => array(
                        'total_hours'
                    )
                )
            )
        );
        $this->DataTable->mDataProp = true;
        $this->set('response', $this->DataTable->getResponse());
        $this->set('_serialize','response');
    }

As you can see once we've created a virtualField for a given model we then include in that models field array. Note: You cannot search on virtual fields because it will cause a SQL error. For now set these to bSearchable: false in dataTables.

Now let's look at our HTML:

<table id="users-table">
    <thead>
        <th>Username</th>
        <th>Full Name</th>
        <th>Email</th>
        <th>Created</th>
        <th>Active</th>
        <th>Hours</th>
    </thead>
    <tbody>
    </tbody>
</table>

And finally our JavaScript:

$('#users-table').dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "/users/index.json",
    "sDom": "fr",
    "aoColumns": [
        {mData:"User.username"},
        {mData:"User.full_name",bSearchable:false}, // can't search virtual fields
        {mData:"User.email"},
        {mData:"User.created"},
        {mData:"User.active"},
        {mData:"User.total_hours",bSearchable:false}, // can't search virtual fields
    ]
});