Eloquent Performance Pattern - notes

use laravel debugbar

composer require barryvdh/laravel-debugbar --dev

use index to make it faster

// everything in column field needs to be indexed
order by {field}, where {field} = 1

do not use *

// *
User::query()
	->with('author')
	->latest('published_at')
	->get()
	->groupBy(fn($post) => $post->published_at->year);

use select

User::query()
	->select('id','title','slug','published_at','author_id') // <- this
	->with('author')
	->latest('published_at')
	->get()
	->groupBy(fn($post) => $post->published_at->year);

within with() can select too

User::query()
	->select('id','title','slug','published_at','author_id')
	->with('author:id,name') // <- this
	->latest('published_at')
	->get()
	->groupBy(fn($post) => $post->published_at->year);

When using with() loads more data, subquery only can return 1 column back

$user = User::query()
	->addSelect(['last_login_at' => 
		Login::select('created_at')
			->whereColumn('user_id', 'users.id')
			->latest()
			->take(1)
	]) // instead of with
	->withCasts(['last_login_at' => 'datetime']) // instead of diffForHumans()
	->orderBy('name')
	->paginate();

dynamic relationship

$user = User::query()
	->withLastLogin()
	->orderBy('name')
	->paginate();

public function lastLogin() {
	return $this->belongsTo(Login::class);
}

public function scopeWithLastLogin($query) {
	$query->addSelect(['last_login_id' => Login::select('id')
		->whereColumn('user_id', 'users.id')
		->latest()
		->take(1)
	])->with('lastLogin');
}

conditional aggregates

select
	count(case when status = 'Requested' then 1 end) as requested,
	count(case when status = 'Planned' then 1 end) as planned,
	count(case when status = 'Completed' then 1 end) as completed
from features

$statuses = Feature::toBase()
	->selectRaw("count(case when status = 'Requested' then 1 end) as requested")
	->selectRaw("count(case when status = 'Planned' then 1 end) as planned")
	->selectRaw("count(case when status = 'Completed' then 1 end) as completed")
	->first();

optimizing circular relationship

public function show(Feature $feature)
{
	$feature->load('comments.user');
	$feature->comments->each->setRelation('feature',$feature);
	return view('feature', ['feature' => $feature]);
}

multi column searching

$users = User::query()
	->search(request('search'))
	->with('company')
	->paginate();
return view('users', ['users' => $users]);

public function scopeSearch($query, string $terms = null) {
	collect(explode(' ', $terms))->filter()->each(function($term) use ($query) {
		$term = '%' . $term . '%';
		$query->where(function($query) use ($term) {
			$query->where('first_name', 'like', $term)
				->orWhere('last_name', 'like', $term)
				->orWhereHas('company', function($query) use ($term) {
					$query->where('name', 'like', $term);
				});
		}
	}
}

normalize field using regex

Schema::created('companies', function(Blueprint $table) {
	...
	$table->string('first_name_normalize')
	->virtualAs("regexp_replace(first_name, '[^A-Za-z0-9]', '')")->index();
	...
});

limit rows by user_id with loggedin user

public function scopeVisibleTo($query, User $user) {
	if ($user->is_owner) {
		return;
	}
	$query->where('sales_rep_id', $user->id);
}

compound index where it’s a multiple index into 1, the order of the index matters, the example needs to be last_name first then the first_name not the other way around.

$table->index(['last_name','first_name']);

subquery has one relationship, order without using leftjoin

$user = User::query()
	->orderBy(Company::select('name')
		->whereColumn('user_id','users.id')
		->orderBy('name')
		->take(1)
	)
	->with('company')
	->paginate();

subquery belongs to relationship,

order by without using leftjoin but for this case leftjoin is faster so use left join.

//join (faster)
$user = User::query()
	->select('users,*')
	->join('companies','companies.id','=','users.company_id')
	->orderBy('companies.name')
	->with('company')
	->paginate();
//subquery
$user = User::query()
	->orderBy(Company::select('name')
		->whereColumn('id','users.company_id')
		->orderBy('name')
		->first()
	)
	->with('company')
	->paginate();

subquery has many relationship

$user = User::query()
	->select('users.*')
	->join('logins', 'logins.user_id', '=', 'users.id')
	->orderByDesc('logins.created_at')
	->groupBy('users.id') // error below
	->withLastLogin()
	->paginate();

trouble when adding groupBy → order by clause is not in group by clause and contains nonaggregated column

// use this
->orderByRaw('max(logins.created_at) desc')

subquery

$user = User::query()
	->select('users.*')
	->orderByDesc(Login::select('created_at')
		->whereColumn('user_id', 'users.id')
		->latest()
		->take(1)
	)
	->withLastLogin()
	->paginate();

Subquery belongs to many

$books = Book::query()
	->select('books.*')
	->join('checkouts', 'checkouts.book_id', '=', 'books.id')
	->groupBy('books.id')
	->orderByRaw('max(checkouts.borrowed_date) desc')
	->withLastCheckout()
	->with('lastCheckout.user')
	->paginate();

// to subquery
$books = Book::query()
	->orderByDesc(Checkout::select('borrowed_date')
		->whereColumn('book_id', 'books.id')
		->latest('borrowed_date')
		->take(1)
	)
	->withLastCheckout()
	->with('lastCheckout.user')
	->paginate();

// another way
$books = Book::query()
	->orderByDesc(function($query) {
			$query->select('borrowed_date')
				->from('checkouts)
				->whereColumn('book_id', 'books.id')
				->latest('borrowed_date')
				->take(1)
		}
	)
	->withLastCheckout()
	->with('lastCheckout.user')
	->paginate();

// another way
$books = Book::query()
	orderBy(User::select('name')
		->join('checkouts', 'checkouts.book_id', '=', 'books.id')
		->whereColumn('checkouts.book_id', 'books.id')
		->latest(checkouts.borrowed_date')
		->take(1)
	)
	->withLastCheckout()
	->with('lastCheckout.user')
	->paginate();

order with nulls first

$book = Book::query()
	->with('user')
	->orderByDesc('user_id') // messes up the order name
	->orderBy('name')
	->paginate();

$book = Book::query()
	->with('user')
	->orderByRaw('user_id is null')
	->orderBy('name')
	->paginate();

// this query shows the null first
$users = User::query()
	->when(request('sort') === 'town', function($query) {
			$query->orderBy('town', request('direction'));
		})
	->orderBy('name')
	->paginate();

$users = User::query()
	->when(request('sort') === 'town', function($query) {
			$query->orderByRaw('town is null')
				->orderBy('town', request('direction'));
		})
	->orderBy('name')
	->paginate();

Ordering by custom algorithm

status using when

public function scopeOrderByStatus($query, $direction)
{
	$query->orderBy(DB::raw('
		case
			when status = "Requested" then 1
			when status = "Approved" then 2
			when status = "Completed" then 3
		end
	'), $direction);
}

then in migration

Schema::create('features', function(Blueprint $table) {
	$table->rawIndex('(
		case
			when status = "Requested" then 1
			when status = "Approved" then 2
			when status = "Completed" then 3
		end
	'), 'feature_status_ranking_index');
			
}

activity using count

$features = Feature::query()
	->withCount('comments', 'votes')
	...

public function scopeOrderByActivity($query, $direction)
{
	$query->orderBy(
		DB::raw('(votes_count + (comments_count *))'),
		$direction
	)
}

Filtering and sorting

$users = User::query()
	->orderByBirthday()
	->whereBirthdayThisWeek()

public function scopeOrderByBirthday($query)
{
	$query->orderByRaw('date_format(birth_date, "%m-%d")');
}

Schema::create('features', function(Blueprint $table) {
	$table->rawIndex("date_format(birth_date, '%m-%d'), name", 'users_birthday_name_index');
}

public function scopeWhereBirthdayThisWeek($query)
{
	$query->whereRaw('date_format(birth_date, "%m-%d") between ? and ?', [
		Carbon::now()->startOfWeek()->format('m-d'),
		Carbon::now()->endOfWeek()->format('m-d'),
	]);

	// OR

	$query->whereRaw(date_format(birthdate, '%m-%d') in (?,?,?,?,?,?) ,
		iterator_to_array($dates));
}

Fulltext

DB::statement('
	create fulltext index posts_fulltext_index
	on posts(title, body)
	with parser ngram
');

// example
select id, title, match(title, body) against ('fox' in boolean mode) as score
from posts
where match(title, body) against ('fox' in boolean mode)

$posts = Post::query()
	->with('author')
	->when(request('search'),function($query, $search) {
		$query->selectRaw('*,match(title, body) against(? in boolean mode)' as score, [$search])
			->whereRaw('match(title, body) against(? in boolean mode)', [$search])
	}, function($query) {
		$query->latest('published_at');
	})
	->paginate();

Getting distance between geo points

Schema::create('stores', function(Blueprint $table) {
	...
	$table->point('location', 4326);
	...
});

$myLocation = [-79.47, 43.14];
$stores = Store::query()
	->selectDistanceTo($myLocation)
	->withinDistanceTo($myLocation, 10000)
	->orderByDistance($myLocation)
	->paginate();

public function scopeWithinDistanceTo($query, [] $coordinates, int $distance) {
	$distance = strtolower($direction) === 'asc' ? 'asc', : 'desc';
	$query->whereRaw('ST_Distance(
		location,
		ST_SRID(Point(?, ?),4326),
	) <= ?', $direction, $coordinates);
});

public function scopeWithinDistanceTo($query, [] $coordinates, int $distance) {
	$query->whereRaw('ST_Distance(
		ST_SRID(Point(longitude, latitude),4326),
		ST_SRID(Point(?, ?),4326),
	) <= ?', [...$coordinates, $distance]);
});

public function scopeSelectDistanceTo($query, array $coordinates) {
	if (is_null($query->getQuery()->columns)) {
		$query->select('*');
	}

	$query->selectRaw('ST_Distance(
		ST_SRID(Point(longitude, latitude),4326),
		ST_SRID(Point(?, ?),4326),
	) as distance', $coordinates);
});

// change to this

	$query->selectRaw('ST_Distance(
		location,
		ST_SRID(Point(?, ?),4326),
	) as distance', $coordinates);

filter by geospatial

Schema::create('customer', function(Blueprint $table) {
	...
	$table->point('location');
	...
});

Schema::create('regions', function(Blueprint $table) {
	...
	$table->geometry('geometry');
	...
});

public function scopeInRegion($query, $region) {
	$query->whereRaw('ST_Contains(?, customers.location)', [$region->geometry]);
});