Mysql pseudo-inheritance with Zend_Db

May 15, 2008 7:23:57 PM

Tags: PHP Mysql Zend Framework Zend_Db

I've spent the last couple of days musing to myself about Zend Framework's extensibility. One particular feature that's always intrigued and impressed me is the way that Zend_Db handles rowsets and rows fetched by the Zend_Db_Table class: using inheritance it's possible define custom behaviour on both types of objects. As a demonstration of this, I thought I'd explore the concept of database object inheritance.

Inheritance is a mechanism very familiar to anyone who's worked with OO code, but it does not extend (excuse the pun) very comfortably to relational databases. As an example, consider the following three tables:

CREATE TABLE cars (
	`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`brand` VARCHAR(255) NOT NULL
);

CREATE TABLE trucks (
	`parent_id` INT(11) NOT NULL,
	`number_of_wheels` TINYINT NOT NULL
);

CREATE TABLE sports_cars (
	`parent_id` INT(11) NOT NULL,
	`top_speed` TINYINT NOT NULL
);

In a sense, it could be said that `trucks` inherits from `cars`, although the table `trucks` does not have the attributes, or columns, that it should be inheriting from its supertable. The inheritance, therefore, would have to reside in code and be supported by the relational architecture. There are several ways to achieve this, but before we jump in, let's set up three classes to represent the tables above.

<?php

class Car extends Zend_Db_Table
{
	protected $_name = 'cars';
}

class Truck extends Car
{
	protected $_name = 'trucks';
	protected $_rowClass = 'App_Db_Table_Row_Truck';

	protected $_referenceMap = array(
		'Car' => aray(
			'columns' => 'parent_id',
			'refTableClass' => 'Car',
			'refColumns' => 'id'
		)
	);
}

class SportsCar extends Car
{
	protected $_name = 'sports_cars';
	protected $_rowClass = 'App_Db_Table_Row_SportsCar';

	protected $_referenceMap = array(
		'Car' => aray(
			'columns' => 'parent_id',
			'refTableClass' => 'Car',
			'refColumns' => 'id'
		)
	);
}

Fetching a row using Truck or SportsCar will only return columns in their respective tables. Ideally we want a situation where fetching a row from either table also returns the brand, because by inheritance in this context it should have its supertable's columns as well. I'll suggest two ways to achieve this, and you'll notice that which method you choose is a tradeoff between efficiency and simplicity.

On-demand approach

Using this approach, the supertable's columns are fetched the first time a reference to its columns are made. Let's code up App_Db_Table_Row_Truck as an example:

<?php

class App_Db_Table_Row_Truck extends Zend_Db_Table_Row
{
	protected $_parent = NULL;

	public function __get ($columnName)
	{
		if (array_key_exists($columnName, $this->_data)
		{
			return $this->_data[$columnName];
		}

		if ($this->_parent === NULL)
		{
			$this->_parent = $this->findParentRow('Car');
		}

		return isset($this->_parent->$columnName) ? $this->_parent->$columnName : NULL;
	}
}

You can now reference $truck->brand, assuming $truck is an instance of App_Db_Table_Row_Truck, as well as $truck->number_of_wheels. There's nothing fancy going on here – it's just a display of how extensible the framework is – but it is making a second query to fetch the truck's inherited columns. If efficiency is not your key concern, then I recommend this approach. And although the Car supertable is hardcoded in this example, but it would be easy to generalize this code by creating an $_inheritsFrom attribute in the model.

Fetch-interception method

(Yes, I had some fun inventing these names)

This approach works quite simply by subclassing Zend_Db_Table and cunningly overriding the _fetch() method. In case you aren't familiar with it, _fetch() is the final port of call for fetchRow() and fetchAll(), which are used to retrieve rows and rowsets respectively. Out of the box, _fetch() looks like this:

<?php

protected function _fetch(Zend_Db_Table_Select $select)	
{
	$stmt = $this->_db->query($select);
	$data = $stmt->fetchAll(Zend_Db::FETCH_ASSOC);
	return $data;
}

The trick here is to add to $select a join to the supertable. So let's build App_Db_Table_Inheritable:

<?php

class App_Db_Table_Inheritable extends Zend_Db_Table
{
	protected $_inheritsFrom = NULL; // will be 'Car' later

	protected function _fetch (Zend_Db_Table_Select $select)
	{
		if ($this->_inheritsFrom !== NULL)
		{
			$parent = new $this->_inheritsFrom;
			$parentTable = $parent->getTable();

			$select->setIntegrityCheck(false); // needed to allow the join

			$select->join(
				array('parent' => $parentTable),
				$parentTable.'.id = '.$this->getTable().'.parent_id
			);
		}

		return parent::_fetch($select);
	}
}

To complete this example, let's modify Car and SportsCar a little:

<?php

// Car.php

class Car extends App_Db_Table_Inheritable
{
	protected $_name = 'cars';
} 

// SportsCar.php

class SportsCar extends Car
{
	protected $_name = 'sports_cars';
	
	protected $_referenceMap = array(...);

	protected $_inheritsFrom = 'Car';
}

What happens now is that when SportsCar fetch*() functions are called, the database query is automatically extended to include a join to the supertable. All row objects fetched this way will then have the data fetched from the supertable's columns. I quite like this technique because it combines sub- and superclass fetches into one query, but clearly it's a little more complex than the on-demand method.

If you can think of any other methods, please let me know. I've found so many uses for these techniques, and although the denormalization introduces possible inefficiencies, the table structure is much simpler and cleaner.

Recent Posts

• Myqron: pull-to-push web services adapter
• Zoopy's new search algorithm and its evolution
• Zoopy launches a RESTful API
• A new, improved Zoopy
• MySQL Search Relevancy Scoring