php laravel return all data from foreach

Question

I am trying to get data from db into a single array

This what i have edit he Code Thanks 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;
                     }

when i do this i get only 1 output ex:

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

but what i want as result is below

[{"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 of what i get

( [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  ) ) )

Thank you very much

You're not using $day anywhere. You're just getting the exact same data over and over again and putting it in the same place — apokryfos
– apokryfos, Commented Mar 23, 2019 at 15:59

Matt C · Accepted Answer · 2019-03-23 17:05:00Z

1

Without using Eloquent relationships and reusing your code you should be able to get the counts using 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()

You should get back something like this for each user.

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

There are many ways to do this though

With Eloquent Relationships

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'])

edited Mar 23, 2019 at 17:05

answered Mar 23, 2019 at 16:53

Matt C

5483 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

mohamed adel Over a year ago

First i would like to thank you very much for your answer i have been looking everywhere for someone to help me on this task i got [{"id":1,"pleads":2,"mleads":1,"aleads":1}] but this code is inside foreach i want to return

[{"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}]

instead of just 1 line

mohamed adel Over a year ago

Many thanks i would really appreciate it if you helped me with the foreach loop problem cuz i have been trying to solve it for 8 hours straight but with no luck

Matt C Over a year ago

Should work without the foreach now and return all users.

mohamed adel Over a year ago

thank you but the problem is that i have to use the foreach because i want to retrive the data from db by each day of the last month and when i use the return inside the foreach it returns only the 1st day i will edit the post

Matt C Over a year ago

Can you post that part of the code? This portion only loops through each user.

|

Collectives™ on Stack Overflow

php laravel return all data from foreach

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related