Matt Matt - 5 months ago 28
SQL Question

Laravel DB query with count

I have a query where i need to use count and have a normal query.

Normal

select sum(total) as Total,AffID,user_id from Affiliates
group by AffID order by user_id ASC


And Then

foreach($result_array as $row)
{
echo $row['total']."<br/>";
echo $row['AffID']."<br/>";
echo $row['user_id ']."<br/>";
}


In Laravel i have tried

$affdata = DB::select('Affiliates')
->whereRaw('CompletedDate >= curdate()')
->groupBy('AffID')
->orderBy('user_id', 'ASC')
->sum('total');
->get();
foreach($affdata as $row)
{
echo $row->AffID ."<br>";
echo $row->total ."<br>";
}


But it seems to throw out an error. As i need to echo out the AFFID along with calculating the Total

Answer

This is the correct query

$affdata = DB::table('Affiliates')->select(DB::raw('sum(total) as total'), 'AffID', 'user_id')
            ->whereRaw('CompletedDate >= curdate()')
            ->groupBy('AffID')
            ->orderBy('user_id', 'ASC')
            ->get();
Comments