Laravel group by with Eloquent model

I am working with Laravel and Eloquent ORM and after developing controller, model and view for my table I need to extract aggregate information, but I haven't figured out which is the best way or the cleanest "Laravel" way to do that.

I have a db table like this example:

Schema::create('order_items', function (Blueprint $table) {
        $table->increments('id');
        $table->timestamps();
        $table->integer('order_id')->unsigned();
        $table->foreign('order_id')->references('id')->on('orders')->onDelete('cascade');
        $table->string('item_description');
        $table->integer('item_qty');
        $table->status('item_status');
    });

The detail data can be like:

2   2017-02-28 12:48:07 2017-02-28 12:48:31 1   ProductB    2   NEW
4   2017-02-28 12:48:17 2017-02-28 12:48:17 1   ProductC    3   NEW
29  2017-03-10 10:49:47 2017-03-10 10:49:47 1   ProductC    23  CLOSED
40  2017-03-10 10:49:47 2017-03-10 10:49:47 1   ProductB    2   SHIPPED
1   2017-02-28 11:04:28 2017-02-28 11:29:10 3   ProductA    1   NEW
28  2017-03-10 10:49:47 2017-03-10 10:49:47 3   ProductB    22  CLOSED
39  2017-03-10 10:49:47 2017-03-10 10:49:47 3   ProductA    1   SHIPPED
5   2017-02-28 14:36:54 2017-02-28 14:36:54 6   ProductD    4   NEW
6   2017-02-28 14:37:01 2017-02-28 14:37:01 6   ProductD    5   NEW
30  2017-03-10 10:49:47 2017-03-10 10:49:47 6   ProductD    24  CLOSED
41  2017-03-10 10:49:47 2017-03-10 10:49:47 6   ProductC    3   SHIPPED

In the controller I use a scope

public function home()
{
  $onlynew = Orderitem::onlynew ();
  return view('home', compact('onlynew '));
}

The model is

public function scopeonlynew ($query) {
return \DB::select('SELECT item_description, sum(item_qty) qty 
                    FROM   order_items 
                    WHERE item_status = ? 
                    GROUP BY item_description',['NEW']);
}

In the view I can access data in this way

<div class="row">
 <ul>
  @foreach ($onlynew as $newitem)
   <li>{{$newitem->item_description}} - {{$newitem->qty}}</li>
  @endforeach
 </ul>
</div>

Is it possible to use a syntax like the following example or for this kind of query the framework do not allow to use the builder?

return $query->where('item_status', '=', 'NEW')
         ->sum('item_qty')
         ->groupBy('item_description')
         ->orderBy('item_description');

Thanks for any kind of help or suggestion.

Solution

When you use the sum() method it executes the query and returns just the sum. What you want here is to get the aggregate sum AND the description so you have to build the select yourself. This is what the "Laravel" way might look like.

Controller:

public function home()
{
    $onlynew = Orderitem::onlyNew()->get();
    return view('home', compact('onlynew '));
}

Model:

public function scopeOnlyNew($query) 
{
    return $query->where('item_status', '=', 'NEW')
        ->selectRaw('sum(item_qty) as qty, item_description')
        ->groupBy('item_description')
        ->orderBy('item_description');
}

Note the query scope is built a little differently. Query scopes are designed to allow you to group commonly use query constraints into a single method then reuse those constraints with whatever query you're building. They should always return the same query object that they are given instead of creating a new one like DB::select().