Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can the with function be used with a GroupBy clause in Laravel Eloquent?

Can the with function be used with a GroupBy clause in Laravel Eloquent? Does it serve any purpose if I have specific items to select using the Select Clause?

Following is the query that I currently have

Order::with('Campaign')
            ->where('isapproved','=','Y')
            ->groupBy('campaign_id')
            ->orderBy(DB::raw('COUNT(id)','desc'))
            ->get(array(DB::raw('COUNT(id) as totalsales')));

The order table has a column name campaign_id which belongsTo the table named campaigns. I would like to get the total count of the sales from the order table against each campaign and need to show in the following manner.

Total Sales     Campaign
-------------------------
  200            Campaign1
  500            Campaign2
  300            Campaign3

Should I have to perform a specific select or can I access the values of the Campaign table from the above query?

like image 698
Abishek Avatar asked May 26 '13 20:05

Abishek


1 Answers

If the referenced column required by the Model specified on the With function is retrieved in the SELECT clause, then the With function is taken into consideration by the above query. The rectified query will be

$groupedSalesCampaign = Order::with('Campaign')
            ->where('isapproved','=','Y')
            ->groupBy('campaign_id')
            ->orderBy(DB::raw('COUNT(id)','desc'))
            ->get(array(DB::raw('COUNT(id) as totalsales'),'campaign_id'));

This way the Campaign information can be retrieved using

foreach($groupedSalesCampaign as $campaign)
{
      Log::info($campaign->foo->bar);
}

Edited.

like image 127
Abishek Avatar answered Oct 22 '22 06:10

Abishek