>"Give me all the blog posts with 8 replies written by users with a karma more than 42."
SELECT P.*
FROM blog.Posts P
INNER JOIN ( --postid and count of replies
SELECT P.PostId, COUNT(UserReplies.PostId) NumReplies
FROM blog.Posts P
INNER JOIN ( --Replies with users karma more than 42
SELECT R.PostId
FROM blog.Replies R
INNER JOIN blog.Users U ON R.UserId = U.UserId
WHERE U.Karma > 42
) UserReplies ON P.PostId = UserReplies.PostId
GROUP BY P.PostId
) PostReplies ON P.PostId = PostReplies.PostId
WHERE PostReplies.NumReplies > 8
After writing the query (3 min, while eating pizza), there's obviously much room for optimization in table design and query design. I doubt the ORM'ed developer would be able to see the optimizations not understanding how to write the SQL (so they can reason about indices, aggregations, etc...).My take is, most people fall into the trap of programming language X is THE language of this application. If an application has a relational database component to it, it'd be best if there was someone who understood SQL and the connection points to the application.
Programmers want to avoid SQL for the same reason they don't want to write their applications in assembler. Assembler doesn't abstract computational abstractions, it abstracts away the CPU microcode. Since we want to write programs in terms of computations and not CPU micro-operations, assembly is tedious and pointless. SQL is the same way; we don't want to write apps in terms of inner joins. We want to write them in terms of the high-level relationships. ORMs allow this.
(Key/value stores are also the wrong abstraction, they don't abstract much except physically writing the bits to disk. "Yay.")