And's and or's are trivial in any good ORM. There are valid reasons to not want to use an ORM, but they are more around the the object/relational impedance mismatch, coupling table design to the domain model, etc.
But the alternative to an ORM is not opaque blobs if SQL hard coded into the app all over. How do you handle SQL injection attacks for example? What if you add/rename/drop a column? Do you just grep you code and edit every blob of sql in the app?
You still separate your views from your controllers and sql injection is not an issue when you bind params, but even if you are using an ORM you can't change and add columns without some impact on the code (you need to update forms to add the new value to the created objects, show it, and, presumably, do something useful with it).
And that also assumes that you are using databases as an object store. Databases are also useful to answer questions like: show me the number of users who have signed up each day for the last month.
Why would we have table references all over the app? We still use centralized models, just not ORMs.
Have a class representing a table and methods where you hit the database and map the response to an instance of the class.
It’s nice in a typed language when I map what the query will return and the compiler enforces it.
But not all my queries map to a class, but it’s not a big mess since we only use statically typed languages on the server so I still need to map the result to a tuple or dictionary of not a class.
In the end, if you're writing in a non-relational language, using a relational database, and processing/using the DB responses in any way other than just returning them to the user, then you will have an ORM layer/library somewhere. Whether you should be using an off-the-shelf one or not is a different question, but you can't really escape the need to Map between your Object and Relational models (substitute Object for Datatype or Struct if you prefer).
ORMs are just a tool, and they really don't preclude query optimisation.
All the ones I've used or written allow bypassing selects, joins or an entire query and manually translating results, so they don't have to get in the way when optimisation really matters, but the vast majority of the time IME in most apps that just isn't necessary so I'll take the reduced friction of a query builder that automates the basics as long as it allows bypassing it when required.
But the alternative to an ORM is not opaque blobs if SQL hard coded into the app all over. How do you handle SQL injection attacks for example? What if you add/rename/drop a column? Do you just grep you code and edit every blob of sql in the app?