PHP MVC: Data Mapper Pattern: Class Design

If I understood your point ...

Having all your specific mappers inheriting SQL from a generic class has a few issues that you missed:

• the parameter names in your domain objects depend on the column names
• mappers have a "fetch method" which doesn't have a matching table
• you still have the configuration (table name) expected by the superclass
• db schema must have id
  
  as name for all columnsPRIMARY KEY
  
  

Now I will try to unpack each one.

Parameter and column names

To create a generic method findById()

, the only pragmatic approach is to create it something like this:

"SELECT * FROM {$this->tableName} WHERE id = :id"

      

        
        
        
      

    

The main problem is actually the wildcard character *

.

There are two main approaches to populating an entity using a renderer: use setters or use reflection. In both cases, the "names" of the parameters / setters are implied by the columns you selected.

In a normal query, you could do something like SELECT name AS fullName FROM ...

that allows the query to be used to re-name the fields. But with a "one-size-fits-all" approach, there are no good options.

Each transformer can retrieve data using id

?

So the point is, if you don't have a mapper-per-table structure (in which case the active entry will start to look like a pragmatic option), you'll end up with a few (very common) edge-case scenarios for your mappers:

• used only for saving data
• deals with collection and non-singular objects.
• aggregates data from multiple tables
• works with a table that has a composite key
• it is actually not a table but a SQL view
• ... or a combination of the above

Your original idea will work great in a small project (with one or two cartographers being the "edge case"). But with a large project, use findById()

will be the exception, not the norm.

Independent parenting?

To get this method findById()

in a superclass, you need a way to tell it the name of the table. This would mean that you have something like this protected $tableName

in your class definition.

You can reduce it by specifying abstract function getTableName()

an abstract mapper in your class, which, when implemented, returns the value of a global constant.

But what happens when your cartographer has to work with multiple tables.

I feel like I have a code smell because the information actually crosses two boundaries (for lack of a better word). When this code breaks, the error will be shown for SQL in a superclass where no error is thrown (especially if you go with constants).

Primary key naming

This is a slightly more controversial opinion :)

As far as I can tell, the practice of calling all primary columns id

comes from different ORMs. The penalty it incurs only applies to readability (and code maintenance). Consider these two questions:

SELECT ar.id, ac.id 
  FROM Articles AS ar LEFT JOIN 
       Accounts AS ac ON ac.id = ar.account_id 
 WHERE ar.status = 'published'

SELECT ar.article_id, ac.account_id 
  FROM Articles AS ar LEFT JOIN 
       Accounts AS ac USING(account_id)
 WHERE ar.status = 'published'

      

        
        
        
      

    

As the database schema grows and the queries become more complex, it becomes harder and harder to keep track of what "id" means in which case.

My recommendation would be to try and use the same name for a column when it is primary, as when it is a foreign key (when possible, because in some cases, such as for "closing tables", this is not viable) ... Basically, all columns that store identifiers of the same type must have the same name.

As a minor bonus, you get syntactic sugar USING()

.

TL; DR

Bad idea. You are basically breaking LSP .