@ManyToMany relationship to store ids only, without any extra columns and references to objects-CodePudding

I am looking for the best mapping design for this use case:

All I need to do is store foreign key references in a @ManyToMany relationship, but in the fetch, I do not care about objects, only id references. I have a User and a Restaurant relationship: Users can "like" the restaurants, and Restaurants can be "liked" by users. In my front-end, I need to provide users the ability to store their likes and fetch them later.

This is what I've come to so far:

@Entity
User

    @JsonIgnore
    @ManyToMany
    @JoinTable(name = "user_favorite_restaurants",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name= "restaurant_id", referencedColumnName = "id"))
    public Set<Restaurant> userFavoriteRestaurants = new LinkedHashSet<>();

    @ElementCollection
    @CollectionTable(name="user_favorite_restaurants",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"))
    @Column(name="restaurant_id")
    public Set<Long> restaurantIds;

@Entity
Restaurant

   @JsonIgnore
   @ManyToMany(mappedBy = "userFavoriteRestaurants")
   public Set<User> usersFavorite;

   @ElementCollection
   @CollectionTable(name="user_favorite_restaurants",
           joinColumns = @JoinColumn(name = "restaurant_id", referencedColumnName = "id"))
   @Column(name="user_id")
   public Set<Long> userIds;

Table userFavoriteRestaurants is a simple two-column table with user_id and restaurant_id.

This does NOT work. userFavouriteRestaurants has a duplicate ManyToMany intersection table user_favorite_restaurants.

Is this the way to go, or am I shooting myself in the foot down the line? Should I just create an entity UserRestaurant and create a table with PK, or is this a good path to the solution?

How do I return only ids, in an optimized way?

Bonus: Is it logical to assume this relationship will be expanded over time and so that UserRestaurant Entity is needed sooner before later?

CodePudding user response：

Actually, the @ElementCollection fields in both entities seem unnecessary to me. Just use:

@Entity
public class User {
    private Long id;

    @JsonIgnore
    @ManyToMany
    @JoinTable(name = "user_favorite_restaurants",
        joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name= "restaurant_id", referencedColumnName = "id"))
    public Set<Restaurant> userFavoriteRestaurants = new LinkedHashSet<>();
}

@Entity
public class Restaurant {
    private Long id;

   @JsonIgnore
   @ManyToMany(mappedBy = "userFavoriteRestaurants")
   public Set<User> usersFavorite;
}

The @ManyToMany mapping defined in the User entity will create a junction table looking like:

user_id | restaurant_id
...     | ...

That is, Hibernate/JPA will create this junction table under the hood to automatically store the relationships between id values from User and id values from Restaurant. You do not need to manage this yourself.