Mvc – Strategies for avoiding SQL in your Controllers… or how many methods should I have in the Models

design-patternsmvc

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?

Best Answer

Martin Fowler's Patterns of Enterprise Application Architecture describes a number of ORM related paterns, including use of the Query Object, which is what I'd suggest.

Query objects let you follow the Single Responsibility principle, by separating the logic for each query into individually managed and maintained strategy objects. Either your controller can manage their use directly, or delegate that to a secondary controller or helper object.

Will you have a lot of them? Certainly. Can some be grouped into generic queries? Yes again.

Can you use dependency injection to create the objects from metadata? That's what most ORM tools do.

Related Topic