OneToMany relation with multiple join columns

Question

I have this entity Friendship:

@ManyToOne
private User user1;
@ManyToOne
private User user2;
... and other properties of the friendship

Per friendship there is only one instance/record of this entity (not 2), so there can't be any issues where John is a friend of Jane but not the other way around. The entries are also normalized so the member of the friendship with the lowest ID is user1, and the other member is user2, meaning I can prevent duplicatie entries with a simple unique constraint.

To get the friendships of a certain user I perform a query with

WHERE user1 = :me OR user2 = :me.

Is it possible to map that onto a @OneToMany Set<Friendship> property of User?

(Friendship has other properties, so this is not simply @ManyToMany Set<User> friends)

Answer 1

The short answer is no. For a bi-directional fetch, I believe the only way to do what you're asking is with a named query. Something along the lines of:

@Entity
@NamedQuery(name="User.friendships", query="
    select u 
    from User u
    where u in (
        select f.User1
        from Friendship f
        where f.User2.id = :userId
    ) or u in (
        select f.User2
        from Friendship f
        where f.user1.id = :userId
)")
public class User {

private Set<User> friends = new HashSet<User>()

public Collection<User> getFriendships(Session s) {
    return s.getNamedQuery("User.friendships")
        .setParameter("userId", this.id)
        .list();
}

}

The way I've done this in the past is to de-normalize the join table in some fashion, whether through duplicate columns (mapping an association in each direction), or duplicate rows. Either would allow you to simplify your fetch.

Answer 2

Yes it is possible. But why you need @OneToMany? You can use @ManyToMany. It will be same. In @ManyToMany annotation you can specify table where pairs of ids user1 - user2 will be stored. Then just do query to this table.