Post entity:

public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @OneToMany(mappedBy = "post")
    private List<PostComment> postComments;

    ...
}

PostComment entity:

public class PostComment {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @ManyToOne
    @JoinColumn(name = "post_id")
    private Post post;
    
    @OneToMany(mappedBy = "postComment")
    private Set<PostCommentUpvote> postCommentUpvotes;

    @OneToMany(mappedBy = "postComment")
    private Set<PostCommentDownvote> postCommentDownvotes;

    ...
}

PostCommentUpvote entity (PostCommentUpvote and PostCommentDownvote have the exact same fields - these entities act like counters)

public class PostCommentUpvote {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @ManyToOne
    @JoinColumn(name = "post_comment_id")
    private PostComment postComment;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;

    ...
}

All relations are bi-directional as you can see from the annotations.

The goal: When a user (authenticated) upvotes/downvotes a PostComment I want to do the following:

• Check if user already upvoted/downvoted the PostComment.

For this I have Post id (even though this is not needed) and PostComment id and both are indexed.

There are three possible 'states' when User up/downvotes the comment:

1. User hasn't yet up/downvoted that comment, so it is either new upvote or new downvote
2. User has already upvoted and if he upvotes again, it will remove the upvote (same with downvote)
3. User has already upvoted and he when he downvotes, upvote is removed and new downvote is added (and vice-versa)

What would be the most optimal way of doing this? Get the PostComment by its id and then loop through the List of PostCommentUpvote/PostCommentDownvote and check the User on every iteration? Or perform a tactical SQL request, which must be faster than looping in Java? If so, what would this SQL query look like? Or any other approach to make this performant. I am open to any suggestion. Thanks

CodePudding user response：

Assuming you have the post comment id and user id, the following JPA query (or close to it) will return true if the user has upvoted on the post comment and false otherwise:

select case when count(postCommentUpvote) > 0 then 'true' else 'false'
from PostCommentUpvote postCommentUpvote
join postCommentUpvote.postComment postCommnent
where postComment.id = :postCommentId
and user.id = :userId

You would then have to perform the same query using the PostCommentDownVote entity. An alternative would be to remove the up and down vote entities, simply create a PostCommentVote entity which has a boolean attribute that indicates up or down, and helper methods isUpvote() and isDownVote() that would interpret the boolean for you. You could get everything you need with a single query that returns a PostCommentVote if the user has up or down voted and null otherwise.

CodePudding user response：

You did not indicate what you want to do if the user has already commented on the post; ignore the request or update the PostComment. Either way the most optimal way of doing this would be not checking at all. Create a unique index on (user_id, post_comment_id) or drop the the id column and make a composite PK of those columns. Then just insert without checking. Use the On Conflict to either ignore or update the request. You may also want to add an Up/Down vote indicator column.