I was struggling getting my data models (as described in the
Zend Framework Quickstart) to work with relations. My first solution was to create a database view that merged that data using joins to collect this data in a format that I could use in my data models. This was going great until I looked at my database where it contained over 20 views (along with 20 data models, mappers and db table gateways) ! So I said to myself there had to be another way.
Fortunately there was a solution, hinted by
Felix De Vliegher on
IRC. He pointed me to the
Zend Framework Zend_Db_Table Relationships page on the
Zend Framework online manual. And although it's clearly specified how things work, I struggled a bit to see how each component was linked to the other and when I should use what.
So, I decided to work out a small application to test and support this relationship modeling.
Step 1: Preparation
Let's start with creating a new
Zend Framework project that we call "datamodels".
zf create project datamodels
This creates a new Zend Framework project that we can use to showcase these examples. We add a few directories to the application structure to store our documentation, our database and our schemas.
These are four tables all related to each other that we use to base our tutorial and test upon.
Translated into SQLite3 this looks like this:
-- filename: schema.sqlite.sql --
create table `user` (
`id` integer primary key autoincrement,
`username` text not null,
`password` text not null
);
create table `contact` (
`id` integer primary key autoincrement,
`user_id` integer not null,
`email` text not null,
`phone` text null,
`fax` text null
);
create table `address` (
`id` integer primary key autoincrement,
`type_id` integer not null default 1,
`user_id` integer not null,
`address1` text not null,
`address2` text null,
`city` text not null,
`state` text null,
`zip` text not null,
`country` text not null
);
create table `address_type` (
`id` integer primary key autoincrement,
`type` text not null
);
Creating the SQLite3 database is now easy as 1,2,3. Just from command line execute
sqlite3 ./data/db/datamodels.db < schema.sqlite.sql and see the magic happen. Our database is set and ready to use.
Our test data is also stored in a file, it's contents can be found here:
-- filename: data.sqlite.sql --
-- Data for user table --
INSERT INTO `user`
VALUES (1, 'testuser1', 'test123');
INSERT INTO `user`
VALUES (2, 'testuser2', 'test234');
-- Data for contact table --
INSERT INTO `contact`
VALUES (1, 1, 'test1@example.com', '1-800-555-1234', '1-800-555-1230');
INSERT INTO `contact`
VALUES (2, 2, 'test2@example.com', '1-800-555-2234', '1-800-555-2230');
-- Data for address table --
INSERT INTO `address`
VALUES (1, 1, 1, '1 Test Home', '', 'Testtown', 'ZF', '1234', 'PHP');
INSERT INTO `address`
VALUES (2, 1, 2, '2 Test Home', '', 'Testtown', 'ZF', '1234', 'PHP');
INSERT INTO `address`
VALUES (3, 2, 2, 'Test Corp, LTD', '4 Test Ave', 'Testtown', 'ZF', '1234', 'PHP');
-- Data for address_type table --
INSERT INTO `address_type`
VALUES (1, 'Home address');
INSERT INTO `address_type`
VALUES (2, 'Billing address');
In application/configs/application.ini we need to add the following lines in order to use our newly generated database:
[production]
...
resources.db.adapter = "Pdo_SQLite"
resources.db.params.dbname = APPLICATION_PATH "/../data/db/datamodels.db"
You also need to define our "Default" namespace in the application bootstrap file Bootstrap.php. This is done by adding the following lines there.
protected function _initAutoload()
{
$autoloader = new Zend_Application_Module_Autoloader(array (
'namespace' => 'Default',
'basePath' => APPLICATION_PATH,
));
return $autoloader;
}
Creating the four data models along with their mappers and table gateways is a bit of work so I've provided the application as-is on a public SVN repository (
https://svn2.hosted-projects.com/in2it/datamodels/tags/step_1). You can export it and start from there.
Step 2: Displaying a user listing
We want to display a listing of users with their contact details and the number of addresses they have, more or less like the following table:
Username
| E-mail
| Phone
| Fax
| # addresses
|
testuser1
| test1@example.com
| 1-800-555-1234
| 1-800-555-1230
| 1
|
testuser2
| test2@example.com
| 1-800-555-2234
| 1-800-555-2230
| 2
|
As you can see now, with this listing we have 3 tables combined and without some relational information between the models it's hard to get this kind of listing.
Let's set up our dbTable classes to work with relationships.
class Default_Model_DbTable_User extends Zend_Db_Table_Abstract
{
protected $_name = 'user';
protected $_dependentTables = array (
'Default_Model_DbTable_Contact',
'Default_Model_DbTable_Address',
);
}
class Default_Model_DbTable_AddressType extends Zend_Db_Table_Abstract
{
protected $_name = 'address_type';
protected $_dependentTables = array ('Default_Model_DbTable_Address');
}
class Default_Model_DbTable_Contact extends Zend_Db_Table_Abstract
{
protected $_name = 'contact';
protected $_referenceMap = array (
'User' => array (
'columns' => array ('user_id'),
'refTableClass' => 'Default_Model_DbTable_User',
'refColumns' => array ('id'),
),
);
}
class Default_Model_DbTable_Address extends Zend_Db_Table_Abstract
{
protected $_name = 'address';
protected $_referenceMap = array (
'User' => array (
'columns' => array ('user_id'),
'refTableClass' => 'Default_Model_DbTable_User',
'refColumns' => array ('id'),
),
'Type' => array (
'columns' => array ('type_id'),
'refTableClass' => 'Default_Model_DbTable_AddressType',
'refColumns' => array ('id'),
),
);
}
In order to use these relations, we create a new model and mapper for listing these accounts.
class Default_Model_Accounts extends Default_Model_Abstract
{
protected $_username;
protected $_email;
protected $_phone;
protected $_fax;
protected $_addressCount;
public function getUsername ()
{
return $this->_username;
}
public function setUsername ($username)
{
$this->_username = (string) $username;
return $this;
}
public function getEmail ()
{
return $this->_email;
}
public function setEmail ($email)
{
$this->_email = (string) $email;
return $this;
}
public function getPhone ()
{
return $this->_phone;
}
public function setPhone ($phone)
{
$this->_phone = (string) $phone;
return $this;
}
public function getFax ()
{
return $this->_fax;
}
public function setFax ($fax)
{
$this->_fax = (string) $fax;
return $this;
}
public function getAddressCount ()
{
return $this->_addressCount;
}
public function setAddressCount ($addressCount)
{
$this->_addressCount = (int) $addressCount;
return $this;
}
public function getMapper()
{
if (null === $this->_mapper) {
$this->setMapper('Default_Model_Mapper_Accounts');
}
return $this->_mapper;
}
public function populate($row)
{
if (is_array($row)) {
$row = new ArrayObject($row, ArrayObject::ARRAY_AS_PROPS);
}
if (isset ($row->id)) {
$this->setId($row->id);
}
if (isset ($row->username)) {
$this->setUsername($row->username);
}
if (isset ($row->email)) {
$this->setEmail($row->email);
}
if (isset ($row->phone)) {
$this->setPhone($row->phone);
}
if (isset ($row->fax)) {
$this->setFax($row->fax);
}
}
public function toArray()
{
return array (
'id' => $this->getId(),
'username' => $this->getUsername(),
'email' => $this->getEmail(),
'phone' => $this->getPhone(),
'fax' => $this->getFax(),
'address_count' => $this->getAddressCount(),
);
}
}
class Default_Model_Mapper_Accounts extends Default_Model_Mapper_Abstract
{
public function getDbTable()
{
if (null === $this->_dbTable) {
$this->setDbTable('Default_Model_DbTable_User');
}
return $this->_dbTable;
}
public function fetchAll($className, $where = null, $order = null, $count = null, $offset = null)
{
$entries = array ();
$model = null;
if (!is_string($className)) {
require_once 'Zend/Exception.php';
throw new Zend_Exception('Model class name should be a string');
}
if (is_string($className)) {
if (!class_exists($className)) {
require_once 'Zend/Exception.php';
throw new Zend_Exception('Non-existing model class name provided');
}
}
if (null !== ($resultSet = $this->getDbTable()->fetchAll($where, $order, $count, $offset))) {
foreach ($resultSet as $row) {
$model = new $className;
if (!$model instanceof Default_Model_Abstract) {
require_once 'Zend/Exception.php';
throw new Zend_Exception('Invalid model class provided');
}
// let's get contact details first
$contacts = $row->findDependentRowset(
'Default_Model_DbTable_Contact',
'User'
);
$contact = $contacts->current();
$model->populate($contact);
unset ($contacts, $contact);
// let's see how many addresses this contact has
$addresses = $row->findDependentRowset(
'Default_Model_DbTable_Address',
'User'
);
$addressCount = count($addresses);
$model->setAddressCount($addressCount);
unset ($addresses, $addressCount);
// now it's time to add our user details to the model
$model->populate($row);
$entries[] = $model;
unset ($model);
}
}
return $entries;
}
}
And to view our completed table, we need to modify our view partial userlisting.phtml:
<tr>
<td><?php echo $this->escape($this->username) ?></td>
<td><?php echo $this->escape($this->email) ?></td>
<td><?php echo $this->escape($this->phone) ?></td>
<td><?php echo $this->escape($this->fax) ?></td>
<td><?php echo $this->escape($this->address_count) ?></td>
</tr>
Now we have our table set as we want it:
You can download this step from the repo to see the changes yourself:
https://svn2.hosted-projects.com/in2it/datamodels/tags/step_2.
Conclusion
Once you have figured out how these relationships work, you can extends your model capabilities without resorting to other solutions (like using database views) or maintaining crappy code.
On the other hand, It would be nice if I could use my already created models with their functionality in one go, so I can link my datamodels instead of my data sources. Need to figure this one out.
