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!

About these ads


7 Responses to “Zend_Db_Table with Normalized Tables”

  1. Hi there

    I wrote a post a couple of days ago about mimicking Mysql inheritance in Zend_Db. Although the post itself is not directly related to the techniques you’re discussing, you might find some inspiration in my Zend_Db subclassing to build on your ideas here — which are great!

    (hope I can use an anchor tag here)

    http://codecaine.co.za/blog/posts/mysql-pseudo-inheritance-with-zend-db/

  2. 2 alex

    Hi,
    nice idea, but what about n:m relations?
    Greets
    Alex

  3. 3 Justi

    When I’m forced to code in the primitive pile of shit that is PHP, I use ActiveRecord.php – check it out, it’s fantastic and works great with Zend.

  4. I see you are an experienced PHP developer. I am an independent small business owner that is trying to deploy a php driven website with large data base parts files. Where do I start, what php program, and, or get help?

  5. Also, Did you start with .net too? I started my web with .net to find the server I am using if Unix based and that php data admin was the best way to load and easiy update the data base info.


  1. 1 Zend and ORM - Zend Framework Forum
  2. 2 Any1 got a quick and dirty ORM for 1.5? - Zend Framework Forum

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: