Composite Rowsets For Many-To-Many Relationships Via Zend_Db_Table

November 15th, 2010 § 5 comments

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.

Tagged , ,

  • Pingback: Zend Framework in Action » Ralph Schindler: Composite Rowsets For Many-To-Many Relationships Via Zend_Db_Table

  • http://www.davidkmuir.com David

    Why not do it in a single query like before, but only pass in the relevant keys from the target and intersecting tables when creating the row objects?

  • http://ralphschindler.com/ Ralph Schindler

    David, that is also a valid solution. The problem there is that instead of a separate query, you will spend as much time (perhaps more?) in PHP dissecting which result columns go into “proper” row objects. By proper, I mean that the row object will attempt model what a physical row looks like in the database table, and be able to safely store back to the database table when save() or delete() is called on that Zend_Db_Table_Row object.

    I actually experimented with that solution a number of years ago, and it is a completely valid approach. This solution does just fine in that you don’t have to do a query per row.. Basically it means the cost is 2 queries as opposed to N+1 queries to resolve this junction table for data retrieval.

  • Pingback: 岭南六少 – 一朵在LAMP架构下挣扎的云 » 我收藏的技术站点

  • Sascha Schneider

    That is in effect exactly what I wanted. Thank you very much for this article. Great Idea.

What's this?

You are currently reading Composite Rowsets For Many-To-Many Relationships Via Zend_Db_Table at Ralph Schindler.

meta