Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Prevent model hydration on Eloquent queries

Is it possible to have an eloquent query builder return StdClass rather then Model?

For example User::where('age', '>', 34)->get() returns a Collection of User models.

Whereas DB::table('users')->where('age', '>', 34)->get() returns a Collection of StdClass objects. Much faster.

Therefore:

Is it possible to prevent hydrating eloquent models and return StdClass objects as a database query builder would, but still leverage the usefulness of an eloquent query builder syntax?


I do not understand why this question is being down voted. Its quite clear on its expectations and wording. People should hover over the down-vote icon and see the suggest "This question does not show any research effort; it is unclear and unhelpful."

There is a strong belief in the Laravel community that if you step outside Eloquent you are prematurely optimizing. This is incorrect, in a situation where there is 10,000+ rows hydrating Eloquent models from database results is extremely very slow. We are talking seconds, sometimes tens of seconds and heavy memory.

Laravelers would then state "Just do a DB::query() its faster". Yes.. Yes... it is im aware... Although I'm asking in this very question... right here... above.. clearly worded and concise... Can I use Eloquent Query Builder to build the query and have StdClass objects returned so as to not hydrate thousands of Eloquent models with all the overhead. Already I hear the Laravelers "Why not use DB::query() then?" Because I'm NOT asking that... Im asking can we use the Eloquent syntax its easier and more readable than doing joins....

Im not here asking advice on application architecture. There are many deep reasons we are wanting to do this such as to match the output of our ElasticSearch repositories etc... But I should not have to get into the why - this is a question and answer forum. Not a discussion or advice platform.

  • Face palm
like image 476
AndrewMcLagan Avatar asked Mar 08 '18 16:03

AndrewMcLagan


2 Answers

Yes, is possible using the 'getQuery' or 'toBase' method. For example:

User::where('age', '>', 34)->getQuery()->get();

or

User::where('age', '>', 34)->toBase()->get();
like image 91
Rodri_gore Avatar answered Sep 19 '22 04:09

Rodri_gore


First of all, this is a really good question. All those haters out there, give the OP a break! He's pro-Laravel and this isnt an Eloquent sucks question, so cool it.

In my opinion,

Hydrating models rarely affects application performance

There are so many ORMs out there and if you look at any framework, these questions keep popping up - but the truth, as I've come to realize, is that ORMs hardly affect performance.

More often than not the culprits are the queries themselves and not the ORM

Let me give you a few examples of why Eloquent models may perhaps be slower than DB facade queries:

1. Model events:

When you have model events (such as saving, creating, etc.) in your models, they sometimes slow down processing. Not to say that events should be avoided, you just need to be careful when and when not to use them

2. Loading Relationships:

Countless times have I seen folks load relationships using appends lists provided by Eloquent and sometimes models have 5-10 relationships. That's 5-10 joins each time you fire an Eloquent query! If you compare that with a DB facade query, it would definitely be faster. But then again, who's the real culprit? Not the ORM, it's the queries (with the extra joins!)

As an example, not so long someone asked a question on this and he/she wondered why an Eloquent query was slower than a raw one. Check it out!

3. Not understanding what triggers an Eloquent query

This is by far the most prominent reason why people think ORMs are slower. They usually (not always) don't understand what triggers a query.

As an example, lets say you want to update a products table and set the price of product #25 to $250.

Perhaps, you write in your controller, the following:

$id = 25;
$product = Product::findOrFail($id);
$product->price = 250;
$product->save();

Then, your colleague says hey, this is super slow. Try using DB facade. So you write:

$id = 25;
DB::table('products')->where('product_id', $id)->update(['price' => 250]);

And boom! It's faster. Again, the culprit isn't the ORM. It's the query. The one above is actually 2 queries, the findOrFail triggers a select * query and the save triggers an update query.

You can and should write this as a single query using Eloquent ORM like so:

Product::where('product_id', 25)->update(['price' => 250]);

Some Good Practices for Query Optimization

  1. Have your database do most of the work instead of PHP: E.g. instead of iterating over Eloquent collections, perhaps frame your DB query in such a manner that the database does the work for you.

  2. Mass Updates Over Single Updates: Pretty obvious. Avoid saving models in for loops, yuk!

  3. For heavy queries, use transactions: DB transactions avoid re-indexing on every insert. If you really need to call say thousands of inserts/update queries in a single function call, wrap them into a transaction

  4. Last but not the least, when in doubt check your query: If you're ever ever ever in doubt, that perhaps the ORM is the real culprit - think again! Check your query, try and optimize it.

If the ORM is slowing things down, use obervers or the Laravel debugbar to compare the queries with and without the ORM. More often than not, you'll find that the queries are different, and the difference isnt in hydration but the actual queries themselves!

like image 35
Paras Avatar answered Sep 20 '22 04:09

Paras