Using the Query Builder from a Model in Laravel
When you have different database queries based on different query parameters in a request, you can split out the query into different parts, instead of using multiple different queries for different outcomes.
For example, let's say you want to have one endpoint, /model-a
with optional query parameters model_b_id: numeric
, and sort_by: 'asc' | 'desc'
/model-a
returns a list of the User's related modelA
/model-a/?model_b_id=1
returns a list of the User's related modelA
that is related to modelB
with id of 1.
/model-a/?sort_by='asc'
returns a list of the User's related `modelA' in ascending order
Using Query Builder, you can (in my opinion, quite elegantly) split the query into multiple parts, and build the query according to the incoming requests' parameters.
/**
* instead of using different queries in different conditions, can just use
* one query for multiple use cases
*
* Relationships:
* User hasMany ModelA
* ModelA hasMany ModelB
*/
// in SortBy.php
enum SortBy: string
{
case Ascending = 'asc';
case Descending = 'desc';
}
// in MyController.php
// return ModelA of a user, based on ModelB's id
public function __invoke(Request $request)
{
// Validate the model_b_id and sortBy
$validated = $request->validate([
'model_b_id' => ['sometimes', 'numeric', 'exists:model_b_table_name,id'],
'sort_by' => [Rule::enum(SortBy::class), 'nullable'],
]);
/** @var User */
$user = $request->user();
// Get modelA and sortBy; default to null
$model_b_id = Arr::get($validated, 'model_b_id');
$sort_by = Arr::get($validated, 'sort_by');
// Initialize Query Builder
$query = $user->modelA();
/**
* if $model_b is NOT NULL,
* return user's modelA filtered by model_b_id from the query param.
*/
if ($model_b) {
$query->where('model_b_id', $model_b_id);
}
// sort in DB query
$model_a_collection = $query->orderBy('updated_at', $sort_by ?? 'desc')->get();
return ModelAResource::collection($model_a_collection);
}
Notice how I can just add $query->where(...)
in a separate condition, rather than building a whole new query when $model_b
exists (i.e. when model_b_id
is passed in the request parameter)!