107 lines
4.2 KiB
Markdown
107 lines
4.2 KiB
Markdown
# Advanced query best practices
|
|
|
|
## Select single relationship values with subqueries
|
|
|
|
When you need only one value from a has-many relationship, consider a correlated subquery with `addSelect()` instead of loading the entire relationship. The subquery selects the value as part of the main query, without an additional relationship query.
|
|
|
|
```php
|
|
public function scopeWithLastLoginAt($query): void
|
|
{
|
|
$query->addSelect([
|
|
'last_login_at' => Login::select('created_at')
|
|
->whereColumn('user_id', 'users.id')
|
|
->latest()
|
|
->take(1),
|
|
])->withCasts(['last_login_at' => 'datetime']);
|
|
}
|
|
```
|
|
|
|
## Create dynamic relationships with a subquery foreign key
|
|
|
|
The same pattern can select a foreign key and expose the selected model through a `belongsTo` relationship. Eager loading that relationship still runs a separate query, but the pattern avoids loading the full has-many collection.
|
|
|
|
```php
|
|
public function lastLogin(): BelongsTo
|
|
{
|
|
return $this->belongsTo(Login::class, 'last_login_id');
|
|
}
|
|
|
|
public function scopeWithLastLogin($query): void
|
|
{
|
|
$query->addSelect([
|
|
'last_login_id' => Login::select('id')
|
|
->whereColumn('user_id', 'users.id')
|
|
->latest()
|
|
->take(1),
|
|
])->with('lastLogin');
|
|
}
|
|
```
|
|
|
|
## Combine related counts with conditional aggregates
|
|
|
|
Use conditional aggregates to combine several counts over the same filtered data set into one query. Use `toBase()` when you need only scalar values and model hydration adds nothing. Confirm the expression syntax against the application's database engine.
|
|
|
|
```php
|
|
$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();
|
|
```
|
|
|
|
## Reuse loaded parent models with `setRelation()`
|
|
|
|
When a parent and its children are already loaded and code also accesses `$child->parent`, set the inverse relationship to the existing parent instance. Setting the inverse relationship avoids an extra lazy-loading query for each child.
|
|
|
|
```php
|
|
$feature->load('comments.user');
|
|
$feature->comments->each->setRelation('feature', $feature);
|
|
```
|
|
|
|
## Compare `whereHas()` with an `IN` subquery
|
|
|
|
`whereHas()` typically produces an `EXISTS` subquery, while `whereIn()` can express the same filter with an `IN` subquery. Either form can be faster, depending on the database engine, indexes, cardinality, and query plan. Measure both forms with representative data. Neither subquery loads its result set into PHP memory.
|
|
|
|
Option using `EXISTS`:
|
|
|
|
```php
|
|
$query->whereHas('company', fn ($q) => $q->where('name', 'like', $term));
|
|
```
|
|
|
|
Option using `IN`:
|
|
|
|
```php
|
|
$query->whereIn('company_id', Company::where('name', 'like', $term)->select('id'));
|
|
```
|
|
|
|
## Measure two simple queries against one complex query
|
|
|
|
Two targeted queries can outperform one complex correlated subquery or join when the first query is highly selective. They also add a database round trip, can transfer a large identifier list, and do not provide a single-query consistency snapshot. Decide from query plans and production-like measurements.
|
|
|
|
## Design composite indexes for the query
|
|
|
|
For common multi-column sorts, consider a composite index whose column order supports the query's filters and ordering. Database engines may combine indexes or choose an explicit sort, so matching the `ORDER BY` list alone does not guarantee that the engine uses the index. Verify the query plan.
|
|
|
|
```php
|
|
// Migration
|
|
$table->index(['last_name', 'first_name']);
|
|
|
|
// Query that this index may support
|
|
User::query()->orderBy('last_name')->orderBy('first_name')->paginate();
|
|
```
|
|
|
|
## Consider a correlated subquery for has-many ordering
|
|
|
|
When sorting by one value from a has-many relationship, a direct join can duplicate parent rows unless it first reduces the related table to one row per parent. A correlated subquery in `orderBy()` is often simpler, but its performance depends on the query plan and supporting indexes.
|
|
|
|
```php
|
|
public function scopeOrderByLastLogin($query): void
|
|
{
|
|
$query->orderByDesc(Login::select('created_at')
|
|
->whereColumn('user_id', 'users.id')
|
|
->latest()
|
|
->take(1)
|
|
);
|
|
}
|
|
```
|