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;
post_id is indexed, it's not really any different to:
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
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.
I might use
- 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
Instead they did the equivalent of this (buffering 50 inserts at a time and doing 1
INSERT of all 50):
insert into users (id, name) values (1, 'Jeff'), (2, 'Taylor'), (3, 'Adam');
The latency charts in the article speak for themselves!
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.