Working with Zend_Db_Table

Apr 16, 2010   #php  #zend framework 

Ever since we standardized our development platform on the Zend Framework, I’ve been investigating how to utilize its DB classes in our model classes. I have read many articles and opinions on using ActiveRecord and integrating with frameworks like Doctrine. However, I decided that I needed a compelling reason to add another framework to our codebase that I would need to maintain and train our developers on. Not to mention, I like the Table_Data_Gateway and Row_Data_Gateway patterns well enough.

I have learned some cool things about Zend_Db_Table and Zend_Db_Table_Row recently that I wanted to implement, since I had never utilized these classes very well. In fact, I usually used Zend_Db_Table as a generic way of creating a class based on a table name. For instance, I have many (and I’m somewhat embarrassed to admit) classes that look like the following:

<?php
 class MyTables extends Zend_Db_Table_Abstract {
 	protected $_name = ''mytable'';
 	protected $_primary = ''mytableID'';

  	public function getSomeData($mytableID) {
 		$select = $this->select()->from($_name, array(''someData1'', ''someData2'')->where(''mytableID = ?'', $mytableID);
  		return $this->fetchAll($select);
 	}
 }

Now this code by itself isn’t too bad, but it does make the resulting class that’s returned a little harder to predict the results… I’m only receiving 2 fields worth of data and my results are in a very generic Zend_Db_Table_Row class.

In addition, I also frequently have a function like this:

<?php
 public function saveData($mytableID, $someData1, $someData2) {
 	$fields = array (
 		''someData1'' => $someData1,
 		''someData2'' => $someData2
 	);
 	return $this->update($fields)->where(''mytableID = ?'', $mytableID);
 }

Yuck! Why the heck am I saving my results using a Table class? Shouldn’t I be saving the results utilizing at least the Zend_Db_Table_Row classes I’m getting my results in?

Ok, so maybe I’m being a little dramatic… But here’s the thing. I learned about a nasty little bug that happened all because I was trying to be smarter than my code… I thought it’d be great if I created a select statement like this:

<?php
 public function getRandomData($mytableID) {
 	$fields = array (
 		''mytableID'',
 		''mytable.someData1'',
 		''mytable.someData2'',
 		''myothertable.someData1'',
 		''myothertable.someData3''
 	);
 	$select = $this->select()->from($this->_name, $fields)->joinLeft(''myothertable'', "$this->_name.myothertableID = myothertable.myothertableID")->where(''mytableID = ?'', $mytableID);
  	$select->setIntegrityCheck(false);
 	return $this->fetchAll($select);
 }

If you notice the highlighted lines, you can see the problem. What I obviously wanted to accomplish here was getting a bunch of results in a Zend_Db_Table_Row class. I didn’t want to mess with creating code just to get some results between the two tables. Here’s what I thought I would have had to write:

<?php
$mytables = new MyTables();
 $rows = $mytables->find($mytableID);
 $myothertables = new MyOtherTables();
 foreach ($rows as $mytable) {
 	$myothertablesRows = $myothertables->find($mytable->myothertableID);
 	$myothertable = $myothertables->current(); //retrieves the first entry (and only entry in this one-to-one scenario).
  	//Do stuff
 }

This code is less than optimal. I have to instantiate a new MyOtherTables() object and then I have to do a query on each record of the MyTables() class. Uggh. Fortunately, Zend has provided some really nice functionality that you can utilize to make these kinds of links easy to use. Let’s look at some of the features that I had overlooked that are now making my life much easier.

  1. Custom Zend_Db_Table_Row classes: Rather than have a table class pass a generic Zend_Db_Table_Row class, you can have them pass subclasses. This makes it very nice to check object types when passing objects to functions. Using our MyTables class, here’s all you need to do:

    class MyTableRow extends Zend_Db_Table_Row_Abstract {};
    class MyTables extends Zend_Db_Table_Abstract {
        protected $_name = ''mytable'';
        protected $_primary = ''mytableID'';
        protected $_rowClass = ''MyTableRow'';
    }
    

Now every row result from a MyTables object will be an instance of MyTableRow. You can also add custom methods to your subclass and they will be included with every object!

  1. Dependencies between tables: By creating a dependency between tables, you can easily access row results without worry. Let’s look at the code that you would use to create the dependency, then let’s look at how we could change my initial “joined” code.

     class MyTables extends Zend_Db_Table_Abstract {
        //code from previous example
        protected $_dependentTables = array(''MyOtherTables'');
     }
     class MyOtherTables extends Zend_Db_Table_Abstract {
        //code from previous example
        protected $_referenceMap = array ( 
            ''MyTableRow'' => array (
                ''columns'' => array (''myothertableID''),
                ''refTableClass'' => ''MyTables'', 
                ''refColumns'' => array (''myothertableID'')
        );
     }
    

By creating a reference map, the MyOtherTables class knows how the two tables are joined together. Let’s utilize the new reference map.

 $mytables = new MyTables();
 $rows = $mytables->find($mytableID);
 foreach ($rows as $mytable) {
    $myothertablesRows = $mytable->findDependentRowset(''MyOtherTables'');
    $myothertable = $myothertables->current();
 //retrieves the first entry (and only entry in this one-to-one scenario).
        //Do stuff 
}

This may not seem like much, but just think about how powerful it really is. Once I set up the relationship in the table class, I no longer have to worry about how the tables are connected. I just ask for the results and the framework does the work for me.

  1. Utilize built in class methods: Each result returned is an object. Rather than creating various “save” functions in the table class, it will usually be better to just save the row. Consider the following examples.

Save the results: Example 1

 $mytable = new MyTables();
 $myTableRow = $mytable->find($mytableID)->current();
 $mytable->saveData($myTableRow->mytableID, $newSomeData1, $newSomeData2);

Example 2

 $mytable = new MyTables();
 $myTableRow = $mytable->find($mytableID)->current();
 $myTableRow->someData1 = $newSomeData1;
 $myTableRow->someData2 = $newSomeData2; $myTableRow->save();

The differences between examples one and two may not seem to be too different at first glance. However, when you consider the work involved, example 2 quickly becomes a favored choice. In the first example, you had to create a new function in the table class. This function required you to create a SQL query, and you don’t get the updated changes to the $myTableRow object. You have to request it again! In addition, if you decided that you also wanted to update another field, you would have to alter the function to include the change. In the second example, you have none of the disadvantages of the first method. The only disadvantage I see, is that you can only update one row at a time.

Create a new row: I have rarely utilized the ability to create skeleton classes. I know several developers who roll their own model classes who have implemented such a feature. Why develop your own, when Zend_Db_Table will give you a skeleton for free?

$mytables = new MyTables();
$newRow = $mytables->createRow();
$newRow->someData1 = $newSomeData1;
$newRow->someData2 = $newSomeData1;
$newRow->save();
//This will insert the data AND update the row class so that it has the new autoincrement mytableID value in it! Amazin''.

Notice how this code is essentially the same as the update code? Once you have a row object, it’s exactly the same. By using the same workflow, your code can be infinitely more flexible.

Conclusion

The Zend_Db_Table* classes provide some awesome functionality. Unfortunately, it appears that many developers (at least ones I know) haven’t taken advantage of these powerful features. If the Table_Data_Gateway pattern is one that you enjoy, then Zend Framework provides you with some amazing features that will improve how your model classes interact with your database.

The features I covered here are definitely not all that available. These are just a few that have made my life easier as a developer lately as I’ve had to refactor my code.