Helma Logo
main list history

Many-to-many Relationships

This page shows how to implement many-to-many using the Helma 1 Object-Relational Mapping layer.

Let's assume the following scenario: each user should be able to subscribe to (ie. adopt) one or more custom HopObjects (e.g. news channels, weblogs and the like).

Ie. it must be possible to attach one or more custom HopObjects to each user object and, vice versa, to attach one ore more user objects to any custom HopObject.

HopObject1           HopObject2
  +-userObject1        +-userObject1
  +-userObject2        +-userObject3
  +-userObject3        +-userObject5
  +-...                +-...
  +-userObject[n]      +-userObject[n]

userObject1          userObject2
  +-HopObject1         +-HopObject1
  +-HopObject2         +-...
  +-...                +-HopObject[m]

We cannot simply map this special hierarchy between two HopObjects the way we are used to do with setting the appropriate type.properties for them. We are restricted to either map the user objects to one HopObject or the HopObjects to one user object (ie. building a one-to-one, resp. one-to-many relationship). That is due to the limitation of the relational table structure:

| user_id | hopobj_id |
|  1      |  1        |
|  2      |  1        |
|  3      |  1        |
|  1      |  2        |

Here the trouble starts: it is not possible to assign the same user_id to another hopobj_id inside the same table because either user_id or hopobj_id has to be a unique key.

However, we are on the right track and this table leads directly to the solution of the problem if we use it as "manager" for the other two tables (which are in consequences the user objects and HopObjects inside Helma). To do that, we simply add another column containing the missing unique key to the table above:

| id | user_id | hopobj_id |
| 1  |  1      |  1        |
| 2  |  2      |  1        |
| 3  |  3      |  1        |
| 4  |  1      |  2        |
| 5  |  3      |  2        |
| 6  |  5      |  2        |

In summary, we now have to deal with three tables: one containing the user objects, the second containing the custom HopObjects (which certainly have to be defined more precisely) and the third one above, mapping user objects and HopObjects to each other.

The steps to turn the relational tables into object-oriented representations accesible by Helma are quite easy. However, yet the general type-mapping syntax appears rather tricky (but better type-mapping is just around the corner).

Let's assume at this point that the HopObjects we deal with are constructed from a prototype i will refer to as Channel from now on. Ie. there is a directory called "Channel" in the application's directory as well as a database table called "db_channel" (certainly both could use the same name, but I make a difference here for transparency reasons).

The user objects are derived from the standard prototype user and reside in a database table called "db_user".

The "managing" object I will call Subscription from now on. Also here, we then assume that there is a prototype called "Subscription" and a db table called "db_subscription".

We start off with the user's type.properties:

_db = dbconnex
_table = db_user
_children = collection(Subscription)
_children.local = id
_children.foreign = user_id
_id = id
_name = name
name = name
password = password
email = email

Please note that you probably have to arrange the datasource and column names according to those in your tables (I have written them in italics).

As you might have noticed, this user prototype comes along with some more properties (name, password etc.), which is certainly no necessity, however very realistic.

For Channel we set-up a very basic type mapping:

_db = dbconnex
_table = db_channel
_id = id

Finally, we turn to the type.properties file for Subscription:

_db = dbconnex
_table = db_subscription
_id = id
channel = object(Channel)
channel.local = channel_id
channel.foreign = id
user = object(user)
user.local = user_id
user.foreign = id

Et voilà, with these settings n:m relationships should not be a big deal anymore.

Just be aware that when you want to execute a user subscription (ie. to add a Channel object to a user object in this case), you have to use the Subscription object:

 function subscribe(channel) {
   var subscr = new Subscription();
   subscr.user = user;
   subscr.channel = channel;