Laravel，数据表，具有关系计数的列

Laravel, Datatables, column with relations count

提问by Томица Кора?

I have two models, Userand Training, with Many to manyrelationship between them. I'm using the Laravel Datatablespackage 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:

我有两个模型，User和Training，Many to many它们之间有关系。我正在使用Laravel Datatables包来显示所有用户的表格。这是数据控制器方法（检索查询结果并创建 Datatables 表）的样子：

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 Userhave)?

如何在创建的表中添加一列，显示每个用户的关系总数（即每个用户有多少个Trainings User）？

回答by patricus

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.

蛮力的方法是尝试User::selectRaw(...)具有内置子查询的 a 来获取用户的培训计数并将其公开为一个字段。

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_columnmethod to add in the count. Assuming your relationship is named trainings:

但是，有一种更内置的方法可以做到这一点。您可以预先加载关系（以避免 n+1 查询），并使用 DataTablesadd_column方法添加计数。假设您的关系名为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_columnshould 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:

中列的名称add_column应与加载的关系名称相同。如果出于某种原因使用不同的名称，则需要确保删除关系列，以便将其从数据数组中删除。例如：

    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 Builderobject passed to the of()method, so the query itself needs the field on which to order.

不幸的是，如果您想在计数字段上订购，您将需要使用蛮力方法。包通过调用传递给方法->orderBy()的Builder对象进行排序of()，因此查询本身需要排序的字段。

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:

然而，即使您需要执行一些原始 SQL，它也可以变得更简洁一些。您可以添加将添加关系计数的模型范围。例如，将以下方法添加到您的 User 模型中：

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

注意：以下函数仅适用于 hasOne/hasMany 关系。请参阅Edit 2下面的更新功能以处理所有关系。

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:

将该范围添加到您的 User 模型后，您的 getData 函数变为：

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 selectRelatedCountscope (e.g. selectRelatedCount('trainings', 'training_count')).

如果您希望计数字段具有不同的名称，您可以将该字段的名称作为第二个参数传递给selectRelatedCount作用域（例如selectRelatedCount('trainings', 'training_count')）。

Edit 2

编辑 2

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

上述scopeSelectRelatedCount()方法存在一些问题。

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.

首先，调用 to$relation->getQualifiedParentKeyName()仅适用于 hasOne/hasMany 关系。这是该方法定义为 的唯一关系public。所有其他关系将此方法定义为protected. 因此，将此范围与不是 hasOne/hasMany 的关系一起使用会引发Illuminate\Database\Query\Builder::getQualifiedParentKeyName()异常。

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).

其次，生成的计数 SQL 并不适用于所有关系。同样，它适用于 hasOne/hasMany，但手动生成的 SQL 根本不适用于多对多关系（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:

但是，我确实找到了解决这两个问题的方法。在查看关系代码确定异常原因后，我发现 Laravel 已经提供了一个公共方法来为关系生成计数 SQL： getRelationCountQuery(). 适用于所有关系的更新范围方法是：

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

编辑 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 = '/(?:(["\'])(?:\\.|[^])*|\\.|[^\?])+/';
                $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 whereRawto avoid parameter binding.

注意：在撰写本文时，bllim/laravel4-datatables-package 数据表包在选择字段的子查询中存在参数绑定问题。数据将正确返回，但计数不会（“显示 0 到 0 个条目，共 0 个条目”）。我在这里详细说明了这个问题。这两个选项是使用该问题中提供的代码手动更新数据表包，或者不在计数子查询中使用参数绑定。使用whereRaw以避免参数绑定。

回答by Dave

I would setup your DB tables and Eloquent models using the conventions provided at http://laravel.com/docs/4.2/eloquent. In your example you would have three tables.

我将使用http://laravel.com/docs/4.2/eloquent提供的约定设置您的数据库表和 Eloquent 模型。在您的示例中，您将拥有三个表。

• trainings
• training_user
• users

• 培训
• 培训用户
• 用户

Your models would look something like this.

你的模型看起来像这样。

class Training {

    public function users() {
        return $this->belongsToMany('User');    
    }

}

class User {

    public function trainings() {
        return $this->belongsToMany('Training');    
    }

}

You can then use Eloquent to get a list of users and eager load their trainings.

然后，您可以使用 Eloquent 获取用户列表并急切加载他们的培训。

// Get all users and eager load their trainings
$users = User::with('trainings')->get();

If you want to count the number of trainings per user you can simply iterate over $users and count the size of the trainings array.

如果您想计算每个用户的培训次数，您可以简单地遍历 $users 并计算 trainings 数组的大小。

foreach ( $users as $v ) {
    $numberOfTrainings = sizeof($v->trainings); 
}

Or you can simply do it in pure SQL. Note that my example below assumes you follow Laravel's conventions for naming tables and columns.

或者您可以简单地使用纯 SQL 来完成。请注意，我下面的示例假设您遵循 Laravel 命名表和列的约定。

SELECT 
    u.*, COUNT(p.user_id) AS number_of_trainings 
FROM 
    users u 
JOIN 
    training_user p ON u.id = p.user_id 
GROUP BY 
    u.id

Now that you have a couple of ways to count the number of relations, you can use whatever method you like to store that value somewhere. Just remember that if you store that number as a value in the user table you'll need to update it every time a user creates/updates/deletes a training (and vice versa!).

现在您有几种方法可以计算关系的数量，您可以使用任何您喜欢的方法将该值存储在某处。请记住，如果将该数字作为值存储在用户表中，则每次用户创建/更新/删除训练时都需要更新它（反之亦然！）。