Eloquent Efficiency

Install debugbar. It shows you all the queries (2 in both cases)

The only question would be if it were quicker to load both posts and user tables or to do a join of both tables in one query.

In most cases the difference is negligible, but suppose you had 200 posts authored by 3 people. With a join, those three users are going to be in the dataset over and over, consuming more memory than a collection of 200 posts and a collection of 3 users.

And by seeing the generated queries you will see eloquent or active record is basically a shortcut language that is converted to normal SQL at runtime. So if using query Builder instead and writing the two queries you actually achieve the same thing.

I see many questions where someone is asking how do I convert this SQL to eloquent when in reality there is no such thing when it's converted back anyway. Just some thoughts here on active record.

And in a very large database, millions of Records active record loses efficiency. Also in situations where you do need complex joins like a business application in many cases it's not Well Suited.

But active record definitely has some good usages as well.

$post->author() returns the relation instance so you can use the query builder. $post->author()->username should not even work, you are not working on the model instance.

If you are carefully retrieving a low amount of models you should not have any problems. Be aware of situations when you want to use eager loading of related models (prevent n+1).

You might want to switch to the DB facade or even raw SQL when you need to work on large amounts of records. Embrace and use Eloquent until you run into problems and really need to optimize.

@Snapey @skliche Thank you both for the great answers! I experimented a bit, and basically here are two approaches to retrieve comments on the post page, would you do it the same way?

###Approach #1 - Lazy (using basic model relationships):

Post Controller:

public function index(Post $post) {
    return view('forum.posts.index', compact('post'));
}

View Template

@foreach($post->comments as $comment)
    <h3>{{ $comment->message }}</h3>
    <p>by {{ $comment->user->name }}</p>
@endforeach

I retrieve 20 replies per page, so that would basically output 50 mysql queries per request, which is completely insane.

###Approach #2 (Currently using):

Post Controller:

public function index(Post $post) {
    // get all comments first, format them in array => results in one query basically
    $comments = $post->comments()->with('user')->get()->toArray();
    
    return view('forum.posts.index', compact('post', 'comments'));
}

View Template

@foreach($comments as $comment)
    <h3>{{ $comment['message']}}</h3>
    <p>by {{ $comment['user']['name'] }}</p>
@endforeach

Is there any better way to do it? Am I missing something? Thanks in advanced!

public function index(Post $post) {
    // no need to pass this, since auth()->user() is available in the view
    //$user  = auth()->user(); 


    //$comments = $post->comments()->with('user')->get()->toArray();

    // lazy load the comments for this post
    $post->load('comments');

    return view('forum.posts.index', compact('post'));
}

See "lazy eager loading" https://laravel.com/docs/5.6/eloquent-relationships#lazy-eager-loading

If you are iterating over data and always need related data I'd use eager loading. In your case you'd use lazy eager loading, as already suggested.

Replace

$comments = $post->comments()->with('user')->get()->toArray();

with

$post->load('comments.user');

Specifying 'comments.user' will load both the comments and the nested relationship user.

Then don't convert to array, keep on using the models or collections you receive.

return view('forum.posts.index', compact('post'));

and

@foreach($post->comments as $comment)
    <h3>{{ $comment->message }}</h3>
    <p>by {{ $comment->user->name }}</p>
@endforeach

@skliche Thanks once again; however, this method still executes more queries than needed. 4 queries to be exact:

• 1 to get the post
• 2 to get its author
• 3 to get comments
• 4 to get its authors

Currently, this is what I am using, and it uses 2 queries exactly:

$post = \DB::table('posts as p')
    ->join('users as u', 'u.id', '=', 'p.user_id')
    ->where('p.id', '=', $id)
    ->select('p.*', 'u.nickname', 'u.role_id', 'u.posts as u_posts', 'u.comments as u_comments', 'u.likes as u_likes')
    ->take(1)->get()->toArray();
$post = $post[0];
        
$comments = \DB::table('comments as c')
    ->join('posts as p', 'p.id', '=', 'c.post_id')
    ->join('users as u', 'u.id', '=', 'c.user_id')
    ->where('p.id', '=', $id)
    ->select('c.*', 'u.nickname', 'u.posts as u_posts', 'u.comments as u_comments', 'u.likes as u_likes')
    ->take(20)->get()->toArray(); 

return view('forum.posts.index', compact('post', 'comments'));

It doesn't look pretty, but it does exactly what I need - two queries with all the inner joins and stuff. The only question is how can I make it more compact in the code...

dont sweat the number of queries. 4 different queries seems fine to me. But of course active record makes your code much simpler to understand by using multiple simple queries

Sure, if you don't need the Eloquent models and are fine with the raw data arrays, use the DB facade.

You can hide the ugliness within a query object, something like

$posts = Posts::withUsers();

Whether the performance difference is worth the trouble depends on your application, the amount of data, ... I'd prefer the single readable line of code as long as possible. The other way looks a lot like overkill to me for such a small set of data.

@Snapey @skliche Thank you both once again! After several attempts to achieve the most efficient and convenient result, here is what I've done:

PostsController.php

public function index(Post $post) {
    $user = auth()->user();
    $post->load('user'); // loading post's author
    $post->load('likers.user'); // loading all post's like with their authors
    $post->load('comments.user'); // loading all post's comments with their authors
    $post->load('comments.likers.user'); // loading all post's comment's likes with their authors
        
    return view('forum.posts.index', compact('user', 'post'));
}

I ended up with 10 queries including 1 query to get current user (auth()->user()) to load everything that I needed (all the information about posts, comments, authors and likes)

ok. Good.

And nothing you have done prevents you coming back and optimising the queries in the future when the site is massive!

This one $user = auth()->user(); has already been queried as part of the authentication guard so you don't have much choice with this. Your statement just connects what is already loaded.