Database records -- Select from time range, group by ID, sum numeric columns

replied 8 years ago

Do You Mean Like That :

//Controller 
$users = DB::table('users')
                ->whereBetween('date', ['10/01', '10/02'])->get();
       

        return view('index',compact('users')) ;

//View
@foreach ($users as $user)
    {{ $element->userid }}
    {{ $element->username }}
    {{ $element->int1 }}
    {{ $element->int2 }}
@endforeach

0

xylotism

replied 8 years ago

@ABDELRHMAN

whereBetween works successfully for my data, but compact isn't doing what it should be (I think).

Without compact I get multiple rows like my first table.
With compact my view is getting passed an empty array rather than one with users.

I don't know if it makes any difference, but I'm passing additional data to my view in a multidimensional array like so:
[
['users' => (array) $users],
['startmonth' => (string) $sm],
['endmonth' => (string) $em],
]

Last updated 8 years ago.

0

xylotism

replied 8 years ago

Solution

I think I just got it actually. Instead of using Eloquent to query my DB, I used the query builder in the following format:

$users = DB::table('users')  
->select(DB::Raw('sum(int1) as int1'))  
->addselect(DB::Raw('sum(int2) as int2'))  
->addselect('id')  
->addselect('userid')  
->addselect('username')  
->addselect('date')  
->whereBetween('date', [$startdate, $enddate])  
->groupBy('userid')  
->get();

then passing the $users array to the view like so:

$data['users'] = $users;  
$data['startdate'] = $startdate;  
$data['enddate'] = $enddate;  

return view('view.index')->with('data', $data);

I'll have to do some more testing to be sure it's giving me exactly what I want, but for now it looks good. Hopefully this helps others with the same problem!

0

Moderators