Eloquent or Query Builder: When to Use Which?
HTML-код
- Опубликовано: 4 окт 2024
- I've been asked this question quite a lot, so decided to discuss the pros and cons of each approach.
Spatie Ray review: • New Tool! Spatie Ray: ...
My course "Eloquent: Expert Level"
laraveldaily.t...
- - - -
Support the channel by checking out my products:
My Laravel courses membership: laraveldaily.t...
Laravel QuickAdminPanel: bit.ly/quickad...
Livewire Kit Components: livewirekit.com
- - - -
Other places to follow:
My weekly Laravel newsletter: bit.ly/laravel-...
My personal Twitter: / povilaskorop 
Хобби
One thing you missed is that when you use query builder you don't get advantages of model events
Thank you, great content as always. My experience:
1. Simple INSERTS: Eloquent
2. Complex INSERTS: Query Builder
3. Simple READS : Eloquent
4. Complex READS : Query Builder
5. Very Complex READS : Raw SQL
What i miss out for not using eloquent fully is eloquent convenience/magic such as model events. I also find myself not using Eloquent Relationships.
My thought's exactly
Thanks for the tip buddy,..
My thought also
just wondering, could you give me an example of a complex insert? thanks :D ..
Great
Exactly as you say! I am using eloquent mainly and query builder for specific things. The beauty of Laravel: various tools, you can use the best out of both worlds!
Right
I think as a back end developer what is matter is performance and when u are good enough in sql writing queries is a piece of cake . Thanks for this video it is really helpful
you just made things clearer for me when trying to understand this awesome framework, thanks boss
I think it all depends on the case, but for the most part for general use it is better eloquent.
It is unreal that you managed to hook me into your QAP, but I have to say... I adore your content and I adore your services. Much obliged
I normally use Eloquent. But when I do charts, reports, export and etc, query builder rules.
Thank You. I'm getting answers for my questions.
Ha suffered a lot with Eloquent - at the end i managed to solve it that i prepared a view in MySQL and joined this with raw query. I needed sums, with group by with 2 additional joined tables.
Well, in my case, I like to use Raw Queries. Correct me if I'm wrong, but nothing can be more performative than Raw Queries, because I think there are almost no layers betweeen Laravel em PHP PDO/MySQLi/whatever.
Of course the table changing name is more effective with tradicional model approach, but you can always refactor you code with your editor. For me is not a big deal.
And one very important downside of using only EloquentORM or Query Builder is that this is not available on the tools you use to manage directly you database on the server (SequelPro, HeidiSQL, PHPMyAdmin, etc.) Therefore, there can be a time when you will forget how to write propor SQL queries because you are used to run them with Query Builder. Just my 5 cents
Totally agree I hate these complications esp with subjoins and where clauses in query builder, they just complicate things. If we are backend devs we MUST know how to use sql syntax; and everyone tell me to use eloquent or QB to make code readable means bull**it
I've had many situations, where I have had to switch to DB facade. More complicated queries usually tend to be much slower in Eloquent than using the facade or doing raw queries. Eloqent is programmer-friendly.
In some cases I used Stored Procedures for complex queries, and the performance is better. But the daily work, I use eloquent.
Do you have a common workflow for moving to stored procedures? I was recently considering this for a project I'm working on, but wasn't sure if laravel supported these types of migrations. I was able to get by with a custom query and redis caching. It wasn't an often updated data set
My case IF:
(1) - I not need Model and relations for one thinks like this scenario
DB::connection('sqlsrv')
->table('places')
->leftJoin('operators', 'places.OperatorID', '=', 'operators.ID')
->leftJoin('users', 'operators.ID', '=', 'users.OperatorID')
->select('operators.Nick', 'users.ID as UserID')
->where('places.PIN',$pin)
->where('operators.Locked', 0)
->first();
(2) - Must Call to stored Procedure in Sql Server like this scenario many triggers, procedures and a loot in Database
$result = DB::connection("sqlsrv")->statement("exec p_SaveOrderWithRelation @cOrderID='{$order->id}', @cPaymentID='{$payment->id}', @cShiftID='{$shift->id}', @cSimple=0");
return $result->QueneID;
(3) - If Underestand SqlSerwer and have Expirience in sql Querys, reports and advanced querys in T-SQL and have a loot of logic in Database like triggers, functions...
Query Builder is more natural to build your result data
if you have 100 tables and more, and aplications is only web part of full ecosystem (Desktop, Web, Mobile, Devices...)
Elequent for all in own aplication
QueryBuilder for strong and complicated relations like big reports
All rights!
When working with t-sql or Pl/sql, then use Orm is total fail.
Model must get data from View (DB) or Stored procedure like simple Select.
Love your videos. Small bite size chunks and very informative!. Thanks.
I've used query builder for search data by any column. For me it's faster than scope search. I've tested.
Want more? My course "Eloquent: Expert Level"
laraveldaily.com/course/eloquent-the-expert-level
Thanks! Never thought about that difference.
When you use eager loading with eloquent the 'in' clause is used. In bigger systems the 'in' clause may easily have more than a thousand items (a company can sell more than 1000 articles). If so you cannot use it with an Oracle DB (12c) because it has a limit of 1000 items in an 'in' clause.
If I remember correctly Laravel will split the IN clause into multiple IN clauses each not exceeding 1000 entries. I could be wrong though.
@@OLIV3R_YT That would be a new feature then, because that wasn't the case about a year ago. I found out because Oracle is our primary db. On one side it would be great if Laravel would split it up; on the other side: more than a 1000 items in an 'in' clause.... doesn't feel great in my mind.
Lazy collection is automatic?
You can easily resolve this by using chunks. Even though above 1.000 records per time your server memory will feel it's heart being smashed with a giant rock. :(
@@leonardoldr This is not about the amount of records that is retrieved, but the number of entries in the where ... in (1, 2, 3, max 1000 in Oracle). It is the eager loading of Laravel that creates the 'in' clause. That why we use Query builder and Raw queries. (And yes, we use chunking).
I prefered to have two options that one. I use queryBuilder when SQL so hard, long and subquerys then I use Eloquent in sql easy, no more than 3 relationship. I'm junior developper. Sorry for my english.
In my local DB table with 1M records the different between query builder (DB::(table_name)->get()) and eloquent model request (Model::all()) is about 7-10% (obviously in favor query builder).
I use both for Eloquent and Query Builder. Thanks for this info
You can combine some advantages of Eloquent Builder and both less memory usage like using Query Builder (without hidrate Eloquent models) by using toBase() method of Eloquent Builder.
Recien llego al canal por una duda puntual y el video me encanto!! Ya me suscribi para seguir aprovechando siempre la info que venga de gente mas experimentada
Loool What the problem with those people hitting dislike for great tutorials likes this
Thank you for useful video Povilas. Just right now I'm testing a database having 500k+ products with many relations and time loses for eloquent queries are noticeable.
You can use Model::table() instead of hardcoded table name.
You can make table names dynamic when using query builder. So you dont have to update every query when the table name is changing.
$users = DB::table(with(new User())->getTable())->get();
Or you can have a static method in the models, and delegate this logic there and use like this:
$users = DB::table(User::getTable())->get();
What makes laravel special is been able to combine power, performance and ease of use. I know there are other frameworks that perform much better than laravel, but are way much difficult to use.
Awesome tutorial as always. Keep it up👍👍👍👍
If you use some method that that eloquent does not have even if you call it via model facade it falls back to query builder. Like Product::join('products_categories')... so in some sence the diff is really negligible how you access it. $product->category->name or $product->category_name. So its not really an advantage. With our IDEs you can easily find all the cases where you have to change. So one line change versus 20 lines of change its not so different. So we are talking performance vs how quick you can change something for developer. But the probability of this needs changing is very low. So I don't even use eloquent unless it already used and I need to add some stuff.
You are 100 percent correct
Excellent advice! Thanks for the hard work.
Thank you very much for this 🙂
I think we are putting a cart in front of the horse here trying to see what is faster or more readable. Decision what to use should depend on application requirements, user stories, entity objects.
Great Video for beginners
Thanks for your Video, i want a video from you-> about curd operation (edit), actually how to put old value on combo box, using eloquent and db query both
This varies more if it has a hasMany relationship.
I use query builder a lot. But only on MySQL VIEWS. This way MySQL can do what it does best, all the heavy lifting.
For normal CRUD, I always use Eloquent.
One of the main reasons i use eloquent is events. you forgot to mention that. in lots of situations we cleanup system using eloquent events, like uploaded image files.
Thank you for sharing , this is very intersting.
However, i had to switch from elequent to query builder because it was giving me the wrong results (i'm sure there's a fix that i could'nt find ).
My table has a string id composed of numbers and letters such as 1234M001,
the elequent truncates the id and return only 1234.
Thanks for this awesome info❤️
I always use eloquent when I modify the data in db like creating or updating. I use Query builder when extracting data in database
I'm old school. If I could I would use raw SQL. So, for 90% of my queries I use Query Builder. I have tried to use Eloquent but time and time again, I find myself spending more time searching through the Eloquent docs than writing code especially when it comes to complex queries with multiple joins.
Nothing Beats the Classics
In page where need to load alot of information such as dashboard, export to excel, can use Query Builder or Raw SQL. For page such as product page, can use eloquent with paginate so will break the data to small rows althought the the product got 1 millions rows.
I used the query builder if I didn't need the model. I needed the stdClass object because it was convenient to work with.
Hello sir, your tutorials are very helpful for me as a beginner in laravel. May I ask, do you have any student qr code attendance system demo project provided in this channel?
With all fairness that QueryBuilder should be in the model too as it has no place in the controller. then you could use $this->table (or self::) and you solved the "rename table" problem without much of a hassle. not as if renaming a table would be a frequent task.
On the other hand this is just ONE query, imagine running twice as slow on every one of them. and eating up 5 times the memory. Yes there are sacrifices on the developer side if you use DB query but keeping your site lightning fast vs "well its easier to read" is not gonna win much fight with the management and with the endusers..
"sure it takes 5 seconds to render, but boy i can read the code with no effort"
i am all in love with Laravel, but performance is key in every application, and running twice as slow quickly adds up. And the issue is that this masquerades the problem so well that less experienced developers will have no idea where the code gets so slow as they expect that laravel is superfast under the hood. (which is true most of the time)
(note : i love automation, and i love that frameworks making life easier and coding faster, and would be super happy to sacrifice *some* performance, but this example shows that it is not "some" it is a lot. - and i know you can wrap things up and store them in cache for faster access, my point is that with no cache (or empty) things could get dreadfully slow and most of junior / mid devs wont have a clue, and they will accept that pulling 1k of data from a DB is slow, when the truth is DB could handle much bigger datasets much faster)
anyway: keep up the good work, your videos are good and helpful.
I prefer to use query builder for API in general when i need a better response time and i have many req/sec. But, if performance is not a request for app, then Eloquent will be a better solution
query builder is useful if I want to generate reports where the queries require multiple joins from different tables. I also use them when I need to use complex nested queries. On rare occasion, query builder is much easierto use than eloquent when dealing with complex queries.
If you have relationship defined and schema is perfectly build then elequent is better !
Wow, thanks, i thought it same as each other
Please make more video on Laravel Eloquent and try to make Many to many complete crud
2:03 Isn't it the other way around? The Eloquent query is "faster" / uses less database time because it's 27.37 + 4.02 = ~31ms while the Doctrine query with the join uses 52.5ms of database time. This makes the Eloquent query more scalable (only locking one table at a time too I think?).
excellent video, a couple of months ago looking for how to improve my performance I saw your video of refactor controller and you post When to Use Dependency Injection, Services and Static Methods, I would like to know when it is good to use these things also adding resources and collections all attached in a project
For those dependency injections and design patterns in general, there's no ONE answer on when to use them, you need to feel it when your project gets bigger and harder to control, then you need SOME patterns. Maybe my this article will help: blog.quickadminpanel.com/laravel-when-to-use-dependency-injection-services-and-static-methods/
Thanks, I'll give you another read in more detail.
Eloquent is all about readibility and managing relationships more easily. If you have lots of tables with dozans of relationships then Eloquent is better option for sure.
DB all the way with any speed requirements and Eloquent for Crud. We regularly interrogate tables with millions of records and queries were rewritten to use DB for speed. Also that same database imports 100,000 records per day and changing to DB meant we saved 50 percent of the daily load time. However crud, where speed isn't an issue, it's much easier to use eloquent.
When you say eloquent for CRUD, that basically covers every aspect of queries, doesn’t it? So what do you mean by ‘DB all the way’? Thanks in advance I’m just confused with this comment but I’m interested to know your thoughts since you do this often.
@@msw0629 thanks for the question. When I said crud I should have said user interaction, like editing, creating, deleting one record via a user interface, where a process taking 0.25 seconds instead of 0.1 seconds won't be noticeable but if I'm importing thousands of records then I won't use eloquent because 10,000 lots of 0.15 seconds (for example) is an awful lot of additional seconds.
@@TonyByng Thanks Tony for the prompt reply and clarification. Would you care to shed some light on this: eloquent or DB when loading a collection?
For example, let’s say we are loading 50 tasks from a table with 10k records (which is a pretty common scenario for task index page), would it be better to use DB query? This may be from the servers angle as such request can be frequent and consuming, e.g. every time after deleting/edit a task, send an API request to reload the task table.
Thank you for your time.
@@msw0629 personally I'd use eloquent for that. That would be essentially one query that selects 50 records from a table whereas if I was updating those 10,000 task records every day then I would use DB. Ultimately anything that is more than using PDO is potentially adding more processing to the request so if you are ultra sensitive to speed you wouldn't use any laravel routines. It's just weighing up whether in any particular scenario you are more sensitive to speed. And if you are needing the quickest response I would start caching where appropriate but for index pages with a table of 50 paginated records then I'd still use eloquent. Hope that helps
@@TonyByng It does. Thanks for your insights on Eloquent and cache. One last question: when the requested data requires comprehensive relationships (still 50 out of 10k records table, but use query builders that includes 10 joined tables). In this case, I suppose your expertise would favor DB?
you sir... just awesome
Mr. Korop, first of all thank you so much for the videos. You really are a good teacher. Quick question: Im using query builder to fill report data, but when I need to use a related model currency field Laravel casts it as string. If I want the correct casting I need to insert the related field in the casts array of the main model. Is it a good pratice? Shouldnt the cast array content only the model fields? tks in advanced.
Thank you so much.
Thanks
Hi, can you make a tutorial on gitlab automatic tests? It would be really useful and a lot of people would be interested in I think. Your channel is amazing.
I don't work with Gitlab, only Github, so can't make a tutorial on it.
@@LaravelDaily oh, okey :/ Thank you for response.
Excelent thanks! for future videos could you go a little deeper in topics as soft deletes, accesors scopes, and magic methods, please. Thank you.
I did already, in my course "Laravel Eloquent: Expert Level" laraveldaily.teachable.com/p/laravel-eloquent-expert-level
Also you may do a search in my channel and you will find some smaller videos about accessors and scopes definitely, maybe other topics too.
Another great video 👍
Thanks!
And Also for Example your Clients Company has already have an EXISTING DATABASE SYSTEM they used for so many years, you'll have create to create MODELS for them from scratch if you use Eloquent. i know Eloquent is very easy to use. but this will overshadow one of most important Skills in using SQL, in longer terms this will only dull my senses resulting for many loopholes in my projects.
So u use mostly Raw queries/expressions or just Query Builder? Or query builder can overshadow skills in using SQL too? What do u recommend?
you are amazing
Awesome quantity of recording
How we can check API performance? What is better to use for APIs
Laravel Telescope
I think eloquent is too magical so I use it in specific cases and thanks for this video
Thank u
thanks alot ❤
Laravel e-commerce playlist create for beginner helpful please sir.😊
one project create for helpful.
can you make a video about how make a fast filter and serach method for a very large table?
Simple: jQuery Datatables with Server-Side Rendering github.com/yajra/laravel-datatables
Eloquent ❤️
but the result of queries is different, in the first you receive both products and categories, and in the second products and ids of categories, in second query you need to use crossjoin or I miss something?
hello sir, how about eagerload in query builder with complex syntax?
can you make a tutorial? or can I see in your video which one?
thanks
What do you mean complex syntax? Give an example, please
@@LaravelDaily
Here's what I came up with... but I wonder if there is a better way?
If we have this:
$accounts = DB::table('accounts')->paginate(25);
foreach($accounts as $account) {
$ids[] = $account->id;
}
$locations = DB::table('locations')->whereIn('account_id', $ids)->get();
And we want to add the locations to each individual account, we can do this:
foreach($accounts as $account) {
$account->locations = array();
foreach($locations as $location) {
if($account->id == $location->account_id) {
$account->locations[] = $location;
}
}
}
how to get simple code with query builder, because if me use eloquent sometimes it so take long time queries, and then i choose manually query builder. thanks
does it support COMPOUND KEYS?
actually these 2 eloquent queries to the db were faster than query builder, the reason qb was faster was because of php. But we always can compile php using opcache and use small chunks of data to optimize eloquent
Awesome videos
Error handling is much easy with eloquent
Hi sir I have a question
Can I pass a veriable in where clause from db like:
Where ('transaction_id', $transaction_id)->get();
I have one question with Eloquent, how return random row with where. User::where('role', $role)->inRandomOrder()->first() or User::where('role', $role)->random()->first()? These both ways are correct, just using different approaches under the hood or one of them is incorret?
that second query you posted is wrong and will throw an error, there is no random() method in the query builder.
What I think you meant was User::where('role, $role)->get()->random()->first(), as the ->get() returns a collection and ->random() is a method that can be called on a collection.
->inRandomOrder() adds to the query that get build and adds a RAND... function to the underlying sql query, and thus the processing to pushed to SQL rather than in your PHP that the ->random() call does since its acting on the collection in the PHP.
So do you want the load in SQL or PHP? Generally you want to use SQL more as the database is built to be efficient at working with data than PHP is.
So ->inRandomOrder() is probably the best option and I would say the better practise.
Sir I am your regular view can you create a video on role and permission through livewire laravel
Livewire has nothing to do with permission, for permission you should use Laravel core (Gates/Policies) and/or external packages like Spatie Permission. Livewire uses the same Laravel Core Auth.
VIDEO REQUEST : can you make a video to upload an image using dropzone catch is form has other fields too.
I saw your article but I think this can make a good video
Thanks
Yes great idea, but I'm planning a full COURSE on file uploading, it's such a deeeeep topic.
That's the only reason keeps me away from CodeIgniter 4 which it hasn't relation system.
But that considered an advantage for many developers
@@algeriennesaffaires7017 Advantage on what? That is matter.
@@biliyonnet Using regular queries is an advantage itself specially for speed, eloquent is good but not for everyone
@@algeriennesaffaires7017 Eloquent is optional in Laravel. Why not be like that in CodeIgniter 4?
@@biliyonnet Taylor Otwell created Laravel as an attempt to provide a more advanced alternative to the CodeIgniter framework, which did not provide certain features such as built-in support for user authentication and authorization, CodeIgniter team they like to keep it simple for a different case uses
Could I request a video on using the php-league-oauth2.0-client? I want to see how we can connect to multiple oauth2 providers and then save those keys to users
Sorry I haven't worked with that client, so can't make a video on it.
If we use stored procedure what is the advantage and disadvantage
It may be faster in some cases, but then you lose flexibility of Eloquent and your stored procedure code is outside of repository so harder to make changes
Stored Procedures are some how hidden from the rest of the code. You or some other developer might easily forget them.This may cause a hard time understanding what is going on in your program.
@@josephnduati1214 incase of speed?
hi .i am from Iran and i watching your video. thank you for educations. i have a question , whats the mean of (i am shooting that daily subscribe to channel) ?
sorry my english language is not very good
He is uploading video almost every day, that's what he meant (Shooting means video recording in this case)
I have sent you an email asking questions regarding how to generate a custom invoice number that avoids a race condition.
Didn't receive it probably. Try again: povilas@laraveldaily.com
@@PovilasKorop Just resent it please check your spam folder as well. Thanks.
@@tousifali1118 Still haven't got it. Try my personal povilask007@gmail.com
@@PovilasKorop Hi, just send it to your personal email.
আসাধারন।
তবে আমি ইলোকুয়েন্ট ব্যবহার করতে পছন্দ করি
How hard us it to actually learn SQL and use it directly - without all those additional layers?
For more complicated or slow queries, it's good to know what happens under the hood. But those additional layers give you so much control, less code, less bugs, readability, and fun!
@@JohnVT But it is only useful in demos or when working with really small databases. Imagine using this Eloquent on real production database where tables have millions of rows and not few thousands. It loading entire table makes it completely useless. I think using such tools creates bad practices that will haunt developers when they move to working on real-life DBs.
@@marekkedzierski8237 I don't have to imagine that, lots of people (including me) use Eloquent on a daily basis in a professional environment. In frontend scenario's you will always have pagination (and not serve millions of records at once). Even scheduled jobs working with API's will process per batch and not millions at once. So performance is no issue at all, except for some exotic cases like generating reports (like the guy mentions).
Those are for readability and maintainability. It's easier to manipulate clauses i.e. where, orderBy, etc.. using these "additional layers" than doing it manually.
in most use cases eloquent is preferable
DB::table returns an stdclass so you can't use the model methods
Can we better this ?
$model = Log::query();
$model->join("devices as d", "d.device_id", "=", "logs.DeviceID");
$model->join("companies as c", "c.id", "=", "d.company_id");
$model->join("individuals as i", "c.id", "!=", "i.company_id");
return $model->with(['device'])->select("logs.*")->get();
thanks
Thanks