I give this a big "Argh!"
I decided it was finally time to add multi-layer joins to Xmldoom because I needed to create MANY-TO-MANY relationships. For example:
<table name="orders">
<columns>
<int name="order_id" unsigned="true" auto="true" primary-key="true"/>
<datetime current="true"/>
</columns>
</table>
<table name="items_ordered">
<columns>
<int name="order_id" unsigned="true"/>
<int name="item_id" unsigned="true"/>
</columns>
<!-- TODO: could use a more succinct syntax? -->
<join column="order_id" link="orders.order_id" relationship="parent"/>
<join column="item_id" link="items.item_id" relationship="parent"/>
</table>
<table name="items">
<columns>
<int name="item_id" unsigned="true"/>
<string name="description" length="80"/>
</columns>
</table>
Anyway, we would obviously want to be able to retrieve the items on the order. This is where the multi-layer join comes in. We ask definition, "How do I get from orders to items?" The answer is:
orders.order_id -> items_ordered.order_id
items_ordered.item_id -> items.item_id
In the end, we'd have a Order.GetItems() method that would return the items on the order, right? Well, not exactly. What if items_ordered contained more data about each item ordered? For example, the quantity or the sale price. Not only common but almost essential. So I could almost see the need for a new object: ItemOrdered with properties for Quantity and SalePrice. But this isn't exactly what we want because an object must have a single object key.
Here are a number of options:
- Allow an object to be identified by two keys. This also opens the possibility for simply declaring the keys in the table and <object-key ...> will only refer to a table. This would also remove the need for <unique/>.
- Performing the two-layer join but also merging the Item object with the ItemOrdered object so that we can get all of its properties without another query. Or maybe return a dict or tuple containing the packed Item object and the extra ItemOrdered data? Maybe the ability to have ItemOrdered be a descendant of the Item object?
The answer will probably be a combination of the above ideas. I am just upset that I never thought of this situation before now.