I'm trying to set-up a hibernate mapping that will load a set of "relationships" between "users", the complication arises due to the way that the table represents this information. I've tried a few permutations and I'm not having much luck so any pointers on this would be very appreciated.

Here are the tables in question, Nb: This is an overly simplified version:

Table users (
id (pk)
name
)

insert into users values (1, 'ABC corp');
insert into users values (2, 'John Smith');
insert into users values (3, 'Mary Jones');

Table relationships (
id (pk)
user_id (fk back to user)
related_user_id (fk back to user)
)

insert into relationships values (4, 1, 2);
insert into relationships values (5, 2, 3);
insert into relationships values (6, 3, 1);

So what I'd ilke to see is that when I load "John Smith" that it will load the relationships where either the user_id or the related_user_id is "John Smith".

According to the documentation I should consider something like:

<set name="relationships" inverse="true" cascade="all-delete-orphan">
<key column="user_id"/>
<one-to-many class="Relationship"/>
<loader query-ref="loadRelationships"/>
</set>

<sql-query name="loadRelationships">
<load-collection alias="r" role="User.relationships"/>
SELECT
{r.*}
FROM
RELATIONSHIP r
WHERE
(r.USER_ID = :id) OR
(r.RELATED_USER_ID = :id)
</sql-query>

However the <key column="user_id"/> effectively "filters" out the related_user_id records so I only end up with "one side" of the data. Which isn't correct.

What would you suggest to solve this? Ps: I want to keep this as transparent as possible I.e. I don't want to make any changes outside of the mapping files.

Thanks.
-Q

You could have two collections in the User class, one for outgoing relationships and one for incoming. Then if you must have them combined you could add a property that simply merges the collections.

HI,

Thanks for your response. That's definitely a solution that would work, but its not all that pretty, plus it means possibly changing the model class which is something I'm reluctant to do.

Does anyone else have any possible suggestions/solutions?