Strategies for avoiding SQL in your Controllers... or how many methods should I have in my Models?
- by Keith Palmer
So a situation I run into reasonably often is one where my models start to either:
Grow into monsters with tons and tons of methods
OR
Allow you to pass pieces of SQL to them, so that they are flexible enough to not require a million different methods
For example, say we have a "widget" model. We start with some basic methods:
get($id)
insert($record)
update($id, $record)
delete($id)
getList() // get a list of Widgets
That's all fine and dandy, but then we need some reporting:
listCreatedBetween($start_date, $end_date)
listPurchasedBetween($start_date, $end_date)
listOfPending()
And then the reporting starts to get complex:
listPendingCreatedBetween($start_date, $end_date)
listForCustomer($customer_id)
listPendingCreatedBetweenForCustomer($customer_id, $start_date, $end_date)
You can see where this is growing... eventually we have so many specific query requirements that I either need to implement tons and tons of methods, or some sort of "query" object that I can pass to a single -query(query $query) method...
... or just bite the bullet, and start doing something like this:
list = MyModel-query(" start_date X AND end_date < Y AND pending = 1 AND customer_id = Z ")
There's a certain appeal to just having one method like that instead of 50 million other more specific methods... but it feels "wrong" sometimes to stuff a pile of what's basically SQL into the controller.
Is there a "right" way to handle situations like this? Does it seem acceptable to be stuffing queries like that into a generic -query() method?
Are there better strategies?
