Why do people use DB instead of Model?

One of the important usecases, is when you need to do bulk insertions. E.g. insert records retrieved from a csv file or an api request in large chunks. That saves a lot of time.

DB::table('products')->insert($products);

@denismitr

Products::insert($products);
    

So why not use this? Then if I remember right any observers that exist will also fire... but they will not on DB.

So I do not see how your case is better than my case... Mine will also use any and all mutators as defined on the model as well...

Still do not see the benefit.

Is your request "less" server intensive?

I guess it also a habit. If you are used to write raw sql most of your life it is just quicker to write the raw SQL. For example if I have to write a more complex query I 'think' in raw SQL and than need to figure out on how to write the same query with eloquent with eloquent which takes me more time than I want.

"many roads lead to Rome"

What I noticed in this discuss channel, that most mix db, raw and eloquent. It looks terrible and complicated. In such case I would only use raw.

But why? What is the reasoning? It is harder read, from what I can tell query wise far less efficient. And is breaks out of the main focus of laravel being an MVC.

I mean MVC, Model, view, controller.

What everybody is basically doing with queries is basically the same as deciding not to use blades.

It has no logical backing, and nobody has explained a reason yet. Because i am more used too writing sql... well I am more used to writing a plain html page.

People are not leveraging the framework properly, and going out and showing those learning to use DB and raw queries instead of using the framework and its tools.

There is no answer for your question. This is just how it works and there is also not a good and wrong way. Everybody is free to do whatever he wants. Maybe some solutions are better in your eyes or the eyes of the Laravel community.

Raw queries are not less efficient than using ORM (if we talk about time). It depends on what you do but in all occasions a raw query is faster than an ORM because an ORM needs to parse all of your logic into a raw query. This speed difference is small... but there is a difference. An ORM like eloquent can help you easily to cache models and use eager loading and stuff.

Why not eloquent and model also use db. Or in other words a basic PDO instance. Active record is just a shortcut language which in many cases winds up not being so short cut. For a more complex query I prefer just regular SQL with getPdo ().

You guys are still not accounting for the fact that these things are built in. You could recode the entire framework yourself, eloquent is a core building block, and you are all just skating around the fact that eloquent is how LARAVEL does its databasing, yes there are times to use DB::raw or DB in general but the usefulness of eloquent and it being a core building block of laravel... i feel everybody that has posted just does not feel like flowing the framework and learning how ti use it properly.

Because everyone likes spaghetti :)

Let me be clear that I also suggest using eloquent as much as possible because it makes life easier in the end.

But... @cmdobueno what is your goal with this topic? You ask a question why people are using raw queries in Laravel and we give some answers. And you keep saying: 'But this is how Laravel works!' It feels like a religious discussion: "there is only one true way of believing X and that is this because the books says so".....

Ok cool, but what do you want to achieve with this statement? Not everybody is doing everything exactly by the books. Don't judge them. If you want to preach the word of the true Laravel you should leave a comment on the ticket and suggest that there is another way than using raw queries or whatever solution. That way you can help them out and spread the word.

Eloquent is a part of Laravel and I personally recommend to use it because in the end it is easier but it is not required.

ORM abstraction adds a layer to your application. Inserting records natively as described by @denismitr is a lot quicker than your proposed solution.

But I tend to agree; When using an opinionated framework like Laravel it makes little sense. I would simply use a different framework.

@cmdobueno It depends where you’re looking. Most posts on Laracasts I see, people use models in their code examples. You’re only going to get an answer if you actually ask the people who use DB rather than publishing an open-to-all forum post.

As an aside, Eloquent uses the DB query builder under the hood, so anything you can do with DB you can do with an Eloquent model.

Sometimes you simply do not want/need a full Eloquent model - this can especially become apparent when dealing with lots of records. You may not need to incur the cost associated with instantiating a Model instance for every record if you are not using any of the stuff that Eloquent gives you like relations, and accessors, and so on.

Because they like it, or it is the best tool for the job.

Product::insert($products) is going to fail iirc.

@Stratos

Good response, glad to know it will fail... though please satisfy my curiosity... why?

@cmdobueno Sorry my bad, it will actually work, but it won't fire any events iirc. Also it won't add any timestamps like create.

What will fail is Model::create($items);

There is a thread about it https://github.com/laravel/framework/issues/1295

Anyway Models extend DB and sometimes you may use DB instead for better performance

Going back to @cmdobueno's original question... (emphasis mine)

I see DB being used to insert, update, and query, then tons of useless joins... why would one not just use a model and relations to do things properly? Even further it seems to so many people in their response/suggestions also use DB instead of explaining that instead of all these intensive joins, why not just use the power of releations?

tl;dr Laravel's relations are not equivalent to joins (yet represent the same concept).

This next bit is all from memory, hopefully I have made no errors!

Taking a simple eloquent example, when you do Post::find(123)->comments(); you actually get two queries:

SELECT * FROM posts WHERE id = 123;
SELECT * FROM comments WHERE post_id = 123;

If post_id is indexed, it's not really any different to:

SELECT *
FROM posts
INNER JOIN comments
    ON posts.id = comments.post_id;
WHERE posts.id = 123

When your eloquent queries use increasing numbers of related models and more fields you'll find a lot more SELECT queries. This is fine... until the row counts start to rise or indexes are no longer adequate.

Running separate queries has another weakness - each query takes place individually, at a different time, with different underlying data - if there's a 200ms gap between two SELECT statements the second query might not retrieve data that aligns with the first. (A comment without a post for example.) A single query will represent a consistent (The 'C' of ACID) snapshot of the database's state. If you have foreign key between post and comment then every comment will have a post.

When you submit a query a lot of optimisation goes on under the hood:

• "Plan caches" are opened and resources are allocated to each query
• JOIN AND WHERE statements are assessed - the best candidates to reduce the data to be searched are worked out (e.g. a single value from a unique field like customer_id is a good start, searching where 'order' = 'complete' when 90% of the orders are complete is not good).
• The DBMS is tracking what you search for frequently and will be keeping the best data in RAM. Repeatedly SELECTing records on a table-by-table basis may not help it.

Every time you hit the database with a query there's also a minimum round-trip as well and general housekeeping before and after the query.

90% of the time this isn't a concern: the app either has a low user count, small amount of data or low usage. If your data is sensibly indexed you'll be fine. If you're migrating data - that might be a headache, but if it can be done over a weekend, I'm all for letting something run for a couple of hours if it's easier to understand the code doing the migration.

Manual Querying

I might use DB::select() when:

• I need syntax that Laravel doesn't support like Common Table Expressions https://www.postgresql.org/docs/current/static/queries-with.html - if you need to query data that's recursive (WHERE comment.id = comment.parent_id) in a hierarchical structure you will reduce queries by 90% typically!
• Lots of joins/clauses/sub-query references. Sure, you can do it with the query builder - however if the query builder PHP code is hitting 20+ lines with lots of logic there's a real danger that either A) I will loose track of what I'm doing B) The query builder might not handle my code properly. A SQL statement is just that a statement - a fact that the DBMS will obey.
• I need to leverage an optimisation from the DBMS. There are some weird edge cases with complex queries - some times you'll find a clause like WHERE (x = 1 AND y = 2) will make the DBMS use an special index on x and y, but WHERE ((x = 1) AND (y = 2)) doesn't invoke the index. Your query time might become 1000x slower as a result. (This a rarity, I'm thinking of people at the 100GB+ mark!)

When things get RAW

Sometimes there is a need to hugely optimize. Interesting case from Heap.io (analytics) who deal with millions of INSERTS. At their scale doing this (not in PHP, but same scenario):

DB::insert("insert into users (id, name) values (1, 'Jeff')");
DB::insert("insert into users (id, name) values (2, 'Taylor')");
DB::insert("insert into users (id, name) values (3, 'Adam')");

was killing performance as the DB was doing stuff under the hood on EVERY INSERT :o

Instead they did the equivalent of this (buffering 50 inserts at a time and doing 1 INSERT of 50 individual queries):

DB::exec("
insert into users (id, name) values (1, 'Jeff'), (2, 'Taylor'), (3, 'Adam');
");

The latency charts in the article speak for themselves! https://www.postgresql.org/docs/current/static/queries-with.html

Hopefully that helps explain the pros/cons! I would wager that 90% of the people, 90% of the time will never need to shift from eloquent. As I have indicated, only issues of scale, complexity, brevity and performance might require you to execute your queries in a more manual way.

Eloquent was developed for quick and simple consistent database actions, but in real life simple 9 out of 10 becomes complicated, and then you will use SQL.

I use it both, for simple things I use Eloquent and the complicated stuff in SQL.

Best of both worlds, that's what I try to do.

@gregrobson

Thanks for the write up, so from what I understand this is exactly what I expected.

Under normal case usage, with simple to moderately complex queries eloquent is what should be used. Given even within simple there are reasons to use to raw for performance and such...

I think main purpose my post (to circle back to a few other comments) is me many needing to better define what I am asking and saying.

Why are people using DB for simple queries that are none-complex, when a model is far more viable? And to further this as to why people do not better explain to those new to the framework that yes, maybe you are used to writing simple queries, there are a plethora of benefits to using eloquent models for your more common day to day actions of within a laravel application.

@cmdobueno - I must admit in my early days I was more likely to use DB::insert() than the eloquent equivalent! Eloquent is a large part of Laravel which might put people off.

You can build incrementally though, so it's not like you have to tackle everything it does on day 1.

FWIW: I query the Laravel docs on a daily basis, despite being really familiar with it!