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

Compiling assets with Laravel Mix

Compiling assets with Laravel Mix

Laravel Mix provides a fluent API for defining Webpack build steps
Read more...
Laravel Scout: Standard way to use algolia search with your Laravel application

Laravel Scout: Standard way to use algolia search with your Laravel application

Algolia search engine within your Laravel application using Laravel Scout.
Read more...
Use ReactJS with your Laravel application

Use ReactJS with your Laravel application

The standard way to start using ReactJS with your laravel application.
Read more...