[luckylion]

But there's also value gained in it, isn't there? I very much like doctrine's query builders and being able to analyze and manipulate queries programmatically, e.g. dynamically add a filter to a query and a join if needed. That's pretty simple with a query builder once you've gotten comfortable with the concept and the ORM itself, but it's pretty hard to do with plain sql unless you write plenty of specific code to handle all the known things you might care about.

[manuelabeledo]

It sounds to me that you saying that SQL is hard because you’d rather learn the intricacies of an ORM.

Also, this whole point predicates upon the assumption that ORMs are infallible when translating queries into SQL, which most definitely are not.

[luckylion]

No, I'm saying if you want to alter SQL queries programmatically, you'll either do some quick hacks with regexps that you'll regret, or you need to build something to do that, and that will look suspiciously like a query builder.

[manuelabeledo]

I’m having a hard time trying to understand exactly what you want to do, that cannot be done with SQL. Any concrete examples?

I personally rely on views to reuse base queries and then add filters on top of them.

[luckylion]

Sorry, got distracted.

Anything you can do with query builders, you can do with SQL of course, it's just more difficult / verbose (to me).

Let's say I have a lot of entities that are tied to domains, and I have users that have roles on those domains. I do a lot of reports, and for the vast majority, I don't have to worry about whether a user has the necessary access on the domain, I just call a central service, pass my query builder, the user, and the required access level, and it figures out whether the query builder already contains the necessary parts or not, which joins are required (e.g. if I'm selecting from subdomain, I need to join with domain and then join domain to user_domain) and add all of that transparently. I can treat service accounts differently, and I can just ignore all of that noise if a super admin is running the report.

I could absolutely do all of that in SQL, the SQL doesn't really change (if anything, query builders tend to produce worse SQL in my experience since you're not writing DB-specific code), just how I interact with it. The abstraction allows me to compose the query instead of writing it, giving me more flexibility.