1

I'm using strategy pattern for a PHP project which roughly looks like this. I've been reading this book as reference for making the design pattern.

strategy pattern sample

I'm able to do simple inserts, updates or fetches from the database using the "Insert", "Search", "Update" concrete strategy classes on single tables.

However when it comes to a complex query like JOINs or VIEWs with multiple tables, these classes aren't sufficient.

How do I make a strategy class which allows me to retrieve custom no. of columns from two or more tables using JOINs or VIEWs? OR alternatively should I use stored procedures instead?

Currently, each concrete strategy class looks like this:

interface IStrategy {

   public function algorithm(AbstractModel $modelObj, $tablename);

}

class InsertStrategy implements IStrategy{
   public function algorithm(AbstractModel $modelObj, $tablename){
      // construct SQL statement
      // Initialize the Database Object
      // Insert data using PDO
   }
}
maxxon15
  • 1,559
  • 4
  • 22
  • 35
  • Why do you need a Strategy pattern here at all? What kind of client would need that? Strategy is for exchanging algorithms at runtime. The above are CRUD operations and are likely better contained in their entirety in a TableDataGateway of some sort. – Gordon Jul 29 '13 at 08:58
  • @Gordon Well, I do intend to call the specific strategy i.e, Insert, Update, Delete when I need them from the client - just like the book suggested. I don't understand what you mean by "exchanging algorithms". Can you please elaborate on that with some code? – maxxon15 Jul 29 '13 at 09:46
  • 1
    I explained it in http://stackoverflow.com/questions/17721623/advantages-of-using-strategy-pattern-in-php/17754532#17754532. Strategy doesn't seem to be a viable strategy (pun intended) here. Rather, consider http://www.martinfowler.com/eaaCatalog/tableDataGateway.html – Gordon Jul 29 '13 at 09:53
  • @Gordon That example makes sense and seems much simpler. Person interacts with PersonGateway. So, say if I have another class named User; then the corresponding gateway would be UserGateway. Right? – maxxon15 Jul 29 '13 at 10:05
  • Yes. And to anticipate your question about JOINs, the POEAA book says while the queries should be per table in general, it's okay to have JOINs in there when that's needed. – Gordon Jul 29 '13 at 10:06
  • @Gordon Ok. Now that brings me to another question. When the no. of classes grow bigger (say 20 for eg.), then will it be okay to have a gateway for each of those classes? OR should I find a way to put the common functions of the gateways (for eg. like find(id) ) into a separate parent gateway class? – maxxon15 Jul 29 '13 at 10:17
  • 1
    Both. It's okay to have 20 Gateways if you have to access 20 tables. And it's also okay to abstract the logic that applies to all of the subtypes into a generic or abstract TableDateGateway. In fact, that's what you want to do to prevent code duplication anyway. Have a look at http://framework.zend.com/manual/2.2/en/modules/zend.db.table-gateway.html for some ideas. – Gordon Jul 29 '13 at 10:31

1 Answers1

0

My approach for your usual run of the mill business apps is to avoid the "table API" approach and create a "data API" using stored procedures. You can use views to support your SPs too.

So rather than CRUD, write SPs that provide useful chunks of data to your business layer. Do all the filters, groups, sorts etc. that you can in your SP and get the PHP business layer to do the businessy stuff. You can usually access all your SPs thorough your usual data access layer (or ORM at a bit of a stretch of the imagination).

Separating your data preparation behind SPs is analogous to hiding your business layer behind a service. The reasons and benefits all stack up just as well.

LoztInSpace
  • 5,584
  • 1
  • 15
  • 27
  • Using SP moves business logic into the database system, which means you have to reproduce that in case you switch that system. Keeping the logic in the application makes it more portable, especially when you use a DBAL. – Gordon Jul 29 '13 at 09:51
  • No it doesn't. It moves the heavy data processing (sort, group, aggregate etc) into the DB where it belongs. You still need a business layer. Uniqueness, nullable, relationships are all data rules that trancend any particular application. Switching systems is a rare event unless you're specifically targetting multiple platforms up front. In any case, SPs are a capability provided by all DBs. – LoztInSpace Jul 30 '13 at 05:13
  • You can easily sort, group and aggregate by sending the appropriate query from the application instead of putting it into a SP. And uniqueness, nullable and relationships should not leak into your objects. They are features of DB consistency, not for OOD. – Gordon Jul 30 '13 at 05:56