August 29, 2011 at 16:11Zend_Db_Table ease of use
Few people use this amazing class at full capacity. Few guessed about some of its hidden possibilities, just like I did until this moment.
As you know, tables in relational databases are linked by one to many and many to many relationships. Perhaps someone will offer a couple more links, but this article is not in order to debate about this, but in order to suggest and direct the idea in the direction we need. I will consider the one-to-many relationship in the implementation of, you probably guessed, 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 updating of connection records, for which it is necessary, so that when deleting / updating a reference value, records in dependent tables are deleted / updated. For example, we will use a MySQL database with the MyISAM data 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. We will talk about food products (Products), units of measure (Units) and product groups (Groups).
Below are SQL queries for creating and populating tables:
Now that the tables are created and filled with test data, we proceed to create classes for our tables:
For simplicity, I put these classes in the controller; for real projects, I highly recommend this. You can get the data in the product table as follows:
As you can see, the fetchAll method returns a rowset, where each $ row corresponds to a row from our table. If you add the following code to foreach, then it is able to change a specific record:
To our "row" $ row, we apply the save method from the abstract class Zend_Db_Table_Row_Abstract, since foreach parses our rowset, which is an instance of the class Zend_Db_Table_Rowset, inherited from Zend_Db_Table_Row_Abstract into separate $ row. The $ row itself is nothing more than instances of the Zend_Db_Table_Row class. Manipulating instances of the Zend_Db_Table_Row class does not require deep knowledge, so we will not dwell on them, but talk about the following.
Suppose you don’t 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 need to be done to implement this? The head begins to spin. Look at the products table, in it we provided an external connection to the groups table, by the group_id key. And after our changes, products must fit their groups. Perhaps this is not an example that you would like to see and does not pretend to a standing ovation, but it is able to show how the mechanism of interaction between the Zend_Db_Table classes is implemented in the Zend Framework.
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
it also needs to be done in the Units class. These tells us that records in dependent tables will be taken into account when the parent changes. But for this we need to add links to the Units and Groups tables in the Products class. There are several ways to implement this, for example, adding the $ _referenceMap property to the Products class:
The $ _referenceMap property allows you to add a link to an external table, so we get a many-to-one relationship. It is necessary to define the following parameters in it
: 1. associative key
2. name of the foreign key field in the referencing table
3. name of the class of the table to which we refer
4. name of the field in the table to which we refer
5. action that will happen when deleting
6. action that will happen when updating
Code as a whole:
Now we have related tables and we can not worry and calmly update our id keys.
By the way, I forgot to mention that when you delete a product group, the products themselves that correspond to these groups are also deleted. You can experiment and delete any of them. If you are interested, then the _cascadeUpdate and _cascadeDelete methods of the abstract class Zend_Db_Table_Abstract are responsible for updating and deleting and they are called when $ row-> save () and $ row-> delete () are called, respectively. You can also get the parent record (row) by calling the findParentRow method
Either get all the dependent records (rows) by calling the findDependentRowset method
With the received rows, you can also freely work as with instances of the Zend_Db_Table_Row class. Good luck
ps Hi Jos, all your wishes have been taken into account.
As you know, tables in relational databases are linked by one to many and many to many relationships. Perhaps someone will offer a couple more links, but this article is not in order to debate about this, but in order to suggest and direct the idea in the direction we need. I will consider the one-to-many relationship in the implementation of, you probably guessed, 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 updating of connection records, for which it is necessary, so that when deleting / updating a reference value, records in dependent tables are deleted / updated. For example, we will use a MySQL database with the MyISAM data 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. We will talk about food products (Products), units of measure (Units) and product groups (Groups).
Below are SQL queries for creating and populating tables:
# product units table 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 group 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` = 'Fruit'; 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, 'Potato'); INSERT INTO `products` (` group_id`, `unit_id`,` product_name`) VALUES (1, 1, 'Tomato'); # Fruit 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, 'Brynza'); 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 are created and filled with test data, we proceed to create 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 highly recommend this. You can get the data in the product table as follows:
$ productsTable = new Products;
$ productsRowset = $ productsTable-> fetchAll ();
foreach ($ productsRowset as $ row) {
echo ''. print_r ($ row-> toArray (), true). ''
'. PHP_EOL;
}
As you can see, the fetchAll method returns a rowset, where each $ row corresponds to a row from our table. If you add the following code to foreach, then it is able to change a specific record:
if ($ row-> product_name == 'Pork') {
$ row-> product_name = 'Pork Kebab';
$ row-> save ();
}
To our "row" $ row, we apply the save method from the abstract class Zend_Db_Table_Row_Abstract, since foreach parses our rowset, which is an instance of the class Zend_Db_Table_Rowset, inherited from Zend_Db_Table_Row_Abstract into separate $ row. The $ row itself is nothing more than instances of the Zend_Db_Table_Row class. Manipulating instances of the Zend_Db_Table_Row class does not require deep knowledge, so we will not dwell on them, but talk about the following.
Suppose you don’t 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 need to be done to implement this? The head begins to spin. Look at the products table, in it we provided an external connection to the groups table, by the group_id key. And after our changes, products must fit their groups. Perhaps this is not an example that you would like to see and does not pretend to a standing ovation, but it is able to show how the mechanism of interaction between the Zend_Db_Table classes is implemented in the Zend Framework.
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');
it also needs to be done in the Units class. These tells us that records in dependent tables will be taken into account when the parent changes. But for this we need to add links to the Units and Groups tables in the Products class. There are several ways to implement this, for example, adding 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-to-one relationship. It is necessary to define the following parameters in it
: 1. associative key
2. name of the foreign key field in the referencing table
3. name of the class of the table to which we refer
4. name of the field in the table to which we refer
5. action that will happen when deleting
6. action that will happen when updating
Code as a whole:
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 related tables and we can not worry and calmly update our id keys.
By the way, I forgot to mention that when you delete a product group, the products themselves that correspond to these groups are also deleted. You can experiment and delete any of them. If you are interested, then the _cascadeUpdate and _cascadeDelete methods of the abstract class Zend_Db_Table_Abstract are responsible for updating and deleting and they are called when $ row-> save () and $ row-> delete () are called, respectively. You can also get the parent record (row) by calling the findParentRow method
$ productsTable = new Products;
$ productsRowset = $ productsTable-> fetchAll ();
foreach ($ productsRowset as $ row) {
echo '-----------------------'. PHP_EOL;
echo ''. print_r ($ row-> toArray (), true). ''
'. PHP_EOL;
echo ''. print_r ($ row-> findParentRow ('Units') -> toArray (), true). '''. PHP_EOL;
echo ''. print_r ($ row-> findParentRow ('Groups') -> toArray (), true). '''. PHP_EOL;
echo '-----------------------'. PHP_EOL;
}
Either get all the dependent records (rows) by calling the findDependentRowset method
$ groupsRowset = $ groupsTable-> fetchAll ();
foreach ($ groupsRowset as $ row) {
echo '-----------------------'. PHP_EOL;
echo ''. print_r ($ row-> toArray (), true). ''
'. PHP_EOL;
echo ''. print_r ($ row-> findDependentRowset ('Products') -> toArray (), true). '''. PHP_EOL;
echo '-----------------------'. PHP_EOL;
}
With the received rows, you can also freely work as with instances of the Zend_Db_Table_Row class. Good luck
ps Hi Jos, all your wishes have been taken into account.