Laravel Group Data: in Collections or in Eloquent Query?

Mukesh Tilokani

2022-06-18 20:48:33

If groupBy on collection is that expensive then would be interesting to know how "with" relation in Laravel is working? Because when we need records that grouped by particular key and need in nested array then we are preferring this groupBy on collection so if it is that expensive then how "with" relation in Laravel is working by providing relation records in nested array?

Trik Ngonlen

2021-07-11 15:02:46

thanks for this great video...
How to group data (with sum and count) per 1 hour? Thank you...

Nathan Budd

2021-05-25 15:33:06

How about if you need all of the data, as well as the count. Eg an online shop. You want to return all of the items, but also the count on the item category?

darknmy

2021-05-12 10:59:17

DBMS SQL can also perform max and avg :-)

Ognyan Dimitrov

2021-04-22 13:05:48

One can do caching rather than grouping here or there. The grouping could become complicated, then caching is the only way out. Then it doesn't really matter collections or DB will do the update cache.

Inti Pontt

2021-04-22 03:09:35

What about using lazy collections to avoid hydrating the models? Switching ->get() by ->cursor() or even ->cursor()->remember() should yield a similar performance increase.

Iordanis Tsapanidis

2021-04-21 07:32:35

If app\\providers classes can return cached queries, what are the pros and cons to actually use it?
example repository here -> github.com/jordantsap/karvali/blob/master/app/Providers/ComposerViewsServiceProvider.php github.com/jordantsap/karvali/blob/master/app/Providers/ComposerViewsServiceProvider.php

Юрий Назаров

2021-04-19 08:19:44

Also - never ever use ->where() on collections. if you need to search in collection by fields values its much faster ti transform collection int associative array like
$array[$obj->field_1][$obj->field2] = $obj. or $array[$obj->field1] = [obj1, $obj2,...]
it was tested on my work project. searching through collection of ~1M objects is 300-1000 times slower (different cases) than transforming it into array and search by key in array

Mohammed Omer

2021-04-18 17:23:48

Sometimes I use this approach using filter instead of normal where clause
The reason is that I need to apply a user search in table that has relation with main table
Ex.
I am displaying current ownership of properties
I need to search in property details so instead of applying where on with function, I just fetch all data and use filter.
There is other way of course but it is just another way.

zachary zheng

2021-04-18 16:26:44

So that means Eloquent Query faster than PHP Collections?

XiEpsilon

2021-04-18 16:21:00

Can you also do a comparison/tutorial with stored procedures ins mysql/postresgql vs these methods? Maybe something with complex joins. I find myself in a similar situation, trying to see which is fastest, yet easiest to maintain in a complex app. Also dB migrations for stored procs. 😁

JohnnyBigodes

2021-04-18 15:58:35

I don't understand this one. How are you grouping it on the database layer and it isn't grouping by time? Normaly for doing this you have to use date_format on the database layer and that also costs a lot of time and so it is an expensive query.

Vinicius Guedes

2021-04-18 14:49:19

I think that keep in DB layer what you can is always better. The results you got in this video are a proof of this. But, I also think we should keep as ORM-ish as we can, because if anytime you need to change your DB or your environment you wouldn't need to change your code.

M Febryan

2021-04-18 11:24:17

This is just my opinion, instead calling facade DB::raw, it can be changed with selectRaw. So the query will Exception::selectRaw()->groupBy()->orderBy()->get()

Bumble Bity

2021-04-18 10:38:07

This scenario just proves that if you work with a lot of records forgto eloqunet unless your customer is prepared pay a lot of money for server.

Jailson Carneiro

2021-04-18 09:53:33

Great help! Thaks for all.

Paltibenlaish

2021-04-18 09:13:54

Hi,

I often encounter the same problem in my work. I have two different DB connections where I cant left join the tables, is the correct way to make two different queries and then map the results?

Or what would be the best approach for this case?

Thank you

Sherwin de Jesus

2021-04-18 08:56:37

I think as much as possible do the queries on the DB server. it's because the queries are optimized on the succeeding calls. Plus DB servers are designed/optimized to performs these queries.
Having it done on the PHP side has several drawbacks: A large amount of data are being transferred from the DB server to the PHP app. Another it's more costly to do the operations (aggregating/sorting) in PHP than in DB server. If the query is too complex, then use views or stored procedures.
just my 2 cents :)

Jesper N

2021-04-18 07:56:42

Also, consider that it's much easier to scale web servers horizontally than it is to scale something like PostgreSQL horizontally. So in some cases, it might actually be a good idea to move some processing from the database to the web server.