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.

Amen. There's already a well-proven and quite expressive DSL for accessing data stored in a relational database: SQL.

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...

Thanks for pointing to that interesting article. However, a link to the corresponding HN discussion would have been more useful, at least to me.

Wasn't very successful, 3 points and 0 comments: http://news.ycombinator.com/item?id=3059460

I found it with hnsearch, by the way.

Now it has 4 points. :-)

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.

Install Propel, define the schema and only generate migration scripts with it. Problem solved.

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.

Here's some RedBean code from one of my projects:

http://pastebin.com/hpLB1h37

That looks interesting. I'll give it a look.

It is so simple:

- 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.

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.

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.

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.

I really enjoy Flourish's ActiveRecord model http://flourishlib.com/docs/fActiveRecord

It feels natural, also it let's you do SQL if you really need it.

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.

I'd say that's one of the bad examples according to the article. :)

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."

Maybe it's just me. :)

Not just you. :)

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.”

But It was very informative, as he explains what he is talking about since the definition of an ORM seems to be quite different for different people.

For me it also means anything that tries to generate SQL.

Yeah, that's exactly what I meant by it.

I would just add a fetch_scalar() function. It makes some common operations get very readable:

$total_users = $db->fetch_scalar("SELECT COUNT(*) FROM users");

fetch_column() does essentially that. You can pass in the column number you want fetched, in this case, it would be the 0th column.

I know. I would at least add it as an alias to $fetch_column(0), out of sheer readability preciosism =D

I'm interested in your counter arguments too. I'm sure there is some component or aspect I'm missing.

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.

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.

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.