java - Most efficient way to implement Liked/Disliked status retrieval in JPA/Hibernate?

Question

I'm currently implementing a doc with a like button like this:

The like button is associated with certain user account. When you press a like, it will stay liked for that user (similar to youtube video).

My entities and DTOs are below:

Doc.java:

@Entity(name = "Doc")
@Table(name = "doc")
@Data
public class Doc {
   //Unrelated code reacted for clarity
   @ManyToMany(cascade = {
            CascadeType.MERGE,
            CascadeType.PERSIST
   })
   @JoinTable(
            name = "doc_user_dislike",
            joinColumns = @JoinColumn(name = "doc_id"),
            inverseJoinColumns = @JoinColumn(name = "user_id")
    )
    private Set<UserWebsite> dislikedUsers;

    @ManyToMany(cascade = {
            CascadeType.MERGE,
            CascadeType.PERSIST
    })
    @JoinTable(
            name = "doc_user_like",
            joinColumns = @JoinColumn(name = "doc_id"),
            inverseJoinColumns = @JoinColumn(name = "user_id")
    )
    private Set<UserWebsite> likedUsers;
}

User.java:

@Entity
@Table(name = "user_website")
@Data
public class UserWebsite {
    //Unrelated code reacted for clarity
    @ManyToMany(mappedBy = "likedUsers")
    private Set<Doc> likedDocs;

    @ManyToMany(mappedBy = "dislikedUsers")
    private Set<Doc> dislikedDocs;
}

DocDetailsDTO.java (This will be sent to client).

@Data
public class DocDetailsDTO {
    private Long id;

    private Boolean isDisliked;

    private Boolean isLiked;
}

I'm having some solutions:

1. Add a field called isLiked to Doc.java with @Formular combine with @Transient and perform queries to DB.
2. Have another API which accept from Client a list of DocID, and a UserID, then return a list of DocID that UserID liked.
3. Check if UserID exist in likedUsers list (not very efficient, sometimes not feasible since I have to initialize that big lazy-loaded list).

The question is: What is the most efficient way to retrieve liked/disliked status for many post at once (>10 doc but max 100 doc per request) for about thousand users (1000 CCU) at once ? Are above solutions already optimal ?

Any help is appreciated. Thanks for your time reading through the question.

Answer 1

1. If I understand the problem correctly, this approach is not correct. You want to determine if a given user likes specified documents, so the formula would need a user id parameter, which you have no way to pass to the formula. Even if somehow @Formula could be used, it leads to N+1 problem (extra query per each document). Plus, you use managed entities which means extra dirty checking at the end.

2. This one is optimal in my opinion - one query, capable of using projection (no managed entities).

3. As you notice, this will kill your application and database. Plus, again you use managed entities which means extra dirty checking at the end. Definitely don't use this one.