I've done research on how to make recursive relationships and I already know how to do them, but I can't avoid redundancy in this type of relationship. I have a User class that has a "friends" attribute. The User can be friends with many other Users, and other Users can be friends with one. So I did the following:

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "friends",
    joinColumns = @JoinColumn(name = "user1_id"),
    inverseJoinColumns = @JoinColumn(name = "user2_id"))
private List<User> friends = new ArrayList<User>();

To enter the data, I use the following service function.

public void addFriend(User requester, User requested) throws DataNotFoundException{
    requester.getFriends().add(requested);
    requested.getFriends().add(requester);

    update(requested);
}

Every time I update the "requested", it fetches the entity that relates to it "requester" and updates it as well. Initially I thought it was a Cascade problem, but I saw that Cascade default is disabled, and all options are different ways to propagate operations to children.

In the end this creates 2 rows in my table with redundant data

user1_id | user2_id
    1         2
    2         1

I want to create just one line that is used by both sides of the relationship.

I've thought about creating a custom query for this, but it doesn't seem like the most appropriate solution from a design point of view. Because it would create a snowball of customizations that I would need to do.

Edit: When I remove getFriends().add() from either side the result is the same. The code also seems to be redundant as I'm trying this kind of relationship for the first time, and I did it the same way I would with other bidirectional many-to-many relationships

CodePudding user response：

I think that this behavior is due to the underlaying datamodel.

Every User can have 0...n friends (again Users). Each time a User can either be the entity who has friends or who is a friend of someone else. Each User who has friends is stored in user1_id and who is a friend in user2_id.

When JPA fetches the user it always uses the same query to fetch the friends, something like this:

select u2.* from user u
left join friends f on f.user1_id = u.id
left join user u2 on f.user2_id = u2.id
where u.id = ?;

JPA can not distinguish if the joins should be switched - so it is needed to insert the "dublicated" rows.

CodePudding user response：

The important question is: what is the problem in the duplication?

I'd say the current variant is the default correct way to model this.

Alternatives could be:

1. On writing, fill only one direction, on the database side use a trigger for adding the reverse direction. This avoids transferring the data twice to the database. The reverse direction will only be visible once the entity in question got reloaded, which normally means, only when you use a new session.

2. On writing, fill only one direction. Map the relationship to a view which does a union all on the underlying table with the two user ids switched. You'll need insert and delete triggers on that view. Effects on your application are the same as variant 1.

3. You could model Friendship as its own entity having a Set<User> which is fixed to having two entries all the time.

4. If this relationship is important for your application using a specialised database like Neo4j could be worthwhile. It comes with its own Spring Data Neo4J.

CodePudding user response：

By adding each to each other's friend list, it isn't redundant at all.

What you've mapped is a unidirectional relationship. I know my friends, but my friends don't know I consider them my friends. It is just more confusing because it is a self reference (Users->Users). Try to look at it as separate objects (parent->Children) and it will be easier to understand what you've got and what you may want instead if this isn't it.

While what you want seems correct - I'm your friend means you are mine - it isn't accurate. I can be friends with people who do not consider me their friend - it really is a one way relationship. Even your terminology of a requester and requested means there is a side to the relationship that makes it more a follow/followee type situation. Your database, and the nature of foreign keys, reflect that. If you got what you are asking for, the relational table "friends" will have one user in "user1_id" and presumably the requested's ID in "user2_id". When you go looking for the Requested's list of friends (assuming they have an id of '2'), the requested would not show up - the DB will search the "user1_id" values for '2' and find nothing, making it appear as if the Requested had no friends.

Unless you add the requested.getFriends().add(requester); they really don't have friends.

So the question becomes what does this relationship mean to your model. If you don't want to automatically force Johnny to be Billy's friend just because Billy has Johnny on his friend list, but still need a way to determine who considers Johnny their friend, create a bidirectional relationship:

@ManyToMany(mappedby="friends", fetch = FetchType.LAZY)
private List<User> friendedBy = new ArrayList<User>();

Your method then becomes:

public void addFriend(User requester, User requested) throws DataNotFoundException{
    requester.getFriends().add(requested);
    requested.getFriendedBy().add(requester);

    update(requested);
}

This gives you a way to traverse the relationship both ways, and let the 'requested' decide later on if they really want to add the requestor to their own friend list instead of it being automatic.