Leveraging Custom Query Builders in Laravel

In the realm of web development, query builders play a vital role in database management. Essentially, a query builder is an interface that allows you to construct a query in a dynamic and programmatic way. It helps to create SQL queries with a fluent and more readable interface.

In Laravel, one of the most popular PHP frameworks, we have the Illuminate\Database\Eloquent\Builder class, which is used to interact with database records through the Eloquent ORM (Object-Relational Mapping).

Let's consider a simple example:

$query = Customer::where('name', 'John Doe');

In the above example, we're invoking the where method on the Customer model class, which is an instance of the Illuminate\Database\Eloquent\Builder class. 

However, in a complex CRM (Customer Relationship Management) system, we will often need more sophisticated queries. Laravel provides an elegant solution to this problem: Custom Query Builders.

Custom Query Builders allow us to extend the base Builder class and create a specific set of query methods related to a particular model. This way, we could define more complex queries in a clean and readable way.

Let's consider an example related to a CRM system. Here, we extend the base Builder class to create a custom query builder:

class CustomerBuilder extends Builder

{

    public function whereHasRecentOrders(): self

    {

        $oneMonthAgo = now()->subMonth();

        return $this->whereHas('orders', function ($query) use ($oneMonthAgo) {

            $query->where('created_at', '>=', $oneMonthAgo);

        });

    }

}

The whereHasRecentOrders method checks if a customer has any orders placed within the last month. We can use this method like this:

$activeCustomers = Customer::whereHasRecentOrders()->get();

In the builder, we return self to be able to chain Eloquent methods:

$activeCustomers = Customer::whereHasRecentOrders()->where('country', 'USA')->get();

Remember, the query builder methods don't necessarily need to be used for scopes. You can define almost any kind of method in the builder. For example:

class TransactionBuilder extends Builder

{

    public function totalSpentByCustomer(Customer $customer): float

    {

        return $this->where('customer_id', $customer->id)->sum('amount');

    }

}

$totalSpent = Transaction::totalSpentByCustomer($customer);

The above method calculates the total amount spent by a given customer. It can't be chained because it returns a float.

Moreover, we can save and change models. Just see following example:

namespace Domain\Invoice\Builders\Broadcast;

use Illuminate\Database\Eloquent\Builder;

class InvoiceBuilder extends Builder

{

    public function markAsPaid(): void

    {

        $this->model->paid_at = now();

        $this->model->save();

    }

}

Finally, we need to tell Laravel to use our custom query builder. We can do this by overriding the newEloquentBuilder method in the model:

class Customer extends Model

{

    public function newEloquentBuilder($query): CustomerBuilder

    {

        return new CustomerBuilder($query);

    }

}

And there you have it! By using custom query builders, we can keep our models clean and simple, while also encapsulating complex queries in a separate class. This is especially useful in large systems like CRMs, where complex queries are often a necessity. 

In conclusion, custom query builders provide a powerful and elegant way to manage complex queries in Laravel. They allow for more readable code and make working with large databases a breeze. So, next time you're working on a complex Laravel project, consider leveraging the power of custom query builders!