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