90% of the queries in my app are no more complex than selecting from a table with a simple condition. I definitely find
users = User.where(has_foo: true).limit(10)
to be a lot more readable than
rows = connection.exec_query("SELECT * FROM users WHERE has_foo=true LIMIT 10")
users = rows.map { |row| User.build(row) }
(And that's an example with no user-provided input)
Likewise, any app of sufficient size seems to end up with a handful of queries that really are a pain (or impossible) to cram into an ORM. Trying to do so would result in an unreadable mess, and using raw sql improves the situation immensely.
Here's the thing, anyone who knows SQL will find the second one readable, and only Ruby programmers who have used ActiveRecord will know how the first one does.
I've never written a line of Ruby in my life and I know what that code does, because it's essentially identical to what you'd write in C# LINQ to query with Entity Framework, LINQ2SQL, etc.
Unfortunately that argument works both ways, and we are saddled with a majority of mediocre developers who know an ORM but can barely read trivial SQL and can't really write it at all.
In my experience, programmers are often very fuzzy on all of the types involved in such libraries. I don't even mean "strong typing", but just the operations that they can perform with the various chunks of that expression. This is often complicated by the fact that the library itself often has bizarre limitations that have more to do with its internal implementation and limitations than with SQL. It's rare for me to see anyone use a library like that with fluency. Which seems to me to be a valid way of saying that, yes, in practice, it does seem to be harder to understand the first version in Ruby than you might initially think. Using it isn't particularly harder, but understanding it is.
Also note this is an observation of mine, not a logical argument, so trying to logically argue about why it shouldn't be harder would be arguing a point I'm not making. As much as I don't like people bashing strings together programmatically to generate SQL queries due to the ease of screwing it up, I observe that a lot more programmers are capable of this (even if they screw up the security) than seem to understand how to use things like ActiveRecord equally fluently. YMMV.
Really? connection.exec_, .map, .build – there's a lot more non-SQL going on in the second example than the first. The first may be syntactically far from SQL, but the use of familiar vocabulary makes it pretty understandable from a SQL point-of-view.
Readability by maintenance programmers? How many Rails programmers will not know ActiveRecord? How many are going to be better at maintaining SQL than they are at maintaining ActiveRecord queries?
Readability by business people who don't know any programming languages? The first is a lot clearer than the second IMO.
For me thats the opposite. I prefer "rows = connection.exec_query("SELECT * FROM users WHERE has_foo=true LIMIT 10")", because i can read and understand the query. By reaing it i can already tell if it is a good query etc. while "User.where(has_foo: true).limit(10)" tells me nothing.
Read and understand, yes, the first is slightly simpler. Modify? Now I want to add an 'and' to the where clause. What's easier to figure out how to modify, for a random developer? I'd contend the latter, and that that is true for any modification you have to make.
In this example, it seems like it could be an issue that you take everything and then only use the first 10, instead of only taking the first 10 to begin with. Is there a way to not make it take them all, like putting the limit parameter in where()?
At least it is an obvious fix. :) The AR example/solution would tend to just assume you want all the fields. To those naive of how AR behaves, it isn't obvious.
90% of the queries in my app are no more complex than selecting from a table with a simple condition. I definitely find
to be a lot more readable than (And that's an example with no user-provided input)Likewise, any app of sufficient size seems to end up with a handful of queries that really are a pain (or impossible) to cram into an ORM. Trying to do so would result in an unreadable mess, and using raw sql improves the situation immensely.