A simple Data Model Object

The operating paradigm

Rule 1  Primary tables have a unique integer primary key

In our simple world view, all primary data tables have a unique integer primary id. DB theorists can stay away at this point! We use an integer because indexing it is fast and in the vast majority of DBMS' it doesn't take a lot of space. Our target DBMS is MySql. Any straight M2M join tables don't require a redundant key so we don't use them. For the vast majority of cases, the primary key will also be auto-incrementing.

Rule 2  Primary tables may have an additional unique string key

It is often the case that we need to refer to an entity by its name rather than some obscure internal id. This is OK, but the rule is that our models will only support one such unique key. (You can extend the model if you need to.)

Rule 3  We do not model M2M join tables.

Our primary models must take care of any M2M tables to which they have a relationship. This greatly simplifies our operating paradigm and in most cases is easily achieved.

Given the above, we can therefore start with our base class that all our models will be derived from.

The base model

/** 
 * Adds table data as public parameters to the table object 
 * 
 * Every underlying table to a model must have a primary int key = 'id'. 
 * Additionally it can have a a UNIQUE INDEX string field whic identifies the record 
 * @author Ashley Kitson 
 * @copyright ZF 4 Business Limited 
 * @license GPL V3 
 */ 
 
class APP_Db_Table_Model extends Zend_Db_Table_Abstract { 
 
    /** 
     * Unique column name on the table 
     * 
     * @var string 
     */ 
    protected $_uniqueCol = null; 
 
    /** 
     * primary id col 
     * 
     * @var string 
     */ 
    protected $_primary = 'id'; 
  
    /** 
     * Constructor 
     * 
     * If $id is null an empty table object will be returned 
     * If $id is given and results in a valid record being read, the record values are added 
     * as public parameters to the object and can be referenced as $object->parameter (read only) 
     * 
     * @param string $table the name of a table being used by the model 
     * @param string $uniqueCol the name of the unique column in the table 
     * @param int|string $id the record id or a unique name 
     * @param string encryption salt 
     * @throws APP_Db_Table_Exception_InvalidId 
     * @throws APP_Db_Table_Exception_NoUniqueCol 
     */ 
    public function __construct($table, $uniqueCol = null, $id = null) { 
        parent::__construct(array('name'=>$table)); 
        $this->_uniqueCol = $uniqueCol; 
        if (null !== $id) { 
		if (is_int($id)) { 
        		$this->fetchRecordById($id); 
        	} elseif (is_string($id)) { 
        		$this->fetchRecordByName($id); 
        	} else { 
        		throw new APP_Db_Table_Exception_InvalidId(); 
        	} 
        } 
    } 

    /** 
     * Set the table name 
     * 
     * @param string $tablename 
     * @return APP_Db_Table_Model Fluent Interface 
     */ 
    public function setTablename($tablename) { 
 	$this->_name = (string) $tablename; 
 	return $this; 
    } 
 
	/** 
 	 * Return the model's table name 
 	 * 
 	 * @return string 
 	 */ 
 	public function getTableName() { 
 		return $this->_name; 
 	} 
 	 
 	/** 
 	 * Fetch a record from the database by its primary id 
 	 * and set the objects public parameters to its values 
 	 * 
 	 * @param int $id 
 	 * @return APP_Db_Table_Model Fluent Interface 
 	 * @throws APP_Db_Table_Exception_InvalidId 
 	 */ 
 	public function fetchRecordById($id) { 
 		$row = $this->find($id)->toArray(); 
 		if (!isset($row['id'])) { 
 			$row = $row[0]; 
 		} 
 		if (null !== $row) { 
 			$this->_setRecordData($row); 
 		} else { 
 			throw new APP_Db_Table_Exception_InvalidId(); 
 		} 
 		return $this; 
 	} 
 
 	/** 
 	 * Fetch a record from the database by its unique name field 
 	 * and set the objects public parameters to its values 
 	 * 
 	 * @param string $name 
 	 * @return APP_Db_Table_Model Fluent Interface 
 	 * @throws APP_Db_Table_Exception_InvalidId 
 	 * @throws APP_Db_Table_Exception_NoUniqueCol 
 	 */ 
 	public function fetchRecordByName($name) { 
 		$row = $this->fetchRow($this->_getUniqueCol() . "='" . $name . "'"); 
		if (null !== $row) { 
			$this->_setRecordData($row->toArray()); 
		} else { 
			throw new APP_Db_Table_Exception_InvalidId(); 
		} 
		return $this; 
	} 
 
	/** 
	 * Return the unique index column name on this table 
	 * 
	 * @return string 
	 * @throws APP_Db_Table_Exception_NoUniqueCol 
	 */ 
	private function _getUniqueCol() { 
		if ($this->_uniqueCol == null) { 
			throw new APP_Db_Table_Exception_NoUniqueCol(); 
		} 
		return $this->_uniqueCol; 
	} 

	/** 
	 * Return the record parameters 
	 * 
	 * Will strip out any parameters beginning with _ 
	 * 
	 * @return array 
	 */ 
	public function getRecordData() { 
		$vars =  get_object_vars($this); 
		foreach ($vars as $key=>$value) { 
			if (strpos($key,'_') === 0) { 
				unset($vars[$key]); 
			} 
		} 
		return $vars; 
	} 
 
	/** 
	 * Set public parameters on this object 
	 * 
	 * @param array $data 
	 * @return APP_Db_Table_Model Fluent Interface 
	 */ 
	protected function _setRecordData(array $data) { 
		foreach ($data as $key=>$value) { 
			$this->$key = $value; 
		} 
		return $this; 
	} 

	/** 
	 * Do we have a valid record 
	 * 
	 * @return boolean 
	 */ 
	public function isValid() { 
		return (!empty($this->id)); 
	} 
} 

Putting it to use

I'll leave it to you to define the various Exception classes. For now lets put the model class to work with a straightforward example. We'll use the following data model;

The sql to create these tables is;

-- -----------------------------------------------------
-- Table `Organisation` 
-- ----------------------------------------------------- 
CREATE  TABLE IF NOT EXISTS `Organisation` ( 
  `id` INT NOT NULL AUTO_INCREMENT , 
  `orgName` VARCHAR(45) NOT NULL , 
  `coRegNum` VARCHAR(6) NOT NULL , 
  PRIMARY KEY (`id`) ) 
ENGINE = InnoDB; 
CREATE UNIQUE INDEX `orgName_UNIQUE` ON `Organisation` (`orgName`
ASC) ; 
-- ----------------------------------------------------- 
-- Table `System` 
-- ----------------------------------------------------- 
CREATE  TABLE IF NOT EXISTS `System` ( 
  `id` INT NOT NULL AUTO_INCREMENT , 
  `sysName` VARCHAR(45) NOT NULL , 
  `sysOS` ENUM('linux','windoze') NOT NULL , 
  PRIMARY KEY (`id`) ) 
ENGINE = InnoDB; 
CREATE UNIQUE INDEX `sysName_UNIQUE` ON `System` (`sysName` ASC) ;
-- ----------------------------------------------------- 
-- Table `m2mOrgSystem` 
-- ----------------------------------------------------- 
CREATE  TABLE IF NOT EXISTS `m2mOrgSystem` ( 
  `idOrg` INT NOT NULL , 
  `idSys` INT NOT NULL , 
  PRIMARY KEY (`idOrg`, `idSys`) , 
  CONSTRAINT `fk_Organisation_has_System_Organisation` 
    FOREIGN KEY (`idOrg` ) 
    REFERENCES `Organisation` (`id` ) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE , 
  CONSTRAINT `fk_Organisation_has_System_System1` 
    FOREIGN KEY (`idSys` ) 
    REFERENCES `System` (`id` ) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE ) 
ENGINE = InnoDB;

Now we can create our two primary models;

In Application/Models/Organisation.php;

/** 
 * Organisation model 
 */ 
class Application_Model_Organisation extends APP_Db_Table_Model { 
	/** 
	 * Constructor 
	 * 
	 * @param int|string $id Organisation internal id or name 
	 */ 
	public function __construct($id = null) { 
		parent::_construct('Organisation','orgName',$id); 
	} 
 
	/** 
	 * Get the systems for this organisation 
	 * 
	 * @param boolean $asObject Return results as array of objects 
	 * @return array 
	 */ 
	public function getSystems($asObject = false) { 
		$select = $this->getAdapter() 
				->select() 
				->from(array('o'=>$this->_name,array()) 
				->join(array('s'=>'system', 
						 'o.id=s.orgId', 
						  array('id'=>'s.id','name'=>'s.name')); 
		$rows = $this->getAdapter()->fetchAll($select); 
		$retArr = array(); 
		foreach($rows as $row) { 
			$retArr[$row->id] 
 
				= ($asObject 
 
					? new Application_Model_System(intval($row->id)) 
					: $row->name 
				  ) 
		} 
		return $retArr; 
	} 
}

In Application/Models/System.php;

/** 
 * System model 
 */ 
class Application_Model_System extends APP_Db_Table_Model { 
	/** 
	 * Constructor 
	 * 
	 * @param int|string $id System internal id or name 
	 */ 
	public function __construct($id = null) { 
		parent::_construct('System','sysName',$id); 
	} 

 
	/** 
	 * Add organisations for this system 
	 * 
	 * @param array $orgs array of org ids 
	 * @return Application_Model_System Fluent Interface 
	 */ 
	public function addOrgs(array $orgs) { 
		$model = new Zend_Db_Table(array('name'=>'m2mOrgSystem')); 
		foreach($orgs as $org) { 
			$model->insert(array('orgId'=>$org,'sysId'=>$this->id)); 
		} 
		return $this; 
	} 
} 

Using these models is simplicity itself.

<?php 
$orgId = 1; 
$orgName = 'ZF 4 Business'; 
$orgBad = true; 
$org1 = new Application_Model_Organisation($orgId); 
$org2 = new Application_Model_Organisation($orgName); 
try { 
	$org3 = new Application_Model_Organisation($orgBad); 
} catch (Exception $e) { 
	//do something 
} 
$org4 =  = new Application_Model_Organisation(); 
if ($org1->isValid()) { 
	echo $org1->orgName; 
} 
if ($org2->isValid()) { 
	echo $org2->id; 
} 
if ($org4->isValid()) { 
	echo $org2->id; 
} else { 
	echo 'Org not valid'; 
} 
Zend_Debug::Dump($org1,'Org1'); 
Zend_Debug::Dump($org2->getRecordData(),'$org2->getRecordData()'); 

Note that there is nothing wrong with creating an invalid model as above for $org4. Just like any other models that you might create using Zend_Db_Table_Abstract, there are many operations that you can perform using the empty model.

<?php
$sysModel = new Application_Model_System(); 
$select = $sysModel->select() 
			  ->from($sysModel,array('id','sysName'); 
$rows = $sysModel->fetchall($select); 
$sysModel->insert($someData); 

Extending the class

The following example fulfils the common requirement to create a select box from a table's contents;	
   /** 
 	 * Get id->someNameColumn for use in form selectors 
 	 * 
 	 * @param string $nameCol name column to use - default is the unique 
         *				   column for the table 
	 * @param array $where additional where clauses to add to selection 
 	 * @return array 
 	 */ 
	public function getForSelect($nameCol = null, array $where = array()) { 
 		if ($nameCol == null) { 
 			$nameCol = $this->_getUniqueCol(); 
 		} 
 		$select = $this->select() 
 				   ->from($this,array('id', $nameCol)); 
 		if (count($where) > 0) { 
 			foreach ($where as $w) { 
 				$select->where($w); 
 			} 
 		} 
 		$rows = $this->getAdapter()->fetchAll($select); 
 		$retArr = array(); 
 		if (count($rows)>0) { 
 			foreach ($rows as $row) { 
 				$retArr[intval($row['id'])] = $row[$nameCol]; 
 			} 
 		} 
 		return $retArr; 
 	} 
Descendent classes can easily extend this to add preset 'where' clauses etc.The final example demonstrates how you can get the comments from the database schema.  For many applications, storing the UI names of
table fields as comments can be a great benefit.
    /** 
     * meta info column comment field 
     * 
     */ 
    const COMMENT = 'COMMENT'; 
 
    /** 
     * Zend doesn't return the column comments, so use this to 
     * get the table column metadata including comments 
     * 
     * NB This is only tested for MySQL 
     * 
     * If you don't need comments use ->info(Zend_Db_Table_Abstract::METADATA) 
     * instead as this is a bit slow 
     * 
     */ 
     public function getColInfo() { 
    	$meta = $this->info(Zend_Db_Table_Abstract::METADATA); 
     	$sql = 'SHOW FULL COLUMNS FROM ' . $this->getTableName(); 
     	$stmt = $this->getAdapter()->query($sql); 
     	$result = $stmt->fetchAll(Zend_Db::FETCH_NUM); 
     	foreach ($result as $col) { 
     		$meta[$col[0]][self::COMMENT] = $col[8]; 
     	} 
     	return $meta; 
     }
Of course you could employ caching to speed things up if
necessary.
The next article in this series will describe how we can extend
this basic object with some additional and powerful functionality.
 
Pragmatic Programming 1 - A simple Data Model Object
© Copyright October 2010, Ashley Kitson & ZF 4 Business
Limited, UK
 


 
 
About the author:  Ashley is a long time programmer and
has used a variety of languages over the past 25 years or so.  He has
been using Zend Framework since about version 0.9 and fully embraces
the OO principles that it is built on.  He makes a living as a
freelance contractor, designing and building business systems using
PHP, Zend Framework and jQuery as a platform.  You can reach him via
his blog pages at http://blog.zf4.biz
or via email at ashley@zf4.biz. 
You can find out about his recent projects at http://zf4.biz