从Laravel的Where Where关系计算关系

Question

我正在Laravel 5.4中构建一个小应用程序，我正在查询whereHas的关系数据：

$interactions  = Company::where('is_client', '=', 1)
    ->whereHas('interactionSummaries', function ($query) {
        $query->whereHas('interaction', function ($query2) {
            $query2->whereHas('contactsAssociation', function($query3) {
                $query3->whereHas('company', function ($query4) {
                    $query4->where('type', 'like', 'Research');
                });
            });
        });
})->get();

所以以下是我的模型：

公司：

class Company extends Model
{
     public function contacts()
    {
        return $this->belongsToMany('AppContact', 'company_contact', 'company_id','contact_id');
    }
    public function interactionSummaries()
    {
        return $this->hasMany('AppInteractionSummary', 'company_id');
    }
}

Interactionsummary：

class InteractionSummary extends Model
{
    public function interaction()
    {
        return $this->belongsTo('AppInteraction');
    }   
}

交互：

class Interaction extends Model
{
     public function clientsAssociation()
    {
        return $this->belongsToMany('AppContact', 'contact_client_interaction',  'interaction_id', 'contact_id')->withPivot('company_id')->withTimestamps();
    }
    /**
     * @return IlluminateDatabaseEloquentRelationsBelongsToMany
     */
    public function contactsAssociation()
    {
        return $this->belongsToMany('AppContact', 'contact_interaction',  'interaction_id', 'contact_id')->withPivot('company_id')->withTimestamps();
    }
}

联系人：

class Contact extends Model
{
    public function company()
    {
        return $this
            ->belongsToMany('AppCompany', 'company_contact','contact_id', 'company_id')->withTimestamps();
    }
}

我想获得company模型的contactsAssociation计数，我该如何实现？我的意思是withCount('contactsAssociation')

Answer 1

我不建议使用此解决方案，因为它是沉重的查询和许多用于不必要的集合的内存，即使使用小数据集也可以成为瓶颈。我建议与加入和/或子选择的查询构建器以及更多的SQL方法。

雄辩的方式是：

// Assuming you want to get sum for the Companies collection
$companies = Company::where('is_client', '=', 1)
    // load count on distant model
    ->with(['interactionSummaries.interaction' => function ($q) {
        $q->withCount(['contactsAssociation' => function ($q) {
            $q->whereHas('company', function ($q) {
                $q->where('type', 'like', 'Research');
            });
        }]);
    }])
    ->get()
    ->transform(function ($company) {
        $company->contacts_association_count = $company->interactionSummaries
                                                       ->pluck('interaction.contacts_association_count')
                                                       ->collapse()
                                                       ->sum();
        return $company;
    });
    // then each Company will have the count as an attribute:
    foreach ($companies as $company) {
        $company->contacts_association_count;
    }