Laravel, Datatables, column with relations count

Question

I have two models, User and Training, with Many to many relationship between them. I'm using the Laravel Datatables package to display a table of all the users. This is how the data controller method (which retrieves the query results and creates a Datatables table) looks like:

public function getData()
{
    $users = User::select(array('users.id', 'users.full_name', 'users.email', 'users.business_unit', 'users.position_id'))
        ->where('users.is_active', '=', 1);

    return \Datatables::of($users)
        ->remove_column('id')
        ->make();
}

How can I add a column to the created table which displays the total number of relations for each user (that is, how many Trainings does each User have)?

Answer 1

The brute force way would be to try a User::selectRaw(...) which has a built in subquery to get the count of trainings for the user and expose it as a field.

However, there is a more built-in way to do this. You can eager load the relationship (to avoid the n+1 queries), and use the DataTables add_column method to add in the count. Assuming your relationship is named trainings:

public function getData() {
    $users = User::with('trainings')->select(array('users.id', 'users.full_name', 'users.email', 'users.business_unit', 'users.position_id'))
        ->where('users.is_active', '=', 1);

    return \Datatables::of($users)
        ->add_column('trainings', function($user) {
            return $user->trainings->count();
        })
        ->remove_column('id')
        ->make();
}

The name of the column in add_column should be the same name as the loaded relationship. If you use a different name for some reason, then you need to make sure to remove the relationship column so it is removed from the data array. For example:

    return \Datatables::of($users)
        ->add_column('trainings_count', function($user) {
            return $user->trainings->count();
        })
        ->remove_column('id')
        ->remove_column('trainings')
        ->make();

Edit

Unfortunately, if you want to order on the count field, you will need the brute force method. The package does its ordering by calling ->orderBy() on the Builder object passed to the of() method, so the query itself needs the field on which to order.

However, even though you'll need to do some raw SQL, it can be made a little cleaner. You can add a model scope that will add in the count of the relations. For example, add the following method to your User model:

Note: the following function only works for hasOne/hasMany relationships. Please refer to Edit 2 below for an updated function to work on all relationships.

public function scopeSelectRelatedCount($query, $relationName, $fieldName = null)
{
    $relation = $this->$relationName(); // ex: $this->trainings()
    $related = $relation->getRelated(); // ex: Training
    $parentKey = $relation->getQualifiedParentKeyName(); // ex: users.id
    $relatedKey = $relation->getForeignKey(); // ex: trainings.user_id
    $fieldName = $fieldName ?: $relationName; // ex: trainings

    // build the query to get the count of the related records
    // ex: select count(*) from trainings where trainings.id = users.id
    $subQuery = $related->select(DB::raw('count(*)'))->whereRaw($relatedKey . ' = ' . $parentKey);

    // build the select text to add to the query
    // ex: (select count(*) from trainings where trainings.id = users.id) as trainings
    $select = '(' . $subQuery->toSql() . ') as ' . $fieldName;

    // add the select to the query
    return $query->addSelect(DB::raw($select));
}

With that scope added to your User model, your getData function becomes:

public function getData() {
    $users = User::select(array('users.id', 'users.full_name', 'users.email', 'users.business_unit', 'users.position_id'))
        ->selectRelatedCount('trainings')
        ->where('users.is_active', '=', 1);

    return \Datatables::of($users)
        ->remove_column('id')
        ->make();
}

If you wanted the count field to have a different name, you can pass the name of the field in as the second parameter to the selectRelatedCount scope (e.g. selectRelatedCount('trainings', 'training_count')).

Edit 2

There are a couple issues with the scopeSelectRelatedCount() method described above.

First, the call to $relation->getQualifiedParentKeyName() will only work on hasOne/hasMany relations. This is the only relationship where that method is defined as public. All the other relationships define this method as protected. Therefore, using this scope with a relationship that is not hasOne/hasMany throws an Illuminate\Database\Query\Builder::getQualifiedParentKeyName() exception.

Second, the count SQL generated is not correct for all relationships. Again, it would work fine for hasOne/hasMany, but the manual SQL generated would not work at all for a many to many relationship (belongsToMany).

I did, however, find a solution to both issues. After looking through the relationship code to determine the reason for the exception, I found Laravel already provides a public method to generate the count SQL for a relationship: getRelationCountQuery(). The updated scope method that should work for all relationships is:

public function scopeSelectRelatedCount($query, $relationName, $fieldName = null)
{
    $relation = $this->$relationName(); // ex: $this->trainings()
    $related = $relation->getRelated(); // ex: Training
    $fieldName = $fieldName ?: $relationName; // ex: trainings

    // build the query to get the count of the related records
    // ex: select count(*) from trainings where trainings.id = users.id
    $subQuery = $relation->getRelationCountQuery($related->newQuery(), $query);

    // build the select text to add to the query
    // ex: (select count(*) from trainings where trainings.id = users.id) as trainings
    $select = '(' . $subQuery->toSql() . ') as ' . $fieldName;

    // add the select to the query
    return $query->addSelect(DB::raw($select));
}

Edit 3

This update allows you to pass a closure to the scope that will modify the count subquery that is added to the select fields.

public function scopeSelectRelatedCount($query, $relationName, $fieldName = null, $callback = null)
{
    $relation = $this->$relationName(); // ex: $this->trainings()
    $related = $relation->getRelated(); // ex: Training
    $fieldName = $fieldName ?: $relationName; // ex: trainings

    // start a new query for the count statement
    $countQuery = $related->newQuery();

    // if a callback closure was given, call it with the count query and relationship
    if ($callback instanceof Closure) {
        call_user_func($callback, $countQuery, $relation);
    }

    // build the query to get the count of the related records
    // ex: select count(*) from trainings where trainings.id = users.id
    $subQuery = $relation->getRelationCountQuery($countQuery, $query);

    // build the select text to add to the query
    // ex: (select count(*) from trainings where trainings.id = users.id) as trainings
    $select = '(' . $subQuery->toSql() . ') as ' . $fieldName;

    $queryBindings = $query->getBindings();
    $countBindings = $countQuery->getBindings();

    // if the new count query has parameter bindings, they need to be spliced
    // into the existing query bindings in the correct spot
    if (!empty($countBindings)) {
        // if the current query has no bindings, just set the current bindings
        // to the bindings for the count query
        if (empty($queryBindings)) {
            $queryBindings = $countBindings;
        } else {
            // the new count query bindings must be placed directly after any
            // existing bindings for the select fields
            $fields = implode(',', $query->getQuery()->columns);
            $numFieldParams = 0;
            // shortcut the regex if no ? at all in fields
            if (strpos($fields, '?') !== false) {
                // count the number of unquoted parameters (?) in the field list
                $paramRegex = '/(?:(["\'])(?:\\\.|[^\1])*\1|\\\.|[^\?])+/';
                $numFieldParams = preg_match_all($paramRegex, $fields) - 1;
            }
            // splice into the current query bindings the bindings needed for the count subquery
            array_splice($queryBindings, $numFieldParams, 0, $countBindings);
        }
    }

    // add the select to the query and update the bindings
    return $query->addSelect(DB::raw($select))->setBindings($queryBindings);
}

With the updated scope, you can use the closure to modify the count query:

public function getData() {
    $users = User::select(array('users.id', 'users.full_name', 'users.email', 'users.business_unit', 'users.position_id'))
        ->selectRelatedCount('trainings', 'trainings', function($query, $relation) {
            return $query
                ->where($relation->getTable().'.is_creator', false)
                ->where($relation->getTable().'.is_speaker', false)
                ->where($relation->getTable().'.was_absent', false);
        })
        ->where('users.is_active', '=', 1);

    return \Datatables::of($users)
        ->remove_column('id')
        ->make();
}

Note: as of this writing, the bllim/laravel4-datatables-package datatables package has an issue with parameter bindings in subqueries in the select fields. The data will be returned correctly, but the counts will not ("Showing 0 to 0 of 0 entries"). I have detailed the issue here. The two options are to manually update the datatables package with the code provided in that issue, or to not use parameter binding inside the count subquery. Use whereRaw to avoid parameter binding.