Count Relations By Column: 3 Ways To Optimize Eloquent Query

hello guys today we'll take a look at an example from larka's Forum about repeating code in eloquent and how can we optimize it both in terms of performance and in terms of readability as well I have replied on that Forum post and also posted that as article on my laravel daily com but decided to also shoot a video version of steps what we can do to optimize a similar scenario so scenario is this I've recreated that project locally for example you have eloquent model with belongs to many relationship and you need to filter those and your goal is to have count of each of some fields of the relationship for example subscription type in the database we have something like this so I have 100 members and then Member Subscription is a pivot table for many too many and subscription has type so in case of that project that type is full-time part-time and affiliate but that doesn't really matter that's just an example and this is the initial code on optimized code and if we launch that in Postman the result is something like this Json result grouped by the type of subscription I will show you three ways how we can optimize that the obvious performance issue here is of course repeating queries so we have one query here then repeating almost identical query here and here's the third query and also we load with and where has which would fire additional queries or conditions to the database so could we optimize that into somehow one query in fact we can one possible optimization looks like this so we take one query to the database get all the members at once and don't repeat any more calls to the database from here we'll work with the collection of all members and then we filter the result and group them with a callback function however you want and then count the results it does look like an optimization with one query instead of at least three or maybe more but be careful this optimization method that is good only for smaller amount of data because this query is one query but it is heavy in terms of how much data it loads into memory so if you have for example a million members it will load all the million members with their data into memory into RAM and if you have multiple requests to this page or endpoint by multiple users your server May physically run out of memory and you will not even get larval error you will get browser error with from what I remember 500 code or Gateway timeout or something like that basically your server won't be able to serve pages so that is a huge risk that's why I recommend this optimization with the collection only if you have small amount of data and you know that it won't grow really big but that's not the only way to optimize that code let's rethink the whole strategy quite often while browsing the larcas form I see people loading too much data too many relationships although the actual needed data is just part of that relationship so we're loading members with subscriptions although what we're actually counting here is the subscriptions by type so we don't need the members at all and this is exactly what I'm doing by inversing that query doing subscription with count of numbers we don't need the members we only need the count of numbers then I get all those subscriptions with the amount of numbers by type or in this case by ID for example you can do it by type so you could do something like key buy type and then change it to full time for example if you have it as a string or something like that and then it Returns the same result if you don't believe it let's refresh this endpoint and the result didn't change so now we load only what we need we download the members also we're loading the count and not the full relationship and then we're using Key by to provide the results in a better format to transform to the final Json but even that is not all we're not finished yet one more optimization method was suggested to me by Patrick Alstrom on Twitter and what he's suggesting here in a screenshot is to use the MySQL function of case one so in some cases for counting something you can totally skip eloquent and use database functions for example if you don't use other databases if you are only on MySQL you can use MySQL power so instead of that we can execute something like this so DB select it's not an eloquent it's query Builder so selecting the raw query and in this case the query is like this case one type is one then we return one as a result of subscription option and then count the ones for type 1 as full-time part-time and affiliate and the result of that is one row with columns so there will be a column of full-time part-time and affiliate that's why we need to return Json with subscription 0 and the result is also absolutely the same we'll refresh that and it didn't change the downside of this approach however it's not using eloquent functions so for example if you have soft deletes or if you have some Logic on for example accessors or mutators it would not be fired because it's really a raw query to the database you're not working with eloquent or laravel you're working directly with the database this would be probably the fastest way so you're querying the pivot table directly only joining the subscriptions if needed but again it has its own limitations and with all those examples I wanted to emphasize that there was no one right approach for all cases all of those potential optimizations or even other if I didn't mention something they are useful in certain scenarios based on your structure the amount of data the database engine you use and other stuff but I hope from this video you got a few tricks and tips how to optimize eloquent if you want to read more about optimizing eloquent on my laravel daily among premium tutorials I've published this one tutorial optimizing eloquent and DB speed all you need to know huge article 14 minutes read almost 3 000 words this is available for premium members at laraveldaily.com so subscribe to the membership and get all those premium tutorials and courses that's it for this time and see you guys in other videos

Channel: Laravel Daily

Views: 10,118

Keywords:

Id: PYAuAStv6cw

Length: 6min 25sec (385 seconds)

Published: Thu Jan 26 2023