php laravel 返回 foreach 中的所有数据

Question

我正在尝试将数据从数据库获取到单个数组中

这就是我编辑的代码，谢谢 Matt C

foreach ($list1 as &$day){
           $pleads = \DB::table('leads')
            ->selectRaw('count(*)') 
            ->whereColumn('owned_by_id', 'users.id')
            ->where('lead_source_id', 7)
            ->whereRaw("DATE(created_at) = '$day'");

            $mleads = \DB::table('leads')
                    ->selectRaw('count(*)')
                    ->whereColumn('owned_by_id', 'users.id')
                    ->where('lead_source_id', 3)
                    ->whereRaw("DATE(created_at) = '$day'");

            $aleads = \DB::table('leads')
                    ->selectRaw('count(*)')
                    ->whereColumn('owned_by_id', 'users.id')
                    ->where('lead_source_id', 4)
                    ->whereRaw("DATE(created_at) = '$day'");

            $personalleads = \DB::table('users') 
                    ->where('id', $id) // User ID
                    ->select('users.id')
                    ->selectSub($pleads, 'pleads')
                    ->selectSub($mleads, 'mleads')
                    ->selectSub($aleads, 'aleads')
                    ->get();
                    return $personalleads;
                     }

当我这样做时，我只得到 1 个输出，例如:

[{"userid":1,"pleads":2,"mleads":1,"aleads":1}]  

但我想要的结果如下

[{"userid":1,"pleads":2,"mleads":1,"aleads":1},{"userid":1,"pleads":0,"mleads":0,"aleads":0},{"userid":1,"pleads":0,"mleads":0,"aleads":0},{"userid":1,"pleads":1,"mleads":0,"aleads":0},{"userid":1,"pleads":1,"mleads":0,"aleads":0},{"userid":1,"pleads":1,"mleads":0,"aleads":0},{"userid":1,"pleads":0,"mleads":0,"aleads":0}]

print_r 我得到的内容

( [items:protected] => Array ( [0] => stdClass Object ( [userid] => 1 [pleads] => 2 [mleads] => 1 [aleads] => 1 ) ) )  ( [items:protected] => Array ( [0] => stdClass Object ( [userid] => 1 [pleads] => 0 [mleads] => 0 [aleads] => 0  ) ) )  ( [items:protected] => Array ( [0] => stdClass Object ( [userid] => 1 [pleads] => 0 [mleads] => 0 [aleads] => 0  ) ) ) ( [items:protected] => Array ( [0] => stdClass Object ( [userid] => 1 [pleads] => 1 [mleads] => 0 [aleads] => 0  ) ) )  ( [items:protected] => Array ( [0] => stdClass Object ( [userid] => 1 [pleads] => 1 [mleads] => 0 [aleads] => 0  ) ) )  ( [items:protected] => Array ( [0] => stdClass Object ( [userid] => 1 [pleads] => 1 [mleads] => 0 [aleads] => 0  ) ) )  ( [items:protected] => Array ( [0] => stdClass Object ( [userid] => 1 [pleads] => 0 [mleads] => 0 [aleads] => 0  ) ) )

非常感谢

Answer 1

在不使用 Eloquent 关系和重用代码的情况下，您应该能够使用 selectSub 获取计数。

$pleads = \DB::table('leads')
            ->selectRaw('count(*)') 
            ->whereColumn('owned_by_id', 'users.id)
            ->where('lead_source_id', 7)
            ->whereRaw("DATE(created_at) = '$day'");

$mleads = DB::table('leads')
        ->selectRaw('count(*)')
        ->whereColumn('owned_by_id', 'users.id)
        ->where('lead_source_id', 3)
        ->whereRaw("DATE(created_at) = '$day'");

$aleads = \DB::table('leads')
        ->selectRaw('count(*)')
        ->whereColumn('owned_by_id', 'users.id)
        ->where('lead_source_id', 4)
        ->whereRaw("DATE(created_at) = '$day'");


$personalleads = \DB::table('user')         
        ->select('users.id')
        ->selectSub($pleads, 'pleads')
        ->selectSub($mleads, 'mleads')
        ->selectSub($aleads, 'aleads')
        ->get()

您应该为每个用户返回类似这样的内容。

[{"id":1,"pleads":2,"mleads":1,"aleads":1}]

有很多方法可以做到这一点

有 Eloquent 关系

User::withCount([
        'leads as pleads' => function ($q) {
            $q->where('lead_source_id', 7)
                ->whereRaw("DATE(created_at) = '$day'");
        },
        'leads as mleads' => function ($q) {
            $q->where('lead_source_id', 3)
                ->whereRaw("DATE(created_at) = '$day'");
        },
        'leads as aleads' => function ($q) {
            $q->where('lead_source_id', 4)
                ->whereRaw("DATE(created_at) = '$day'");
        },
    ])
    ->first()
    ->only(['id', 'pleads','mleads', 'aleads'])