Zend_Db_Table easy to use

hardly anyone uses this amazing class at full capacity. Some of its hidden features also few people knew, as I did now.

As you know tables in a relational database associated with a relationship one-to-many and many-to-many. Maybe someone will offer another couple of links, but this article is not to debate this issue, and to prompt and guide the thought in the right direction. I will consider the one-to-many relationship in the implementation, as you probably guessed, the Zend Framework's.

/ > 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 capable of producing a cascade delete and cascade update relationship records, for which it is necessary, Yes to deleting/updating reference values was removed/updated records in dependent tables. For example we will use a MySQL database with the data storage engine MyISAM which does not support declarative referential integrity.

Now a small digression. We for example need a small database consisting of following tables: Products, Units and Groups. We will focus on the food (Products), units (Units) and product groups (Groups).

Below SQL queries to create and populate tables:
the

#  table  of units of measurement
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` = 'grams';
INSERT INTO `units` SET `unit_name` = 'milliliter';

# table product groups
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';

# table products
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');
# 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, '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 when tables are created and populated with test data and begin to create classes for tables:

the

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 into a controller for real projects to do you is not recommended. To obtain the data in the products table, use the following:

the

$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 method fetchAll returns us to a rowset (rowset), where each $row is a row from our table. If you add the following code in the foreach, then he is able to change a specific record:

the

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

To our "row". $row applies the save method from abstract class Zend_Db_Table_Row_Abstract because our foreach parses the rowset, which is an instance of Zend_Db_Table_Rowset class that inherits from Zend_Db_Table_Row_Abstract on a separate $row. Themselves $row nothing but instances of the concrete class Zend_Db_Table_Row. Manipulation of instances of the concrete class Zend_Db_Table_Row does not require deep knowledge, so we won't dwell on them, and talk about the following.
Let's say you don't like the order of the primary keys in the table groups and you want to begin in, for example, with 100, not a problem, but how many actions need to take to implement it? The head starts to go around. Look at the products table, we have provided an external link to table groups, the key is the group_id. And after our changes, the products should match their groups. Perhaps not the example you would like to see and it does not claim to applause, but he is able to show how the mechanism of the interaction of classes in the Zend Framework Zend_Db_Table e.
So let's start. In the object model for the parent tables, you must specify the dependent table. In class Groups, you must specify a dependent class of Products by adding the following property

the

protected $_dependentTables = array('Products');

also this must be done in class Units. These tells us that the entries in the dependent tables will be taken into account when modifying the parent. But for this we need in class Products to add tables Units and Groups. This can be realized in several ways, for example to add the property $_referenceMap class Products:

the

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 property $_referenceMap allows you to add a link to an external table, so we get a many-to-one. It is necessary to define the following parameters.

1. associative key
2. the name of the foreign key field in referencing table
3. the class name of the table referenced
4. the field name in the table referenced
5. the action that happens when you remove
6. action that will occur when upgrading

Full code:
the

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');
}

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

the

$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 records (rows) calling a method findDependentRowset

the

$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;
}

Received lines (rows) you are also free to work with instances of the concrete class Zend_Db_Table_Row. Good luck!

p.s. Hi Joss, take into account all your wishes.

Article based on information from habrahabr.ru