Hello, laravel web developers! In Laravel, the Query Builder is a powerful tool for building database queries, but sometimes, we need to extend its functionality to handle more complex scenarios.
In this article, I’ll show you how to customize Laravel’s Query Builder using macros and higher-order functions. This allows us to create reusable logic for tasks like complex filtering, sorting, and data aggregation.
Laravel Query Builder with Macros & Higher-Order Functions
Laravel allows you to define custom macros that extend the functionality of existing classes, including the Query Builder. Macros can be defined in the AppServiceProvider or a dedicated service provider.
App\Providers\AppServiceProvider.php
use Illuminate\Support\ServiceProvider;
use Illuminate\Database\Query\Builder;
class AppServiceProvider extends ServiceProvider
{
public function boot()
{
Builder::macro('whereLike', function ($column, $value) {
return $this->where($column, 'LIKE', '%' . $value . '%');
});
}
public function register()
{
//
}
}
This macro adds a whereLike method to the Query Builder, allowing us to perform "LIKE" queries more concisely
Once the macro is defined, you can use it in your query just like any other Query Builder method
$users = DB::table('users')
->whereLike('name', 'John')
->get();
In this example, the whereLike macro simplifies searching for users whose names contain "John."
Higher-order functions allow you to encapsulate logic into reusable methods. In this example, we’ll create a higher-order function to handle sorting.
use Illuminate\Database\Eloquent\Builder;
Builder::macro('applySorting', function ($column, $direction = 'asc') {
return $this->orderBy($column, $direction);
});
This applySorting function can be reused across different queries for sorting purposes.
Now that we’ve created the applySorting macro, we can use it in queries.
$sortedUsers = DB::table('users')
->applySorting('created_at', 'desc')
->get();
This query will return users sorted by their creation date in descending order.
You can combine multiple macros and higher-order functions to build complex query logic.
$filteredSortedUsers = DB::table('users')
->whereLike('email', 'example.com')
->applySorting('created_at', 'desc')
->get();
In this case, we’re filtering users by email and then sorting the results by creation date.
Let’s add an aggregation example using a custom macro for summing values.
Builder::macro('sumColumn', function ($column) {
return $this->sum($column);
});
// Using the sumColumn macro
$totalSales = DB::table('orders')
->where('status', 'completed')
->sumColumn('total_amount');
Here, we use the sumColumn macro to calculate the total sales for completed orders.
You might also like:
In this article, we will see examples of carbon add hours in laravel 8. Carbon provides many functions like ad...
Dec-09-2020
In this tutorial, we will see how to get hourly data in mysql. Many times we need to get hourly data or we are required...
Feb-04-2022
In this article, we will see how to import and export Excel & CSV files in laravel 10. Here, we will learn about lar...
Mar-08-2023
In this article, we will see how to create a pie chart in laravel 9 using highcharts. A pie chart is a circular statisti...
Oct-05-2022