Why I Prefer Propel over Zend_Db(_Table)
First of all: I like Zend_Db and its subcomponents. I used it in some of my private and also business projects. It is a firm tool to work with your database.
The post is focussed on "ORM features" so I will center on the subcomponent Zend_Db_Table. In the beginning I'll show what you can do with Zend_Db_Table, afterwards I point out which problems Zend_Db_Table in my opinion has and how Propel solves it.
Zend_Db_Table Features
I use the same table layout like it is used in the official Zend_Db manual. That means I have the following tables:
Database Layout
| Tables_in_test |
+----------------+
| accounts |
| bugs |
| bugs_products |
| products |
+----------------+
In detail the tables look like this:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| account_name | varchar(100) | NO | PRI | NULL | |
+--------------+--------------+------+-----+---------+-------+
mysql> SHOW COLUMNS FROM bugs;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| bug_id | int(11) | NO | PRI | NULL | auto_increment |
| bug_description | varchar(100) | NO | | NULL | |
| bug_status | varchar(20) | NO | | NULL | |
| reported_by | varchar(100) | YES | MUL | NULL | |
| assigned_to | varchar(100) | YES | MUL | NULL | |
| verified_by | varchar(100) | YES | MUL | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
mysql> SHOW INDEX FROM bugs;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| bugs | 0 | PRIMARY | 1 | bug_id | A | 0 | NULL | NULL | | BTREE | |
| bugs | 1 | reported_by | 1 | reported_by | A | 0 | NULL | NULL | YES | BTREE | |
| bugs | 1 | assigned_to | 1 | assigned_to | A | 0 | NULL | NULL | YES | BTREE | |
| bugs | 1 | verified_by | 1 | verified_by | A | 0 | NULL | NULL | YES | BTREE | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> SHOW COLUMNS FROM products;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| product_id | int(11) | NO | PRI | NULL | auto_increment |
| product_name | varchar(100) | NO | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
mysql> SHOW INDEX FROM products;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| products | 0 | PRIMARY | 1 | product_id | A | 0 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> SHOW COLUMNS FROM bugs_products;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| bug_id | int(11) | NO | PRI | NULL | |
| product_id | int(11) | NO | PRI | NULL | |
+------------+---------+------+-----+---------+-------+
mysql> SHOW INDEX FROM bugs_products;
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| bugs_products | 0 | PRIMARY | 1 | bug_id | A | 0 | NULL | NULL | | BTREE | |
| bugs_products | 0 | PRIMARY | 2 | product_id | A | 0 | NULL | NULL | | BTREE | |
| bugs_products | 1 | bug_id | 1 | bug_id | A | 0 | NULL | NULL | | BTREE | |
| bugs_products | 1 | product_id | 1 | product_id | A | 0 | NULL | NULL | | BTREE | |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
The Table Class
In order to operate for example on the table bugs, you can create a class which extends Zend_Db_Table_Abstract.
{
protected $_name = 'bugs';
}
That's it. If you instantiate the class Bugs, you get an object which represents the bugs table (see Table Data Gateway). Those four lines are enough to enable the developer to insert new rows, update existing rows, delete rows and of course select rows over a simple object orientated API.
Some Examples
$bugs->insert(array(
'bug_description' => 'A description ...',
'bug_status' => 'NEW'
));
$select = $bugs->select()->where('bug_status = ?', 'NEW');
$rowset = $bugs->fetchAll($select);
foreach ($rowset as $row) {
$row->bug_status = 'CLOSED';
$row->save();
}
$bugs->delete(
$bugs->getAdapter()->quoteInto('bug_status = ?', 'CLOSED')
);
As you can see I create a new bug, select all bugs which have the status "NEW", iterate over the set of rows, change each row and save it back to the database. Afterwards I delete all bugs with the status "CLOSED". The API is easy, the code is readable.
What I have is a special table class which represents a table from my database. I can add custom methods to it for recurring and/or complex table operations. That is good. But additionally I need a Bug class which represents a single row from my bugs table. Luckily I can tell the Bugs class that it shall return Bug objects instead of generic Zend_Db_Table_Row objects.
{
protected $_name = 'bugs';
protected $_rowClass = 'Bug';
}
The class Bug must extend Zend_Db_Table_Row_Abstract. If I execute a select statement via the Bugs class, it will return Bug objects. Now I can implement row specific functionality in my Bug class. The same is possible with the rowset classes.
One big thing is still missing which is inevitable if you are working with a relational database.
Relationships
Relationsships are noted in the table classes. I start with the simplest table:
{
protected $_name = 'accounts';
protected $_rowClass = 'Account';
protected $_dependentTables = array('Bugs');
}
The table accounts has a dependent table called bugs. You only declare dependent tables if your DBMS does not support cascading write operations (referential integrity). The class for the table products looks pretty similar:
{
protected $_name = 'products';
protected $_rowClass = 'Product';
protected $_dependentTables = array('BugsProducts');
}
Now it gets more exciting. The table bugs has references to the table accounts. I declare them as "reference rules" in the $_referenceMap attribute:
{
protected $_name = 'bugs';
protected $_rowClass = 'Bug';
protected $_dependentTables = array('BugsProducts');
protected $_referenceMap = array(
'Reporter' => array(
'columns' => 'reported_by',
'refTableClass' => 'Accounts',
'refColumns' => 'account_name'
),
'Engineer' => array(
'columns' => 'assigned_to',
'refTableClass' => 'Accounts',
'refColumns' => 'account_name'
),
'Verifier' => array(
'columns' => 'verified_by',
'refTableClass' => 'Accounts',
'refColumns' => 'account_name'
)
);
}
For every reference rule I have to note the column in the dependent table ("columns"), the parent table class ("refTableClass") and the referenced column in the parent table ("refColumns"). In the reference map you can additionally declare the cascading write operations (onDelete and onUpdate, see Cascading Write Operations in the official documentation for more). One table is still missing:
{
protected $_name = 'bugs_products';
protected $_referenceMap = array(
'Bug' => array(
'columns' => 'bug_id',
'refTableClass' => 'Bugs',
'refColumns' => 'bug_id'
),
'Product' => array(
'columns' => 'product_id',
'refTableClass' => 'Products',
'refColumns' => 'product_id'
)
);
}
My table objects are now able to work with relations. An example: I will select a random account from the accounts table and afterwards select all bugs reported by that account.
$account = $accounts->fetchRow(null, new Zend_Db_Expr('RAND()'));
$bugs = $account->findBugsByReporter();
The syntax is "find<TABLE>By<RULE>". I can query the parent table with the same schema:
$bug = $bugs->current();
$reporter = $bug->findParentAccountsByReporter();
The syntax is "findParent<TABLE>By<RULE>". Many-to-many relationships with an intersetion table are possible too:
$bug = $bugs->fetchRow(null, new Zend_Db_Expr('RAND()'));
$products = $bug->findProductsViaBugsProductsByBug();
The syntax is "find<TABLE>Via<INTERSECTIONTABLE>By<RULE>". The syntax is probably a bit confusing in the first moment but you get used to it fast.
Ok, the intro was a bit lengthy. Next I will show what annoys me about Zend_Db_Table and how Propel does it better.
Zend_Db_Table Drawbacks and Propel Advantages
First let me say: All the things I showed with Zend_Db_Table above, Propel can do too.
Table Metadata
Remember how a simple table class looks like?
{
protected $_name = 'bugs';
}
The only thing I configure is the table's name. What happens if I use such a class? How does it get the needed metadata? The manual says:
By default, Zend_Db_Table_Abstract queries the underlying database for table metadata whenever that data is needed to perform table operations.
That happens everytime when I execute for example an insert or a find method. A Zend_Db advocate would probably argument that I can utilize a cache. Yes I can do that. It's actually very easy to set that up:
$frontendName,
$backendName,
$frontendOptions,
$backendOptions
);
Zend_Db_Table_Abstract::setDefaultMetadataCache($cache);
From now on every table object can use the cache instance to save the requested metadatas. The DESCRIBE queries are reduced to a minimum. But in my opinion that's not the best solution. Calling Zend_Cache is not as expensive as querying the database but it still costs performance. Additionally I must have a strategy if I want to take a database change live. Do I just put it live and wait until the cache times out? Could lead to weird behaviour. I could invalidate all metadata cache records after deploying the new version on the live systems. What if I use memcache? I can't even put a tag on all metadata cache entries so I have to maintain a list of all metadata cache record names in order to kill them with a script. Or I invalidate the relevant ones by hand. Doesn't sound very comfortable to me.
Luckily Zend_Db_Table offers a third option. I can hardcode the table metadata in my table class. From the manual:
{
protected $_name = 'example';
protected $_metadata = array(
'<column_name>' => array(
'SCHEMA_NAME' => <string>,
'TABLE_NAME' => <string>,
'COLUMN_NAME' => <string>,
'COLUMN_POSITION' => <int>,
'DATA_TYPE' => <string>,
'DEFAULT' => NULL|<value>,
'NULLABLE' => <bool>,
'LENGTH' => <string - length>,
'SCALE' => NULL|<value>,
'PRECISION' => NULL|<value>,
'UNSIGNED' => NULL|<bool>,
'PRIMARY' => <bool>,
'PRIMARY_POSITION' => <int>,
'IDENTITY' => <bool>,
),
// additional columns...
);
}
In principle I like this solution. It's plain PHP what means it's fast. And there is less magic, because I can see what the table class knows. But imagine you have a database with 100 tables and an average of 5 columns per table. I bet you do everything to prevent database changes. 
So how does Propel solve this problem? Propel hardcodes the metadata in PHP too. But you, as the developer, don't have to care about it. If you change the database, just run the Propel Generator again. Best solution is probably to make the generation a task in your deployment script. Propel regenerates all table classes and adapts the database changes instantly. So no manual work for you.
Where Is My Autocompletion?
Zend_Db_Table is very generic. Doesn't sound too bad, does it? However my IDE has no idea about a lot of methods and attributes the table and row classes offer me. Remember all those find* methods? None of them are available via autocompletion. That is logical. How could Zend_Db_Table_Abstract know what reference rules I defined in my Bugs class which extends the abstract class. The attributes in a row object are realized with the magic methods __get, __set, __isset and __unset. Whenever I use a row class I have to manually look up what columns the table has, because the IDE does not offer autocompletion for magic attributes.
I defined a special row class in every table class (e.g. the table class Bugs returns sets of Bug objects intead of Zend_Db_Table_Row objects). Now if I use the fetchRow() method of my Bugs class, it returns a Bug object. But my IDE still thinks it returned a Zend_Db_Table_Row object because the @return phpdoc tag in Zend_Db_Table_Abstract still says so. That is annoying.
Again the Propel Generator can score here because it materializes all those magic methods and adds correct phpdoc to them so my IDE knows about everything.
Working with Objects
I'm using an ORM because I want to think in terms of objects and not database tables. But sometimes Zend_Db_Table prevents me from doing exactly that. For example I create a new bug:
$assignee = $accounts->find("Mike");
$bugs = new Bugs();
$bug = $bugs->createRow();
$bug->bug_description = 'My description ...';
$bug->bug_status = 'ASSIGNED';
and now I want to add my assignee to it:
$bug->save();
What is saved in the column assigned_to? Not the primary key but"Object". You have to pass the primary key! Passing the whole object is not possible. That is not very intuitive. Let's do the same with Propel:
$bug = new Bug();
$bug->setBugDescription('My description ...')
->setBugStatus('ASSIGNED')
->setAssignee($assignee)
->save();
That is what I mean with thinking in term of objects. It seems like nothing but it makes work a lot easier.
Conclusion
As I said in the very beginning: Zend_Db_Table is a firm tool. But Propel is more conveniant. You don't have to concentrate so much on what I may pass here or how the method's name is there. You can focus on building the actual application. In my opinion Zend_Db_Table could improve a lot if it would offer the possibility to generate table and row classes like propel does it. The Zend Framework offers all tools needed (Zend_CodeGenerator, Zend_Reflection). A colleague of mine is already working on a solution and hopefully release it soon. Until then I will definitely continue using Propel.
Why I Prefer Propel over Zend_Db(_Table) by Mike Rötgers is licensed under a Creative Commons Attribution 3.0 Germany License.
Categories
Recent Posts
Recent Comments
- DS on Why I Prefer Propel over Zend_Db(_Table)
- Scott Lively on Integrate Propel with the Zend Framework
- Jaco on Integrate Propel with the Zend Framework
- Taha Bayrak on Why I Prefer Propel over Zend_Db(_Table)
- LimeSpace – IT » Der Wochenrückblick:PDF’s und Zend, ORMs, CSS Tabs on Integrate Propel with the Zend Framework
October 21st, 2009 - 16:06
I would prefer a solution that insulates my Bug object totally from the database (Zend_Entity).
October 23rd, 2009 - 03:27
@Giorgio: Me, too, at least in principle. But as a ZF noob, I’m still struggling with even these simple cases.
@Mike: Thanks for the great rundown on some of the Zend_Db stuff. Very helpful, especially the insights on the IDE advantage to having actual generated classes rather than depending on the PHP magic methods.
But I have a question on Propel workflow. Assume I’m using Propel and I change my db schema – add a field, say. I use the Propel Generator to recreate my classes. Fair enough.
But what if I had added custom methods to those classes? Those methods gets blasted by the regeneration. Do I need to put the custom methods in a subclass and then just regenerate the parents?
Again, thanks for a very informative post.
October 23rd, 2009 - 11:21
@Giorgio: Thanks for the hint. I’ll read through the proposal over the weekend.
@David: If you let Propel create all its stuff for the table “Bugs”, you get a row class BugBase where Propel puts all its logic in and a class called Bug, which extends BugBase. Same procedure for the table classes. That class will never be touched again by Propel, so you can add your custom code in there without losing it.
November 3rd, 2009 - 05:22
@Giorgio: Zend_Entity will no longer be supported by Zend. Instead, Zend_Doctrine will be integrated to the framework.
March 3rd, 2010 - 06:38
seems like this is on the lines of Hibernate. But how about complex queries with joins etc. ? does propel provide a counterpart to HQL? to truly abstract your calls from underlying database’s sql dialect it is very important to have something like HQL