Zend_Db_Table with Normalized Tables

13May08

So in my latest project I’ve been using Zend_Db_Table which is really nice for interfacing with a table, however it lends itself for a simple table structure which maps perfectly with your php/application model. I, however, do not have that luxury as I usually normalize my table structure. So one night, around 2am, I wrote an abstract class to allow me with minimum code do the following:

$table1->joinedTable->joinedTableField

Now, I am sure the code I wrote to do this is coupled, inefficient, etc. So as always, feel free to openly torment it 🙂

First Things First
Let me lay out my file structure that I use. The file structure is not dependent on the class I wrote, but I use autoload so it will make sense why I name what I name what:

+ app
  + Models
    + DB
– AbstractModel.php (this is my class)

So I basically added another layer to the Model. In /app/Models/DB I put all my simple Zend_Db_Table classes that represent a table in my database. Then I put all my PHP Model Objects in the /app/Models directory. Before I get to far ahead of myself, it’s always best for me to learn by a live working example so without further adue:

The Example

I have about 16 tables so far in this project ( mid build ) but let’s take a table that joins a lot of other tables. That table is called establishments. Here is the DDL for the table establishments:

CREATE TABLE `establishments` (
`id` bigint(20) unsigned zerofill NOT NULL auto_increment,
`name` varchar(255) default NULL,
`categoryID` mediumint(8) unsigned default NULL,
`longitude` double default NULL,
`lattitude` double default NULL,
`address` varchar(255) default NULL,
`cityID` tinyint(3) unsigned default NULL,
`stateID` int(10) unsigned default NULL,
`zip` varchar(6) default NULL,
`website` varchar(255) default NULL,
`mainPicID` int(10) unsigned default NULL,
`description` text,
`dressID` int(11) default NULL,
`vallet` tinyint(1) default NULL,
`isActive` tinyint(1) unsigned NOT NULL,
PRIMARY KEY  (`id`),
UNIQUE KEY `name` USING BTREE (`name`,`categoryID`),
UNIQUE KEY `address` (`longitude`,`lattitude`)
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8

The columns to note are:

• categoryID: categories Table ( id, name, isActive )
• cityID: cities Table ( id, name, lattitude, longitude, isActive )
• stateID: states Table ( id, name, longitude, lattitude, isActive )
• mainPicID: photos Table ( id, categoryID, entityID, userID, location, description, timestamp, isActive )
• dressID: dress Table ( id, name, description, isActive

The Objective
The goal is to be able to do the following very easily:

$establishment->name //returns establishments.name
$establishment->category->name //returns categories.name mapped to categoryID
$establishment->city->name //returns cities.name mapped to cityID
$establishment->state->name //returns states.name mapped to sateID
$establishment->photo->location // returns photos.location mapped to mainPicID
$establishment->dress->name // returns dress.name mapped to dressID

As you can see this gives us two things, easily mapping our normalized table out & being able to keep naming in our application layer different then the naming in our database (e.g. as a property that represents a table is usually singular tense and the table is plural). Well, let’s as always, work backwards. We need to load $establishments:

//I actually do not do this, I use a factory! But here you go anyways
$establishments = new Models_Establishment();

//Grab the Active Record Where the Name Matches the var $estabNam
$establishment = $establishments->find('name', $estabName);

The Establishments Class
Now we need to actually have our Models_Establishment class. Simply save the following in /app/Models/:

class Models_Establishment extends Models_AbstractModel {
  protected $_map = array(
    'self'=>'Models_DB_Establishment',
    'category'=>array('col'=>'categoryID', 'class'=>'Models_DB_Category'),
    'city'=>array('col'=>'cityID', 'class'=>'Models_DB_City'),
    'state'=>array('col'=>'stateID', 'class'=>'Models_DB_State'),
    'photo'=>array('col'=>'mainPicID', 'class'=>'Models_DB_Photo'),
    'dress'=>array('col'=>'dressID', 'class'=>'Models_DB_Dress')
  );
}

If you notice the first thing this class does is extend the abstract model class which I haven’t gotten into yet. The only other thing in this class is the $_map array which simply maps your object. The ‘self’ index maps the current object to it’s base/core Zend_Db_Table file as you see here ‘Models_DB_Establishment’ which exist in /app/Models/Db/Establishment.php and it looks like:

class Models_DB_Establishment extends Zend_Db_Table_Abstract{
  public $_name = 'spottedhere.establishments';
  protected $_primary = 'id';
}

Nothing fancy there. Then the other indexes are category, city, state, photo, and dress. These all map what the ID is in the current object (categoryID, cityID, etc.) and which DB file to use. All these DB files exist in /app/Models/DB and here is what /app/Models/DB/Category.php looks like:

class Models_DB_Category extends Zend_Db_Table_Abstract{
  public $_name = 'spottedhere.categories';
  protected $_primary = 'id';
}

As you see they are all very similar. So for the most part all you have to do is have a /app/Models/DB/ table for each table you have and each one just specifying the name of the table and the primary key. Now all you need is the abstract class which exist in /app/Modles/AbstractModel.php and it looks like:

abstract class Models_AbstractModel {
  
  protected $id;
  protected $data;
  protected $selfObject;
  protected $_complexFields;
  protected $_map;
  static protected $pk = 'ID';
  
  public function __construct() {
    $this->selfObject= new $this->_map['self']();
  }
  
  public function __get($what) {
    if(is_array($this->_map)) {
      if(array_key_exists($what, $this->_map)) {
        try {
          return $this->data->$what;
        } catch (Exception $e) {
          return false;
        }
      }
    }
    if(is_array($this->_complexFields)) {
      if(array_key_exists($what, $this->_complexFields)) {
        $o;
        foreach($this->_complexFields[$what]['fields'] as $field) {
          $o .= $this->data->self->$field.$this->_complexFields[$what]['seperator'];
        }
        return $o;
      }
    }
    try {
      return $this->data->self->$what;
    } catch (Exception $e) {
      return false;
    }
  }
  
  protected function build() {
    if(is_numeric($this->id)) {
      $this->data->self = $this->selfObject->find($this->id)->current();
      if(is_array($this->_map)) {
        foreach($this->_map as $field=>$properties) {
          if(is_array($properties)) {
            $s = $properties['class'];
            $i = new $s();
            if($properties['col']) {
              //dependent table
              $this->data->$field = $i->find($this->data->self->{$properties['col']})->current();
            } else {
              //not dependent table
              $this->data->$field = Models_Loader::get('UserAccount')->find($properties['fk'], $this->id);
            }
          }
        }
      }
    }
  }
  
  public function find($field, $value) {
    $record = new $this->_map['self']();
    $table = $record->_name;
    $dbObj = Zend_Registry::get('db');
    if(!is_array($value)) {
      $sql = 'SELECT '. self::$pk .' FROM '. $table ." WHERE {$field} = \"{$value}\" LIMIT 1";
      $id = $dbObj->fetchOne($sql);
      if(is_numeric($id)) {
        $this->id = $id;
        $this->build();
        return $this;
      } else {
        return false;
      }
    } else {
      $sql = 'SELECT '. self::$pk .' FROM '. $table ." WHERE {$field} IN (". implode(',',$value) .')';
      return $dbObj->fetchAll($sql);
    }
  }
  
  public function insert($data) {
    return $this->selfObject->insert($data);
  }
  
  public function toArray() {
    $d = array();
    foreach($this->data as $i=>$v) {
      if(get_class($v) == 'Zend_Db_Table_Row') {
        $d[$i] = $v->toArray();
      }
    }
    return $d;
  }
  
}

Anyways, this was my quick solution. As always, looking forward to your feedback!

Filed under: Development, Framework, PHP, Zend Framework   |  7 Comments
Tags: database, databases, Development, Framework, Frameworks, Internet, mysql, normalized, PHP, Programming, tutorial, Web, Zend, Zend Framework, Zend Framework Tutorials, zend_db, zend_db_table, zend_db_table_abstract, Zend_Framework, ZF