Zend_Db_Table ease of use

Few people use this amazing class at full capacity. About some of its hidden features as few people guessed, as I did until that moment.

As you know, tables in relational databases are linked by one-to-many and many-to-many relationships. Maybe someone will offer another couple of links, but this article is not to discuss this, but to prompt and direct the thought in the direction we need. I will look at the one-to-many relationship in the implementation, you guessed it, of Zend Framework.

As you know, the Zend_Db_Table class is an object-oriented interface to database tables. The first useful discovery was that Zend_Db_Table is capable of cascading deletion and cascading update of link records, for which it is necessary, and so that when deleting / updating a reference value, records in dependent tables are deleted / updated. For example, we will use the MySQL database with the MyISAM storage engine, which does not support declarative referential integrity.

Now a small digression. For example, we need a small database consisting of the following tables: Products, Units and Groups. It will be about food products (Products), units of measure (Units) and product groups (Groups).
')
Below are SQL queries for creating and populating tables:

 # table of units of products
 DROP TABLE IF EXISTS `units`;
 CREATE TABLE `units` (
   `unit_id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `unit_name` VARCHAR (256) NOT NULL DEFAULT '',
   PRIMARY KEY (`unit_id`)
 ) ENGINE = MYISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

 INSERT INTO `units` SET` unit_name` = 'gram';
 INSERT INTO `units` SET` unit_name` = 'milliliter';

 # product groups table
 DROP TABLE IF EXISTS `groups`;
 CREATE TABLE `groups` (
   `group_id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `group_name` VARCHAR (256) NOT NULL DEFAULT '',
   PRIMARY KEY (`group_id`)
 ) ENGINE = MYISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

 INSERT INTO `groups` SET` group_name` = 'Vegetables';
 INSERT INTO `groups` SET` group_name` = 'Fruits';
 INSERT INTO `groups` SET` group_name` = 'Dairy';
 INSERT INTO `groups` SET` group_name` = 'Meat';

 # product table
 DROP TABLE IF EXISTS `products`;
 CREATE TABLE `products` (
   `product_id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `group_id` INT (10) DEFAULT NULL,
   `unit_id` INT (10) DEFAULT NULL,
   `product_name` VARCHAR (256) NOT NULL DEFAULT ',
   PRIMARY KEY (`product_id`)
 ) ENGINE = MYISAM DEFAULT CHARSET = utf8;

 # Vegetables
 INSERT INTO `products` (` group_id`, `unit_id`,` product_name`) VALUES (1, 1, 'Potatoes');
 INSERT INTO `products` (` group_id`, `unit_id`,` product_name`) VALUES (1, 1, 'Tomato');
 # Fruits
 INSERT INTO `products` (` group_id`, `unit_id`,` product_name`) VALUES (2, 1, 'Apricot');
 INSERT INTO `products` (` group_id`, `unit_id`,` product_name`) VALUES (2, 1, 'Apple');
 # Dairy
 INSERT INTO `products` (` group_id`, `unit_id`,` product_name`) VALUES (3, 1, 'Cheese');
 INSERT INTO `products` (` group_id`, `unit_id`,` product_name`) VALUES (3, 2, 'Milk');
 # Meat
 INSERT INTO `products` (` group_id`, `unit_id`,` product_name`) VALUES (4, 1, 'Veal');
 INSERT INTO `products` (` group_id`, `unit_id`,` product_name`) VALUES (4, 1, 'Pork');

Now that the tables have been created and filled with test data, we proceed to the creation of classes for our tables:

 class Products extends Zend_Db_Table_Abstract
 {
     protected $ _name = 'products';
     protected $ _primary = array ('product_id');
 }
        
 class Units extends Zend_Db_Table_Abstract
 {
     protected $ _name = 'units';
     protected $ _primary = array ('unit_id');
 }
 
 class Groups extends Zend_Db_Table_Abstract
 {
     protected $ _name = 'groups';
     protected $ _primary = array ('group_id');
 }

For simplicity, I put these classes in the controller, for real projects I don’t recommend this. Get data in the product table as follows:

 $ productsTable = new Products;
 $ productsRowset = $ productsTable-> fetchAll ();
 foreach ($ productsRowset as $ row) {
     echo '<pre>'.  print_r ($ row-> toArray (), true).  '</ pre>'.  PHP_EOL;
 }

As you can see, the fetchAll method returns us a rowset (rowset), where each $ row corresponds to a row from our table. If you add the following code in foreach, then it can change a specific entry:

 if ($ row-> product_name == 'Pork') {
     $ row-> product_name = 'Pork Kebab';
     $ row-> save ();
 }

The save method from the abstract class Zend_Db_Table_Row_Abstract is applicable to our row row row, since foreach parses our rowset, which is an instance of the Zend_Db_Table_Rowset class inherited from Zend_Db_Table_Row_Abstract into separate $ row. $ Row themselves are nothing but instances of the Zend_Db_Table_Row class. Manipulations with instances of the Zend_Db_Table_Row class do not require deep knowledge, so we will not dwell on them, but talk about the following.

Suppose you do not like the order of the primary keys in the groups table and you want them to start, for example, with 100, not a problem, but how many actions do you need to take to implement it? Head starts to spin. Look at the products table, in it we have provided an external link to the groups table, key group_id. And after our changes, products must fit their groups. Perhaps this is not the example that you would like to see and he does not pretend to be standing ovations, but he is able to show how the mechanism of interaction between the Zend Framework’s Zend_Db_Table classes is implemented.
So let's get started. In the object model for parent tables, you must specify dependent tables. In the Groups class, you must specify the dependent class Products by adding the following property

 protected $ _dependentTables = array ('Products');

this also needs to be done in the Units class. These tell us that the records in the dependent tables will be taken into account when the parent changes. But for this we need in the Products class to add links to the Units and Groups tables. You can implement this in several ways, for example, to add the $ _referenceMap property to the Products class:

 protected $ _referenceMap = array (
     'refUnits' => array (
         self :: COLUMNS => 'unit_id',
         self :: REF_TABLE_CLASS => 'Units',
         self :: REF_COLUMNS => 'unit_id',
         self :: ON_DELETE => self :: CASCADE,
         self :: ON_UPDATE => self :: CASCADE
     ),
     'refGroups' => array (
         self :: COLUMNS => 'group_id',
         self :: REF_TABLE_CLASS => 'Groups',
         self :: REF_COLUMNS => 'group_id',
         self :: ON_DELETE => self :: CASCADE,
         self :: ON_UPDATE => self :: CASCADE
     )
 );

The $ _referenceMap property allows you to add a link to an external table, so we get a many link to one. It is necessary to define the following parameters

1. associative key
2. the name of the foreign key field in the referring table
3. class name of the table to which we refer
4. name of the field in the table referenced
5. the action that will occur when you delete
6. what happens during the update

Complete code:

 class Products extends Zend_Db_Table_Abstract
 {
     protected $ _name = 'products';
     protected $ _primary = array ('product_id');

     protected $ _referenceMap = array (
         'refUnits' => array (
             self :: COLUMNS => 'unit_id',
             self :: REF_TABLE_CLASS => 'Units',
             self :: REF_COLUMNS => 'unit_id',
             self :: ON_DELETE => self :: CASCADE,
             self :: ON_UPDATE => self :: CASCADE
         ),
         'refGroups' => array (
             self :: COLUMNS => 'group_id',
             self :: REF_TABLE_CLASS => 'Groups',
             self :: REF_COLUMNS => 'group_id',
             self :: ON_DELETE => self :: CASCADE,
             self :: ON_UPDATE => self :: CASCADE
         )
     );
 }

 class Units extends Zend_Db_Table_Abstract
 {
     protected $ _name = 'units';
     protected $ _primary = array ('unit_id');

     protected $ _dependentTables = array ('Products');
 }

 class Groups extends Zend_Db_Table_Abstract
 {
     protected $ _name = 'groups';
     protected $ _primary = array ('group_id');

     protected $ _dependentTables = array ('Products');
 }

Now we have linked tables and we can not worry and calmly update our id keys.
By the way, I forgot to mention that when you delete any group of products, the products that correspond to these groups are also deleted. You can experiment and remove any of them. If you are interested, the _cascadeUpdate and _cascadeDelete methods of the abstract class Zend_Db_Table_Abstract are responsible for updating and deleting and they are called when you call $ row-> save () and $ row-> delete (), respectively. You can also get the parent entry (row) by calling the findParentRow method.

 $ productsTable = new Products;
 $ productsRowset = $ productsTable-> fetchAll ();
 foreach ($ productsRowset as $ row) {
     echo '-----------------------'.  PHP_EOL;
     echo '<pre>'.  print_r ($ row-> toArray (), true).  '</ pre>'.  PHP_EOL;
     echo '<pre>'.  print_r ($ row-> findParentRow ('Units') -> toArray (), true).  '</ pre>'.  PHP_EOL;
     echo '<pre>'.  print_r ($ row-> findParentRow ('Groups') -> toArray (), true).  '</ pre>'.  PHP_EOL;
     echo '-----------------------'.  PHP_EOL;
 }

Or get all the dependent rows (rows) by calling the findDependentRowset method

 $ groupsRowset = $ groupsTable-> fetchAll ();
 foreach ($ groupsRowset as $ row) {
     echo '-----------------------'.  PHP_EOL;
     echo '<pre>'.  print_r ($ row-> toArray (), true).  '</ pre>'.  PHP_EOL;
     echo '<pre>'.  print_r ($ row-> findDependentRowset ('Products') -> toArray (), true).  '</ pre>'.  PHP_EOL;
     echo '-----------------------'.  PHP_EOL;
 }

With the rows obtained (rows), you can also work freely with instances of the Zend_Db_Table_Row class. Good luck!

ps Hi Jos, all your wishes are taken into account.