Using Laravel's Eloquent ORM Efficiently

Using Laravel's Eloquent ORM Efficiently

May 31, 2019 / Ramesh Mhetre

Database query performance holds the major part when it comes to your application performance in general. Optimizing your big queries is always a challenging job for developers. If you are using an ORM then its actually extra pain to find efficient way to do it in your ORM.

We will see some tips & tricks which might help you understand your Eloquent ORM better.

Eloquent's where() method

Where clause is the most frequently used clause of relational database. It can be used in many different conditions together like AND, OR, IN NULL, NOT NULL, GREATER THAN, EQUALS TO, NOT EQUALS TO & so on.

Here are some ways to use it with Laravel Eloquent:

$posts = Post::where("id", "!=", 1)->get();
// Any of the following may be used as the second parameter (and use the third param for the value)
// =, <, >, <=, >=, <>, !=, LIKE, NOT LIKE, BETWEEN, ILIKE

$posts = Post::where(function ($query) {
    $query->where('title', 'LIKE', '%laravel%')
        ->orWhere('title', 'LIKE', '%eloquent%');
})->get();

$posts = Post::whereRaw('views > ? and votes > ?', [500, 50])->get();

$posts = Post::whereRaw(DB::raw("author_id IN (SELECT user_id FROM user_permissions GROUP BY user_permissions.user_id WHERE role = 1)"))->get();

$posts = Post::whereExists(function($query){
    $query->select(DB::raw(1))
        ->from('user_credits')
        ->whereRaw('user_credits.user_id = posts.author_id')
        ->groupBy('user_credits.user_id')
        ->havingRaw("COUNT(*) > 3");
})->get();
// Any of the following may be used instead of Detail::whereExists
// ->orWhereExists(), ->whereNotExists(), ->orWhereNotExists()

$posts = Post::whereIn('author_id', [1,2,3])->get();
// Any of the following may be used instead of Detail::whereExists
// ->orWhereIn(),

$posts = Post::whereNotIn('author_id', function($query){
    $query->select('user_id')
        ->from('banned_users')
        ->groupBy('banned_users.user_id');
})->get();

// Any of the following may be used instead of Post::whereExists
// ->whereNotIn(), ->orWhereNotIn

Eloquent where clause with date

Eloquent has special functions for date conditions in the query.

->whereDay()
->whereMonth('birth_date', '=', 12)
->whereYear('birth_date', '>', 2000)
->whereDate('birth_date', '>', '1970-12-12')

Eloquent where null or not null

->whereNull('is_published')
->orWhereNull('is_published')
->whereNotNull('is_published')
->orWhereNotNull('is_published')

Ordering hasMany relation data

We can add ->orderBy() to the hasMany relationship to get ordered output of a specified column.

return $this->hasMany('Posts::class')->orderBy('published_date');

restore() soft deleted Eloquent Models

If you are using soft delete in your model & has enabled added SoftDelete trait in your model, you can do restore with restore() on the model object.

Post::withTrashed()->where("id", 1)->restore()

SQL JOIN's with Eloquent

Eloquent's join() method also has many make efficient queries at the end.

<?php
$post = Post::where('id', $postId)
    ->join('tags', 'tags.post_id','=', 'posts.id')
    ->select('tags.id', 'post.id')->first();

$post = Post::where('id', $postId)
    ->leftJoin('post_views', 'post_views.post_id', '=', 'posts.id')
    ->first();

$post = Post::where('id', $postId)
    ->join('comments',function($join) use($userId) {
        $join->on('comments.post_id', '=', 'posts.id')
            ->on('comments.user_id', '=', $userId, 'and', true);
    })->first();

Cache Data in Eloquent

You can retrieve an item from the cache without loading it all the item.

$posts = Cache::remember('posts', $seconds, function () {
 return DB::table('posts')->get();
});

//OR to remember forever

$posts = Cache::rememberForever('posts', function () {
 return DB::table('posts')->get();
});

Here you can find complete guide of Laravel Eloquent on the official website.



Similar Articles

What is new to Laravel 6?

What is new to Laravel 6?

Today Laravel 6 has finally officially released. Let's have a detailed look at what is changing in Laravel 6.
Read more...
Connect your laravel application to Sqlite DB

Connect your laravel application to Sqlite DB

Quickly connecting with sqlite database within laravel application
Read more...
Laravel v6 & Laravel Vapor is going to release soon 🚀

Laravel v6 & Laravel Vapor is going to release soon 🚀

Laravel v6 is is releasing soon. Serverless laravel is next big thing in laravel community!
Read more...