如何将非重复计数SQL查询转换为Laravel口才查询 [英] How to convert Distinct Count SQL query into a Laravel Eloquent query

问题描述

我想将此SQL查询转换为laravel雄辩的查询.这样我就可以创建

I would like to convert this SQL query into a laravel eloquent query. So I can create

SELECT
    count(distinct article_id) as assigned
FROM
    actions
WHERE
    action_id = 1 and
    set_id = 1

我可以将查询转换为有效的原始请求

I can translate the query into a raw request which works

DB::table('actions')->select(DB::raw('count(DISTINCT article_id) as assigned'))
        ->where('action_id', 1)
        ->where('set_id', 1)
        ->get();

但是我想弄清楚该怎么做

But I would like to figure out how to do it something like this

$sets = Set::withCount(['actions as assigned' => function ($q) {
    $q->distinct()
        ->select('article_id')
        ->where('action_id', '=', 1);
}])->get();

或这个

$sets = Set::withCount(['actions as assigned' => function ($q) {
    $q->distinct('article_id')
        ->where('action_id', '=', 1);
}])->get();

最终，我想让我的Set模型包含这样的作用域方法

Eventually I would like to have my Set model contain a scoped method like this

public function scopeWithAssignedCount($query){
    $query->withCount(['actions as assigned' => function ($q) {
        $q->distinct('article_id')
            ->where('action_id', '=', 1);
    }])
}

因此我可以在控制器中的单个呼叫中添加多个计数

So I can add multiple counts to a single call in my controller

$sets = Set::withAssignedCount()
    ->withUnassignedCount()
    ->where('palm' => $face)
    ->get();

---

按评论编辑

---

我想使用以下类似的记录来做不同的记录.

---

Edited per comments

---

I would like to use something like the below with distinct records.

public function scopeWithAssignedCount($query)
{
    $query->withCount(['actions as assigned' => function ($q) {
        $q->where('action_id', 1);
    }]);
}

推荐答案

检查此查询

$count = Set::select('article_id as assigned');
if(!empty($action_id)){
   $count = $count->where('action_id', $action_id); 
}
if(!empty($set_id)){
   $count = $count->where('set_id', $set_id); 
}
$count = $count->distinct('article_id')->count('article_id');

这篇关于如何将非重复计数SQL查询转换为Laravel口才查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！