Heads up: more of a technical talk. Code will be available on GitHub later

When you're building a web application, performance is going to be an important piece of it. Keep happy customers, reduce number of servers needed, etc.

One particular issue I see is how they use their database (or not). For example, doing operations in Collections instead of in the database.

Example app is AnglersOnline (social media platform for sports fishermen).

In the app, name and club. In database structures, there's users, clubs, buddies (pivot table between users) and trips. Fishermen can post what user, what lake in the trips table. In this example, 250,000 rows

Requirements

1. Only show fishermen if you're both in the same club or are already buddies

php artisan make:policy UserPolicy

Now say that the user policy checks the club and buddies list.

Want to look in the database? Not quite yet, since we just created that policy. Need to do stuff in PHP.

Went from 4 to 7 MB, slower, and now we're hydrating all of the models.

Easy solution is to add it in as a model scope ($query->where('club_id', $user->club_id)->orWhereIn('id', $user->buddies->pluck('id')))

With the model scope, DB runs 2ms. 40-50ms, member about 4MB, hydrating few number of models. Awesome.

Sort by buddies first?

Actually created orderBySub macro that allows you to order by a subquery. Query still runs in a happy time!

Add the last trip date

Remember, there's a quarter-million trips.

// N+1 problem
$user->trips->sortByDesc('went_at')->first()->went->diffForHumans()

// After eager-loading, now it's even heavier because we're doing another N+1 query anyways

// Maybe? It's better, but again, N+1 issue. N+1 don't necessarily have to be the worst, but it's a trade-off.
$user->trips()->latest('went_at')

// Or...subqueries
public function scopeWithLastTripDate($query)
{
    $query->addSubSelect('last_trip_at', function ($query) {
        $query->select('went_at')...
    });
}

Now I need to add last_trip_at in my $casts array. But now...$user->last_trip_at->diffForHumans() doesn't have N+1 and is light!

What lake did they go to on the last trip?

Add another scope? scopeWithLastTripLake, copy from the other scope (replacing references as appropriate)

What if I want a link to the trip?

Well...now we're adding in a good number of scopes. Maybe a dynamic relationship?

Pretend you have a lastTrip() belongsTo thing that belongs to Trip. Then change your scope to just be with last trip, select ID, then and you'll have something that looks a lot closer to a Laravel relationship ($user->lastTrip->id)

No longer need the last_trip_at scope!

We did go up one database query for the query for trips, but it's only one (since it's getting eager loaded)

Eloquent doesn't know or care that last_trip_id isn't actually on the table, the subquery makes it all good. Anytime you can avoid caching, that's a win, but subqueries can be just as fantastic about making a performant page