How do Laravel process the query when we use Eloquent vs SelectRaw for an scenario like:
Will this fetch data faster:
$data = Records::where('active', 1)->get();
or will this fetch my data faster:
$data = DB::select( DB::raw("SELECT * from records WHERE active ='1'") );
Does using SelectRaw effect on query processing speed when we are dealing with large data around 5,00,000 records?
It's mostly raw queries:
DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
DB::statement('SET @sql = CONCAT(...)');
DB::statement('PREPARE stmt FROM @sql');
DB::statement('EXECUTE stmt');
DB::statement('DEALLOCATE PREPARE stmt');
Try this:
DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
$sql = DB::selectOne('select @sql')->{'@sql'};
ItemDetails::select('item_number', DB::raw('SUM(quantity) as total_quantity'))
->selectRaw($sql)
->groupBy('item_number')
->get();
Because the areas is a collection so you should loop over it like this :
@foreach($users as $u)
<tr role="row" class="odd">
<td class="sorting_1">{{$u->name}}</td>
<td></td>
<td>
@foreach($u->areas as $area)
{{$area->name}},
@endforeach
</td>
<td>{{route('show_user', ['id' => $u->id])}}</td>
</tr></tbody>
@endforeach
And if you want to separate them whith comma for example you can do it like that without looping :
@foreach($users as $u)
<tr role="row" class="odd">
<td class="sorting_1">{{$u->name}}</td>
<td></td>
<td>
{{ $u->areas->pluck('name')->implode(', ') }}
</td>
<td>{{route('show_user', ['id' => $u->id])}}</td>
</tr></tbody>
@endforeach
This can be done in (at least) 2 ways.
Using pure Eloquent model logic:
class Buy extends Model
{
public function getTotalPrice() {
return $this->buyDetails->sum(function($buyDetail) {
return $buyDetail->quantity * $buyDetail->price;
});
}
}
The only issue here is that it needs to fetch all buy details from the database but this is something you need to fetch anyway to display details in the view.
If you wanted to avoid fetching the relation from the database you could build the query manually:
class Buy extends Model
{
public function getTotalPrice() {
return $this->buyDetails()->sum(DB::raw('quantity * price'));
}
}
Use whereHas():
RestaurantFood::with('foodDetail')->whereHas('foodDetail',function($q){
$q->where('type',0);
})->where('id',$id)->get();
There will always be some overhead from using any ORM. However, this is nearly always a non-issue.
For example, while there might be some slight performance increase from writing raw SQL, it's generally dwarfed by the cost of making the query in the first place. You can get a far, far bigger improvement in performance by caching the responses than by rewriting ORM queries in raw SQL.
An ORM does make certain types of slow inefficient queries more likely, but that's something that can be resolved by using a profiler like Clockwork to identify the slow or unnecessary queries and refactoring them away. Most ORM's have tools to handle things like the N+1 problem - for instance, Eloquent has the
with()method to eager-load related tables, which is generally a lot more convenient than explicitly writing a query to do the eager-loading for you.Using an ORM also comes with significant benefits to developers:
If you have a slow query in a web application, then rewriting it as a raw query is probably the very last thing you should consider doing, after:
Writing all your queries as raw queries is a micro-optimization - it's a lot of work for not that much payback, and considering developer time is much more expensive than server time, it's hardly ever worth the bother. Even if you have a single, utterly horrendous query or set of queries that has a huge overhead, there are better ways to deal with it - under those circumstances I'd be inclined to create a stored procedure in a migration and call that rather than making the query directly.