Eloquent Custom Query

Jun 29, 2022 4 minutes read
Eloquent Custom Queries

Eloquent is the heart of the Laravel application, it is the most important part that gives us the pleasure of development. There are probably more parts you enjoy in Laravel, but Eloquent and the way you interact with the database they are key points of an amazing DX in that framework.

Custom queries

While developing your application, you may have encountered displaying or processing of different kind of lists of objects for the same entity. E.g. User entity, in the admin panel you probably need to display a list of all users, there may also be a list of only active users or those who are ending subscriptions in the next day. And so on. 

There are many such situations, especially if you develop a large application and have many entities. So wherever in your code you use Model::query() or Model::where() these are custom queries. If you create them in a well-organized way, you can create a testable system that will be resistant to changes in business logic.

Where to store custom queries?

In my experience, it's best to create them directly in Eloquent models. No matter if you are building a large or a small system.

Instead of doing that:

// controller
public function index()
	$users = User::query()
		->where('is_active', true)
	return view('users', ['users' => $users]);

Try to do this:

// model
use Illuminate\Database\Eloquent\Builder;

public static function queryOnlyActive(): Builder
	return self::query()->where('is_active', true);
// controller
public function index()
	$users = User::queryOnlyActive()->paginate();
	return view('users', ['users' => $users]);

By doing this you can achieve some kind of DRY principle. Your code will become consistence and more readable for other team members. Also you will not waste time looking for a suitable place for the next custom query you'd like to create.

The key part of the code above is to return a Builder instead of primitive or generic types. This will allow you to keep some flexibility for any consumer of this method. By returning the Builder you still have a lot of freedom in what query you send to the database, few examples are below:

// ...

Avoid DB queries

In your business logic, try to avoid creating queries using Illuminate\Support\Facades\DB. Always try to register models for each table in your database. Even if it's just an intermediate table. Really it will save you a lot of time.

Of course there are reasons for using DB helper (e.g. reading data from different database), but IMHO it's easier to use Eloquent queries in your business logic in order to avoid a difference in the results, to have consistent structures and readable code.

Before you use Repository pattern

In all framework battles, there is always the key player - ORM. It is clear that ORM is the backbone of most database systems. Also sometimes somewhere between the ORM and your business logic, you can come across Repository pattern. There are many articles on that, so I used a quote from Martin Fowler.

A repository performs the tasks of an intermediary between the domain model layers and data mapping, acting in a similar way to a set of domain objects in memory.

Martin Fowler

I think the first sentence of this quote is already suggesting we should forget about it. Eloquent has no data mapping feature or any additional layer for that. And that's good! Eloquent is an implementation of Active Record. If you take a look how queries are processed in Laravel Eloquent you will realize that truly it makes no sense to use Repository pattern.

Also note that the Repository pattern described by Mr. Martin is not the same repository you might have seen in Symfony. I have often met people who have had many years of experience with Symfony, and have tried to force themselves into the practices they have acquired over the years. In my opinion, this is a small misunderstanding. It's possible that this is why some people create separate classes / namespaces and call them UserRepository. Of course, you can create additional classes with the Repository suffix, but the name itself may cause more confusion for next hero in the project.


Database structures are already complicated enough, let's try not to complicate the code.

Disclaimer: The opinions expressed here are my own and do not necessarily represent those of current or past employers.
Comments (0)