You'll get none from me. Funny reading your post, because it perfectly describes my own experience this year: starting to learn another language (ruby), then getting pulled right back into php for a big client project. And I also decided to ditch ORM and stick to PDO, which by the way is one hell of a library.
It's really all about factoring. You'll notice now that you have model logic in your controller. If you want to reuse that elsewhere, you'll first have to extract it to another class. That class is effectively a SQL generation library, except it's not useful in a general sense and it's not as well tested. The more you do this, the more you converge on reinventing a full blown ORM.
I'm not saying there aren't downsides to ORMs, but the counter-arguments to SQL in your controller are pretty obvious.
I disagree. Yes, this should be refactored and placed in a model; but that would be something like Clients::getReadMetrics() that would contain the same SQL. I don't see that heading towards a full ORM or even remotely like SQL generation.
Couple of small things: you shouldn't `return ($var);`, since that returns the result of the statement `($var)`. Use `return $var;` instead, and return them by reference where possible.
I also would have gone with naming consistent with PDO's (i.e. `halfCamelCase` rather than `lower_case`), but that's personal preference. Consistency would be nice though.
Other commenters are complaining about SQL written in the controller. I don't see this as problem in general, depending on project and workflow it can be valid. OOP nit-picks will see a problem there, but OOP users know how to fix it right?
Back on topic:
I'm grieving with ORM for some time now. The main problem is that people doesn't really think about how appropriate an ORM solution over any other solution is. They learn one ORM system and use it everywhere. Worse, they do everything the ORM way, writing abstract ORM queries that should have been straight SQL (for readability). And you know what? Those people (or projects) are fire-and-forget. They work on a project for few month and then they're gone. They don't have experience with projects that run several years and all those problems tied to it.
I think ORM (or full-stack framework) prototyping is much faster than straight SQL - within it's limitations. But for long term projects it's likely toxic.
So in my opinion, ORM is pretty good for prototyping or low maintenance projects, or just prototypes. Considering your past it's learning curve.
I've been using the library for the last few months on a number of projects. It's sort of become my Swiss Army knife for PHP. It's saved me a ton of time.
This sentence took me several reads to parse: "I will refer to any library or framework for interacting with a database besides writing straight SQL as an ORM in this article."
Some rearranging/rewriting would make it easier to parse. E.g.:
“In this article I will use the term ORM to refer to any library or framework for interacting with a database; that is, to refer to anything other than writing straight SQL.”
After about 10-15 years working with many different systems that one way or another store their data in a RDBMS, all I can say is that I agree with what you wrote. ORM-wed code is a nightmare, 3-5 years on. When the ORM trends have shifted to something else, and the original people who implemented it or selected the framework is long gone.
Ill take a messy system with hand coded SQL over that any day, atleast then you have a chance to refactor.
Also, its not that hard to implement a pluggable data access layer (that uses hand written SQL). The most important thing is to keep your RDBMS data model separate from your application model. These two will be intermingled when you are using a ORM, in my experience.
Always good to have something like this. Throw it up on GitHub and give it a descriptive name :)
Lately I've been using RedBean ORM for my projects. It lets you write your SQL (no query builders or anything stupid), but creates tables, columns, and foreign keys on the fly.
The concept of ORM is a subset of the concept of SQL. When you're adding complexity and/or overhead to something to achieve a subset of functionality, you'd better have a damned good reason to do so. I'm still waiting for a reason that doesn't make me roll my eyes...
Generally I agree doing SQL queries on your own. I'm also not really an ORM-Fan because it's really hard to debug and difficult queries are more difficult to construct using an ORM.
But doing your SQL Statements in the Controller is just not the way to go.. What if you need the same query on different places?
What I like about good ORMs (like Doctrine or the ActiveRecord of RoR) is the migration part.
Maybe there should be a tool which only supports you in creating the tables and handling migrations.
- If you do quick ad hoc script/page - use straight sql with mysql_query
- If you extend it - use something like PDO
- If you build real application, you have to use Model layer. And no matter what you prefer at this point - you'd better to put everything behind Model be it PDO-based queries, some dynamic objectish query builders or full-scale ORM like DBIx::Class (sorry, this is for Perl, not very familiar with something like this in PHP. If someone know good analogs in PHP - please post links to these).
Right tool for right task - this is what important to keep in mind.
PS: Sorry, but seeing straight SQL in what looks like an action, is really hurt my eyes. If you opted for MVC framework, you'd probably want to push all SQL into model and leave meaningful calls to it.
I'm a fan of a hybrid approach (a few wrapper functions around PDO itself that just abstract the repetitive stuff in PDO but not the SQL, very similar to the post), and very thin ORM too.
I like a basic Model class with a few helpers that I can extend to keep my logic separated from my controllers, but that's about it. Inside any given Model method, SQL is fair game.
There are no comment on the blog, so I'm just going to put this here and hope the author sees it (as they appear to have submitted the post):
You don't want to write SQL in your controllers. It's a recipe for future pain and suffering.
- If a second action needs the same data, it's likely that you'll end up duplicating your query. Especially if you work with other people, and they don't know about the queries that exist in all of the different actions.
- If you change the structure of the data in the DB, you need to find every affected query, in every action. It's extremely fragile, and prone to breaking.
If you have a model, with a nice public API, that interacts with the DB, you know that you've isolated the change to just that one place. All of the controllers that call $model->some_data(); will continue to work no matter how your change your data source so long as you obey the API.
There are million different ways you can approach that, but I strongly recommend that you find one that works for you, and stick with it.
Yes , totally agree with this but I still often see people put SQL (or HSQL or whatever) into controller/view level code.
I think part of the reason for this is it appears in allot of tutorials for different web frameworks, which I think they do because it makes the code smaller and therefor their framework look simpler.
I'm a fan of the recent move towards Micro-ORM's. As others have said, SQL is quite expressive and pretty easy to learn (for most things). I suspect that the reason why many app developers shy away from it is because it is set based rather than object oriented.
32 comments
[ 0.31 ms ] story [ 80.8 ms ] threadI'm not saying there aren't downsides to ORMs, but the counter-arguments to SQL in your controller are pretty obvious.
I also would have gone with naming consistent with PDO's (i.e. `halfCamelCase` rather than `lower_case`), but that's personal preference. Consistency would be nice though.
Back on topic: I'm grieving with ORM for some time now. The main problem is that people doesn't really think about how appropriate an ORM solution over any other solution is. They learn one ORM system and use it everywhere. Worse, they do everything the ORM way, writing abstract ORM queries that should have been straight SQL (for readability). And you know what? Those people (or projects) are fire-and-forget. They work on a project for few month and then they're gone. They don't have experience with projects that run several years and all those problems tied to it.
I think ORM (or full-stack framework) prototyping is much faster than straight SQL - within it's limitations. But for long term projects it's likely toxic.
So in my opinion, ORM is pretty good for prototyping or low maintenance projects, or just prototypes. Considering your past it's learning curve.
It feels natural, also it let's you do SQL if you really need it.
Maybe it's just me. :)
For me it also means anything that tries to generate SQL.
Some rearranging/rewriting would make it easier to parse. E.g.:
“In this article I will use the term ORM to refer to any library or framework for interacting with a database; that is, to refer to anything other than writing straight SQL.”
Ill take a messy system with hand coded SQL over that any day, atleast then you have a chance to refactor.
Also, its not that hard to implement a pluggable data access layer (that uses hand written SQL). The most important thing is to keep your RDBMS data model separate from your application model. These two will be intermingled when you are using a ORM, in my experience.
Lately I've been using RedBean ORM for my projects. It lets you write your SQL (no query builders or anything stupid), but creates tables, columns, and foreign keys on the fly.
Here's some RedBean code from one of my projects:
http://pastebin.com/hpLB1h37
I see that this has been submitted before, so I'll just mention it here: http://thoughts.j-davis.com/2011/09/25/sql-the-successful-co...
I found it with hnsearch, by the way.
But doing your SQL Statements in the Controller is just not the way to go.. What if you need the same query on different places?
What I like about good ORMs (like Doctrine or the ActiveRecord of RoR) is the migration part. Maybe there should be a tool which only supports you in creating the tables and handling migrations.
$total_users = $db->fetch_scalar("SELECT COUNT(*) FROM users");
- If you do quick ad hoc script/page - use straight sql with mysql_query
- If you extend it - use something like PDO
- If you build real application, you have to use Model layer. And no matter what you prefer at this point - you'd better to put everything behind Model be it PDO-based queries, some dynamic objectish query builders or full-scale ORM like DBIx::Class (sorry, this is for Perl, not very familiar with something like this in PHP. If someone know good analogs in PHP - please post links to these).
Right tool for right task - this is what important to keep in mind.
PS: Sorry, but seeing straight SQL in what looks like an action, is really hurt my eyes. If you opted for MVC framework, you'd probably want to push all SQL into model and leave meaningful calls to it.
I like a basic Model class with a few helpers that I can extend to keep my logic separated from my controllers, but that's about it. Inside any given Model method, SQL is fair game.
You don't want to write SQL in your controllers. It's a recipe for future pain and suffering.
- If a second action needs the same data, it's likely that you'll end up duplicating your query. Especially if you work with other people, and they don't know about the queries that exist in all of the different actions.
- If you change the structure of the data in the DB, you need to find every affected query, in every action. It's extremely fragile, and prone to breaking.
If you have a model, with a nice public API, that interacts with the DB, you know that you've isolated the change to just that one place. All of the controllers that call $model->some_data(); will continue to work no matter how your change your data source so long as you obey the API.
There are million different ways you can approach that, but I strongly recommend that you find one that works for you, and stick with it.
I think part of the reason for this is it appears in allot of tutorials for different web frameworks, which I think they do because it makes the code smaller and therefor their framework look simpler.