I am putting together an web app with relatively complex but standard database relationships. I notice that anytime I use an ORM, in python/ruby/php etc, in general, a lot of queries are generated. This is stating the obvious. ORM's typically create more queries than necessary to get the job done. They aren't perfect.
I could create these queries myself. This seems old school but, the app will most likely run faster and I won't have to fool around with ORM's. Instead I will be fooling around with SQL. I would rather fool around with SQL, than a language specific/ version specific library.
I am not afraid of ORM's, I've used them with great success in the past.
I am wondering how people generally (on a macro level) organize their code to support raw SQL queries.
For example, let's say I use an MVC like architecture in my application.
- My views are in HTML with some tempting library.
- My controllers get data from the models and send it to the views.
- My models map individual/multiple db tables to properties and methods in a class like structure for easy access.
Where in there should the sql commands be put? I can imagine it could be a recipe for sloppy code or models with miles of sql queries. Also it would make it impossible to add something like a default sort. And we're back to ORM's again.
If I were to think about this, I might create a sql helper for each model, which contains the query. Wondering if there are other ways