Three-way hibernate ORM mapping - how to?

Question

I have a three SQL tables:

create table users (
  id serial primary key,
  name text not null unique
);

create table posts (
  id serial primary key,
  data text not null,
  authorId integer not null references users (id)
);

create table ratings (
  id serial primary key,
  name text not null unique
);

One post can have only one author, so users <-> posts relation is already established in normal form (correct me if i am wrong).

Ratings are pre-defined constants like "bad", "good" or "awesome", with (in real case) additional data as rating value, description or other fields i omitted here for brevity.

Next i want to relate ratings to users and posts. Each post may be rated once by each user, and may be rated by multiple users. I came up with the following relation:

create table posts_ratings_users_map (
  postId integer not null references posts (id),
  ratingId integer not null references ratings (id),
  userId integer not null references users (id),
  primary key (postId, ratingId, userId)
);

But here is the problem: i can't see a way to integrate it within Hibernate ORM mapping, to get for each of posts list (or set, or any other collection) of pairs of (User,Rating).

Here is how i am trying to map them now:

User.java:

@Entity(name = "users")
public class User {
    @Id
    @Column(nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name;

    @OneToMany
    @JoinColumn(name = "authorId")
    private Set<Post> posts = new HashSet<>();

    // ...
    // getters and setters
    // ...
}

Rating.java:

@Entity(name = "ratings")
public class Rating {
    @Id
    @Column(nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name;

    @ManyToMany(mappedBy = "ratings")
    private Set<Post> posts = new HashSet<>();

    // ...
    // getters and setters
    // ...
}

Post.java:

@Entity(name = "posts")
public class Post {
    @Id
    @Column(nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String data;

    @ManyToOne
    @JoinColumn(name = "authorId")
    private User author;

    @ManyToMany
    @JoinTable(
            name = "posts_ratings_users_map",
            joinColumns = { @JoinColumn(name = "ratingId") },
            inverseJoinColumns = { @JoinColumn(name = "postId") }
    )
    private Set<Rating> ratings = new HashSet<>(); // here is the problem. i can relate ratings to this post, but how
                                                   // do i relate them with users which assigned their ratings to this
                                                   // post ?


    // ...
    // getters and setters
    // ...
}

What needs to be changed in order to relate list of pairs of rating&user to each post?

UPD1

Obvious error: PK for posts_ratings_users_map should be (postId, userId) (excluding ratingId), otherwise same user was able to put different ratings on the same post.

I say you need another table. `user_post_rating` with user_id, post_id and rating_id columns and all there as a combination primary key. This table will store data on which user gave which rating to which post. — Subin Sebastian, Jan 03 '13 at 13:23

score 2 · Accepted Answer · edited May 23 '17 at 12:27

Maybe change your model a little.

Users have posts which you said is already defined.

Why not create a new Entity 'UserRating'.

@Entity(name = "user_ratings")
public class UserRating {
    @Id
    @Column(nullable = false)
    @GeneratedValue
    private Integer id;

    @ManyToOne(nullable = false)
    @JoinColumn(name = "ratingId")
    private Rating rating;

    @ManyToOne(nullable = false)
    @JoinColumn(name = "authorId")
    private User ratedBy;
 }

Now on your Post instead of the ManyToMany have a OneToMany relationship. It would be more ideal to use the id of the rating and the user as the key for the UserRating class but this doesnt model easily in JPA/Hibernate which makes it quite complex. If your interested have a look at these questions

Mapping ManyToMany with composite Primary key and Annotation

Many to many hibernate mapping with extra columns?

This is exactly how I would do it, too. You get something very naturally understandable by introducing a UserRating - even managers would understand that :-) — sorencito, Jan 03 '13 at 18:21

score 0 · Answer 2 · answered Jan 03 '13 at 13:22

0

Include the user in the rating class

@Entity(name = "ratings")
public class Rating {
@Id
@Column(nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

@Column(nullable = false)
private String name;

@ManyToMany(mappedBy = "ratings")
private Set<Post> posts = new HashSet<>();

@Onetoone
private User ratedBy;

// ...
// getters and setters
// ...
}

answered Jan 03 '13 at 13:22

shazinltc

3,616
7
34
49

rating table is just a predefined set of ratings like GOOD, BAD, EXCELLENT. you will need a mapping table with rating_id, post_id and user_id to store real info. – Subin Sebastian Jan 03 '13 at 13:23
@shazinltc Sorry, but unfortunately this won't work out that simple. Just because this annotation won't provide a hint in which table perform one-to-one (also, it is case-sensitive, `@OneToOne`) relation, nor by which column, nor how at all. – Alexander Tumin Jan 03 '13 at 16:51
I find this not very intuitive. – sorencito Jan 03 '13 at 18:22

Three-way hibernate ORM mapping - how to?

2 Answers2