Leveraging Custom Query Builders in Laravel

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!

Popular Posts

My most popular posts

Maximum productivity on remote job
Business

Maximum productivity on remote job

I started my own business and intentionally did my best to work from anywhere in the world. Sometimes I sit with my office with a large 27-inch monitor in my apartment in Cheboksary. Sometimes I’m in the office or in some cafe in another city.

Hello! I am Sergey Emelyanov and I am hardworker
Business PHP

Hello! I am Sergey Emelyanov and I am hardworker

I am a programmer. I am an entrepreneur in my heart. I started making money from the age of 11, in the harsh 90s, handing over glassware to a local store and exchanging it for sweets. I earned so much that was enough for various snacks.

Hire Professional CRM developer for $25 per hour

I will make time for your project. Knowledge of Vtiger CRM, SuiteCRM, Laravel, and Vue.js. I offer cooperation options that will help you take advantage of external experience, optimize costs and reduce risks. Full transparency of all stages of work and accounting for time costs. Pay only development working hours after accepting the task. Accept PayPal and Payoneer payment systems. How to hire professional developer? Just fill in the form

Telegram
@sergeyem
Telephone
+4915211100235