Friday, March 10, 2017

Laravel group by with Elequent model

I am working with Laravel and Eloquent ORM and after developing controller,model and view for my table I need to extract agregate informations but I don't figure out wich 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> - </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.



via AlexMI
Labels: ,

Advertisement