Composite Rowsets For Many-To-Many Relationships Via Zend_Db_Table

November 15th, 2010 § 5 comments § permalink

One of the hardest problems to solve when developing an ORM of any complexity is in deciding how to handle the retrieval of rows that satisfy a many-to-many relationship, also known as a M:N relationship. From the perspective of an object, there is no such thing as a many to many relationship. There are only two relationships an object understands. The first is the relationship of itself to another object, which is a one to one (1:1) relationship. The second is the relationship of itself to a group of other objects, or a one-to-many (1:N) relationship. It’s not until you look at the relationship of all objects in a system that the many-to-many relationship pattern emerges.

In RDBM systems, rows and their relationships are modeled through the use of foreign keys and foreign key constraints between a left table and a right table. Foreign key constraints, by themselves, can only model 1:1 and 1:N relationship of rows. To model M:N relationships, database developers must get creative. By employing the use of a “3rd party”, and by utilizing foreign keys that model a 1:N relationship, database developers can model a M:N relationship. This 3rd party comes in the form of another table that may or may not have any data model specific information attached to it. This table is generally known as a junction table, but has also been known as a cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, link table, or association table.

Zend_Db_Table_Row And Junction Tables

Zend_Db_Table is a component in Zend Framework that implements the Table Data and Row Data Gateway patterns. In short, a row object attempts to create a single PHP object per actual row in the database table. Furthermore, Zend_Db_Table_Row objects can go as far as to describe, understand, and interrogate these various 1:1, 1:N and M:N relationships. This allows row objects to be able to find and return related row objects in the form of a rowset.

One of the primary tenets of Zend_Db_Table and Zend_Db_Table_Row is to be able to produce consistent row objects. This means that the properties of these row objects should be a complete and logical representation of how the row might look inside the table of the RDBMS.

Some time ago an issue (ZF-6232) was filed against Zend_Db_Table to report that columns from the junction table were being included in the resulting rowset’s row objects. This was causing issues for people who then attempted to save() the row object back to the database. If a developer mistakenly altered one of the junction table values that was accidentally included in the row, Zend_Db_Table_Row would throw an exception since the row object had more columns than the actual row in the database. Given that we want to create consistent, complete and logical row objects, a solution was devised to ensure that the junction table’s row information was not included in the resulting rowset’s rows. Consequently, this meant that anyone relying on this undocumented behavior would no longer be able to get data stored inside the junction table as part of the result set’s row object. This fix was incorporated into the 1.10.2 release.

Over the past several years of working on Zend Framework, I’ve noticed the developer population at large is really good at finding undocumented and previously unthought-of use-cases of Zend Framework components. These use-cases, while sometimes “inventive” to say the least- are also sometimes blatant misuses of a component. It suffices to say that these use-cases are not captured in a unit test and consequently are not protected by backwards compatibility.

Relying on Zend_Db_Table_Row to include junction data is not only an unintended use case but also a misuse of the findManyToManyRowset() functionality provided by Zend_Db_Table_Row. That said, I do want to provide a solution for developers that relied on this behavior of Zend_Db_Table_Row in Zend Framework previous to 1.10.2.

A Solution

While the motivation for creating this class is based on providing a solution to developers who relied on utilizing junction table data in Zend_Db_Table_Row’s many-to-many rowsets, this same technique can be utilized with any ORM or database abstraction layer that handles many-to-many result sets.

Basically, I’ve created a single class that effectively take the place of Zend_Db_Table_Row::findManyToManyRowset() for the purposes of creating an iterable rowset that allows access to both the target many-to-many rowset as well as the junction rowset. This solution is called a Composite Rowset. In this solution, both rowsets (iterators) are kept in sync with one another. This proves to be an ideal solution in a couple of ways. First, it will produce consistent row objects that are explicitly tied to a row in a database. Second, the cost of creating this composite rowset is at the expense of 2 queries: the original many-to-many query and a similar query to retrieve the junction rowset. This is ideal since previously, to get the junction data, findDependentRowset() would have had to been called on each row within the rowset produced by the Zend_Db_Table_Row::findManyToManyRowset().

The API for this Composite Rowset looks like this:

NOTE: Full class located here.

As you can see, the API mirrors that of Zend_Db_Table_Rowset to provide a something that is immediately recognizable. Below is an example of sample usage. For this example, assume there is a typically artist/genre data model that demonstrates a many-to-many relationship. Inside of the junction table we are attempting to track the date that the relationship was created. This examples shows this usage:

Where To Get It & Conclusions

This code is available on my GooeyLabs github account, specifically inside of the Gooey-PHP-5.2-Components repository. (Gooey is my namespace and moniker for my open source code contributions.) Hopefully, those who have found they’ve had issues with the above mentioned fix for Zend_Db_Table_Row::findManyToManyRowset() and junction table data might find value in this class.

Where am I?

You are currently viewing the archives for November, 2010 at Ralph Schindler.