Collections vs Query Builder? When to use each?

@tykus Thanks! That helps! Basically anything in any of my Controllers, I have no idea if I'm doing things the "right" way. My biggest so far is this one

<?php

namespace App\Http\Controllers;

use App\Models\Movie;
use App\Models\ReelEntry;
use Illuminate\Contracts\View\View;
use Illuminate\Support\Facades\Cache;

class MovieController extends Controller
{
    /**
     * @return View
     */
    public function index(): View
    {
        // Use eager loading with select and only retrieve necessary fields
        $newestMovies = Movie::select(['id', 'slug', 'poster_path'])
            ->withCount([
                'userInteractions',
                'userInteractions as likes_count' => fn($query) => $query->where('is_liked', true),
                'userInteractions as ratings_count' => fn($query) => $query->whereNotNull('rating'),
            ])
            ->withAvg('userInteractions as ratings_avg_rating', 'rating')
            ->latest()
            ->take(5)
            ->get();

        $popularMovies = Movie::select(['id', 'slug', 'poster_path'])
            ->withCount([
                'userInteractions',
                'userInteractions as likes_count' => fn($query) => $query->where('is_liked', true),
                'userInteractions as ratings_count' => fn($query) => $query->whereNotNull('rating'),
            ])
            ->withAvg('userInteractions as ratings_avg_rating', 'rating')
            ->orderByDesc('likes_count')
            ->orderByDesc('ratings_avg_rating')
            ->take(5)
            ->get();

        $latestReviews = ReelEntry::select([
            'id', 'user_id', 'reelable_id', 'reelable_type',
            'rating', 'is_liked', 'watched_at', 'review_content',
        ])
            ->whereNotNull('review_content')
            ->where('reelable_type', Movie::class)
            ->with([
                'reelable:id,slug,poster_path',
                'user:id,username',
            ])
            ->latest('watched_at')
            ->take(5)
            ->get();

        $movies = [
            'newest' => $newestMovies,
            'popular' => $popularMovies,
            'latestReviews' => $latestReviews,
        ];

        return view('movies.index', compact('movies'));
    }

    public function popular()
    {
        return [
            'success' => true,
        ];
    }

    public function castAndCrew(Movie $movie): View
    {
        // Eager load with specific fields to reduce data transfer
        $movie->load([
            'cast.person:id,name,profile_path',
            'crew.person:id,name,profile_path',
        ]);

        return view('movies.cast-and-crew', compact('movie'));
    }

    /**
     * @param  Movie  $movie
     * @return View
     */
    public function show(Movie $movie): View
    {
        $userId = auth()->id();
        $cacheKey = 'movie_details_'.$movie->id;

        // Cache only the base movie details that don't change frequently
        $cachedMovie = Cache::remember($cacheKey, 3600, function () use ($movie) {
            return Movie::withFullDetails()
                ->findOrFail($movie->id);
        });

        // Load user-specific data separately, since it changes frequently and shouldn't be cached
        if ($userId) {
            $cachedMovie->load([
                'userInteractions' => fn($query) => $query->where('user_id', $userId),
                'reelEntries' => fn($query) => $query->where('user_id', $userId)->latest('watched_at'),
            ]);

            // Set user-specific properties using loaded relationships
            $cachedMovie->user_interaction = $cachedMovie->userInteractions->first();
            $cachedMovie->user_reel = $cachedMovie->reelEntries->first();
        } else {
            $cachedMovie->user_interaction = null;
            $cachedMovie->user_reel = null;
        }

        // Load all interactions and calculate stats from them (single query)
        $userInteractions = $movie->userInteractions()->get();

        // Use collection methods for calculations
        $cachedMovie->likes_count = $userInteractions->where('is_liked', true)->count();
        $cachedMovie->ratings_count = $userInteractions->whereNotNull('rating')->count();
        $cachedMovie->avg_rating = $userInteractions->whereNotNull('rating')->avg('rating');

        // Load reviews with related data in a single query
        $cachedMovie->reviews = $movie->reelEntries()
            ->with(['reviews.user', 'user'])
            ->whereHas('reviews')
            ->latest()
            ->get()
            ->flatMap(function ($reelEntry) {
                // Map reviews with their author data for the view
                return $reelEntry->reviews;
            })
            ->sortByDesc('created_at');


        return view('movies.show', ['movie' => $cachedMovie]);
    }
}

It works (minus maybe one piece right now, but I've fixed it in a different commit I havent pushed lol) and doesn't seem to be loading slowly but the verbosity of it isn't the most appealing. Some places I'm using Query Builder and some I'm using Eloquent, but not sure if both are being properly utilized

@ctrlaltdelme Some notes from your code sample:

1. Selecting specific fields is a micro-optimisation. It barely has any effect on query times to the point that it’s usually not worth it.

2. Concepts like “newest” and “popular” could be wrapped up into local scopes on your Movie model:

$newestMovies = Movie::query()->newest()->take(5)->get();
$popularMovies = Movie::query()->popular()->take(5)->get();

3. I have no idea what your popular controller action is doing, given it just returns a JSON response saying success is true. Seems pretty pointless?

4. Your castAndCrew action should definitely be in a different controller. Most likely the index method of a nested resource controller:

Route::get('movies/{movie}/people', [MoviePeopleController::class, 'index']);

class MoviePeopleController extends Controller
{
    public function index(Movie $movie)
    {
        // Return list of people associated with movie...
    }
}

@martinbean Thank you! I made these changes and the scopes help clean up the Controller a lot. One question about order of operations. Does it matter what order you perform certain methods?

For example (and this may be a bad example), but from the same MovieController as before, but scoped out the latestReviews

public function scopeLatestReviews($query)
    {
        return $query->whereHas('reviews', function ($query) {
            $query->whereNotNull('content');
        })
            ->with([
                'reviews' => function ($query) {
                    $query->latest()->take(1) // Only the latest review for that movie
                    ->with('user');
                },
            ])
            ->latest();
    }

Is there a difference in how it is there versus calling, say, the with first, then the whereHas? I'm sure there is a difference but I just don't know how to read these statements out to myself when writing them. It has been really hard.

@martinbean This is very helpful! So is there a good rule of thumb when to use either I could try to remember? It seems just using Query Builder and not Eloquent is better in both of your cases you provided because of the N+1 issue lol

@ctrlaltdelme I think you’re misunderstanding. I am using Eloquent in my examples? I didn’t use “Query Builder” and “not Eloquent“.

@martinbean Oh then yes I am misunderstanding. I suppose I can't really tell when either is used?

@ctrlaltdelme You shouldn't think in terms of rules of thumb. Instead, try to understand what query builders and collections are. The best place to learn about them is Laravel's documentation, and @martinbean already gave a good summary, but here's my attempt at explaining it:

• Query builder is a tool for building database queries, which can then be executed
• Collection is a list of items

The only connection between the two is that when you execute a SELECT statement using a query builder, the results are often returned in a collection object. If you're fetching a single row, e.g. using ->first(), a single object or null is returned instead of a collection.

I'm not sure which parts you find confusing, but here are some possible sources of confusion:

1. Laravel's syntax. Calling $user->posts() returns a query builder. It does not execute a database query yet. Calling $user->posts builds and executes a database query, returning the results as a collection. In other words, it's equivalent to $user->posts()->get(). Importantly, when you call $user->posts, it stores the retrieved posts on that model object, and subsequent calls to $user->posts on the same model won't execute additional database queries.

2. The collection and query builder classes have some methods with the same names, but they are completely different. For example, User::where(...) adds a WHERE SQL condition to the query builder, whereas $users->where(...) filters the $users collection in memory.

3. There are two query builders: the DB query builder and the Eloquent query builder. Without getting too technical, they're almost the same. Eloquent's builder is the one used through model classes, and it has additional context about the model (such as table name and relations), making it more convenient. The DB query builder is generic and requires you specify everything manually, including table names.

So when should you use $user->posts vs. $user->posts()? It depends on what you're doing. If you want an analogy, a query builder is like a restaurant menu and a collection is like a prepared meal:

• If you plan to eat one item, you don't order everything on the menu and then pick one item to eat. That would be equivalent to calling $menu->items->where('id', 5)->first(), where you retrieve every single row from the database to memory, and then pick one of them on the PHP side using collection methods. The correct call is $menu->items()->where('id', 5)->first(), where you retrieve a single row from the database.

• If you plan to order every item on the menu and take a bite of each, you should't place 30 separate orders. That would be equivalent to calling $menu->items()->where('id', $id)->first() in a loop, making a separate database query for each ID. This is an example of the N+1 issue mentioned earlier. Instead, you should retrieve all items at once by calling $menu->items and then loop over them.

In short: retrieve only what you need, and do so in as few database queries as possible. Usually that means a single query per table.

@JussiMannisto That's absolutely fair. I'm sorry I wasn't able to elaborate on what I was getting confused by. I myself didn't really know either. I just knew I had questions rattling in my head and some general confusion on both.

You answered part of my confusion with your provided list with 1 and 2. As for when to use either, I think it just comes down to knowing when to apply it to my use case and the project I'm currently working on. I have plenty of calls to the Model method (i.e. $user->reviews()) where I can then apply other logic such as ordering, joining with with, etc. and it just seemed that was the dominant use-case in my project and it felt like it never seemed optimal to use $user->reviews for example.

@JussiMannisto I'm coming back to this after some time stewing and playing more and I still am not sure I understand when to use either/or. Maybe it's just something intuitive that comes with time and seeing the queries that are made?

I know about Debugbar, but is there another Package that can help me see what DB queries are made? Is Telescope the only one?