March 31th, 2021

Fetching Eloquent models in Laravel with DB Query Builder and Eloquent fromQuery()

DISCLAIMER

Don't assume the code and solution in this article is right for each query in your application.

If your database is set up properly, has the correct indexes and you (eager) load your relationships to avoid N+1 issues, you'll hardly run into performance issues on a day-to-day basis.

Intro

While developing vursion.io, which is a data-dense application with a lot of relationships, I eventually ran into a performance issue were too many models where being loaded.

My application was becoming slow and unresponsive as the amount of data increased and each record was initialized as an Eloquent model.

Approach

After a lot of research and a few failed attempts, it was clear to me I had to let the database do the hard work for me. Taking into consideration that:

I didn't want to go oldskool and write and execute a raw query that would be hard to read and update later on.
The outcome had to be a collection of models, so I can access them as I've grown accustomed.

Solution

After some code diving, I came up with a solution which was a combination of the Database Query Builder and the Eloquent fromQuery() method:

$query = DB::table('YourModelTable');

$models = YourModel::fromQuery($query->toSql())

DISCLAIMER

The example above is focussing on the essentials and for demonstration purposes only! You should always try to use the Eloquent methods available on your model as much as possible.

A real-life example would look more like this:

$query = DB::table('SomeTable')
			->selectRaw('YourModelTable.*')
			->join('AnotherTable', 'SomeTable.id', '=', 'AnotherTable.SomeTableId')
			->join('YourModelTable', 'YourModelTable.id', '=', 'AnotherTable.YourModelTableId')
			->where('SomeTable.SomeColumnName', '=', '?');

$models = YourModel::fromQuery($query->toSql(), [$binding])

The result is one fast query and only the relevant models are loaded in the collection.

Bonus

You can sneak things in your query which get appended to your model, although they are not existing attributes. Below is an example where a count is added to the query.

$query = DB::table('SomeTable')
			->selectRaw('DISTINCT(YourModelTable.id), YourModelTable.*, count(SomeTableId) AS counter')
			->join('AnotherTable', 'SomeTable.id', '=', 'AnotherTable.SomeTableId')
			->join('YourModelTable', 'YourModelTable.id', '=', 'AnotherTable.YourModelTableId')
			->where('SomeTable.SomeColumnName', '=', '?')
			->groupBy('YourModelTable.id');

$models = YourModel::fromQuery($query->toSql(), [$binding])

@foreach ($models as $model)
	{{ $model->counter }}
@endforeach

Tips

Always check the output of explain to see if you need to optimize your joins or database indexes. I can highly recommend Kai Sassnowski's talk at Laracon EU 2018 on 'Things every developer absolutely, positively needs to know about database indexing'.

Use a tool of your choice like Laravel Debugbar or Laravel Telescope to detect performance issues in your application.

A lot of database performance issues only occur with a large dataset. Always test your queries on both small and large datasets.