Hibernate version:
2.1.8

I want to implement a mapping for the following schema:

create table person {
id number(19,0) not null,
fname varchar(255) not null,
lname varchar(255) not null,
type varchar(255) not null,
primary key (id)
};

create table account {
id number(19,0) not null,
name varchar(255) not null,
primary key (id)
};

create table account_to_person {
person_id number(19,0) not null,
key varchar(255) not null,
account_id number(19,0) not null,
primary key (person_id, key, account_id)
};

alter table account_to_person add constraint FK1 foreign key (person_id) references person;
alter table account_to_person add constraint FK2 foreign key (account_id) references account;


Basically, I want a bidirectional many-to-many association, where a given key could have multiple rows. For example, if I have three persons (Joe and Jack the customers with ids 1 and 2, and Bob the account manager with id 3), and two accounts with ids 1 and 2, I want the following rows in account_to_person:

(1, "memberOf", 1)
(2, "memberOf", 1)
(3, "managerOf", 1)
(3, "managerOf", 2)

This says that Joe and Jack are members of account 1, and Bob is the manager of accounts 1 and 2.

What's the best way to map this? It's definitely not a bidirectional map, because there can be multiple values for each key, bidirectionally. Is it something like a bag with a composite key? Or what? I'm not even entirely sure what I want the Java side to look like....

Anyone know what I'm getting at and how to map it?
Cheers!
Rob

OK, so http://www.hibernate.org/118.html#A11 says to do it with a composite key mapping and a set.

What's the right kind of HQL to use to query this efficiently? Some people are having troubles with it, for example http://forum.hibernate.org/viewtopic.php?t=932971. If, for instance, I want to get all (and only) the "memberOf" persons for account 1, what HQL would I write?

What I really want is something like a Map that contains a Set. In other words, I'd like for Account to contain a Map, indexed by account_to_person.key, with values that are the Sets of all Persons that exist in the relationship table with that key. For example, in the case above, I'd like account 1 to have a Map containing "memberOf" and "managerOf" as keys; the value of account1.get("memberOf") would be a set containing persons 1 and 2, and the value of account1.get("managerOf") would be a set containing person 3.

I think this is very close to what http://forum.hibernate.org/viewtopic.php?t=933914 is asking for. I couldn't find any feature request for it in Hibernate2 *or* Hibernate3. So I made one: http://opensource.atlassian.com/projects/hibernate/browse/HHH-250

Until then, we'll hide the details of the relationship access behind getter methods, so we get the API we want (which is very much of the form "class Account { public Set getPersonsWithRelationship (String relationshipKey); ... }"). Be nice to have Hibernate do it, but until then....

Cheers!
Rob

Also note that an idbag is not what I want for this, because I don't want there to be duplicate rows in the relationship table (there can only be one "Z" relationship between account X and person Y, for all X, Y, and Z).

In fact, let me be fully clear about what I really want. In my Account mapping:



Which would map to the database schema above, and the following generated Java:



where the keys of relationshipsToPerson are the distinct values of account_to_person.key, and the values are sets composed of all persons with the given account_id and key.

Make sense? Really this is kind of expressing an attributed relationship as a multi-level collection of a sort....

Or is this just not Hibernate any more?

Cheers!
Rob

Going for an all-time self-reply-count record, but better that I answer my own questions than that someone else have to do it :-)

Here's how to query the composite relationship:
http://forum.hibernate.org/viewtopic.php?t=932534

We'll wrap this inside our own getters, but man, it'd sure be nice to avoid having to do the query ourself here... just THINK of all the optimizations Hibernate could do :-D

Cheers!
Rob

why not simply use a <list>?

At what level? Do you mean a list of relationships? i.e. do you mean this (from Account.hbm.xml)?



Can't see how you could have a list indexed by a string key. So this must not be what you mean. What *do* you mean?

It's astounding you have any time at all to read the forums, so I know that taking the time to clarify yourself is difficult. But even as a relatively experienced Hibernator, I still sometimes can't decipher your one-liners :-)

Thanks!
Cheers,
Rob

ok, so if its a String, use <map>. Same thing :)

ok so you're talking about http://www.hibernate.org/hib_docs/reference/en/html_single/#collections-ternary then. Alright, I'll try it. Thanks, and stay tuned ;-)
Cheers!
Rob

By the way, did you realize that "index-many-to-many" does not appear ANYWHERE in the text of Hibernate in Action?

I was a Manning EAP subscriber, and I just searched the whole PDF text for "index-many-to-many" with Acrobat, and got no hits at all :-)

Hibernate's so darn big you can't even cover it in a book! :-D
Cheers!
Rob

I don't think I'm talking about index-many-to-many ... im talking about <index>/<map-key>

For those following along at home, here's what you just said in JIRA:



We can't upgrade to Hibernate 3 just for this one new relationship. But we'll implement the API as above, and hide the HQL, so when we go to Hibernate 3 all we have to do is delete some of our code :-)

I'll update this topic then with the results of our experiments.

Why is it so damn hard to write clearly about database mapping? God knows I tried ;-) (I'm sure you know the feeling)

Cheers!
Rob[/quote]