Filtering JPA search results for comment threads by votes

Question

I've concerns about the code below because I'm getting a lot of unnecessary data from my DB and I delete it afterward to only have the "good" data. I didn't find any good workaround to get the data directly from the query.

Basically what I have is like the Reddit comment section. There are comments (post entity) which have votes on them (postvotes entity). What I'm interested in having is the votes from the current user watching the thread so I can decide if they already upvoted or downvoted a post.

This is my original query that returns what I want:

private static final String FIND_POSTS_BY_THREAD_ID_WITH_VOTES = "SELECT p FROM Post p LEFT JOIN FETCH p.postVotes pv ON pv.user1=:currentUser WHERE p.thethread.idthread=:id AND p.replyTo IS NULL";

This query takes the posts (comments) that have a replyTo column to null. That means that those comments are not a reply to another comment. With this query I also have only the votes from the current user. Which is good. I have post in a thread which are not a reply to another comment, then I can use getReplies() from my Post entity to have the replies. However those replies will contain the wrong list of PostVotes. If you don't understand why I'm returning the comments that have a column replyTo null check the Reddit comment section, those posts (comments) are the ones on the outer left of each block.

Post Entity - Scroll down to see the function that I'm currently using to get the correct PostVotes:

@Entity
@Table(name = "posts")
@NamedQuery(name = "Post.findAll", query = "SELECT p FROM Post p")
public class Post implements Serializable, Comparable<Post> {
    private static final long serialVersionUID = 1L;

    @Id
    private int idpost;

    private String content;

    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "date_post")
    private Date datePost;

    private int downvotes;

    private int upvotes;

    // bi-directional many-to-one association to PostVote
    @OneToMany(mappedBy = "post")
    private List<PostVote> postVotes;

    // bi-directional many-to-one association to Post
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "reply_to_post")
    private Post replyTo;

    // bi-directional many-to-one association to Post
    @OneToMany(mappedBy = "replyTo")
    private List<Post> replies;

    // bi-directional many-to-one association to Thethread
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "threads_idthread")
    private Thethread thethread;

    // bi-directional many-to-one association to User
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "users_username")
    private User user;

   // -------------------- I don't like having to use this -----------------
   // ----------------------------------------------------------------

    public List<PostVote> getPostVote4User(User user) {
        this.getPostVotes().removeIf(
                v -> !v.getUser1().getUsername().equals(user.getUsername()));
        return this.getPostVotes();
    }
}

The method getPostVote4User() exists because I'm only Interested in the votes by the user watching the page! I said earlier that my query returns exactly that so why is this method needed? Yes my original query returns what I need, which is a list of 0 or 1 elements by the user when calling getPostVotes(). However if I access a votes from a post like this:

post.getReplies.get(0).getPostVotes()

The list will contain the votes from every user.

PostVote entity

@Entity
@Table(name = "post_votes")
@NamedQuery(name = "PostVote.findAll", query = "SELECT p FROM PostVote p")
public class PostVote implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "idpost_votes")
    private int idpostVotes;

    private int vote;

    // bi-directional many-to-one association to Post
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "posts_idpost")
    private Post post;

    // bi-directional many-to-one association to User
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "from_user")
    private User user1;

    // bi-directional many-to-one association to User
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "to_user")
    private User user2;
}

Answer 1

I made it work a better way. I think I know why it works but I'm not sure though. And it turns out it actually easy.

So what I wanted is to keep the Tree structure I had when I was querying like this:

private static final String FIND_POSTS_BY_THREAD_ID_WITH_VOTES = "SELECT p FROM Post p LEFT JOIN FETCH p.postVotes pv ON pv.user1=:currentUser WHERE p.thethread.idthread=:id AND p.replyTo IS NULL ";

Here all the post are the "parent" posts and the children post are get with the method .getReplies(). Since JPA would requery those, they wouldn't have the Filter on postVotes I put in the left join assossiation in the query. So I thought I had to query all the results with the correct postVotes and remake the three with the parent id and the setReplies() method. It turns out I don't have to. In other words :

When I query like this on the list I get from the query, I have only the vote by the current user ie: Post.getPostVotes().size() will be 0 or 1 (he voted or didn't). But this : Post.getReplies().get(0).getPostVotes().size() will be >= 0. Because the children are requeried.

So to fix that I query all the data with the correct votes like this:

private static final String FIND_POSTS_BY_THREAD_ID_WITH_VOTES = "SELECT p FROM Post p LEFT JOIN FETCH p.postVotes pv ON pv.user1=:currentUser WHERE p.thethread.idthread=:id ";

The "WHERE p.idpost IS NULL" is removed. Now when I access a child's votes like this : Post.getReplies().get(0).getPostVotes().size(), the size will be 0 or 1. That this method actually gives me the already filtered data even with FetchType.LAZY.

My guess is that since I already loaded the data JPA doesn't go ahead and reload it with the data I don't want. I ain't sure about this because of something I'll tell later.

So yeah after querying all the result I just deleted all the Post which are replies from the list :

List<Post> firstPosts = postLookup.findFirstPostsByThreadWithVotes(
        id, sessionBean.getUser());
firstPosts.removeIf(p -> p.getReplyTo() != null);

The weird thing is that if now I access a "child" of a post it will have only the votes by the current user:

firstPosts.get(0).getReplies().get(0).getPostVotes().size() will be either 0 or 1.

The thing that I don't understand is that if I load the data in two separates query it doesn't work anymore. Like this :

private static final String FIND_BY_ID = "SELECT p FROM Post p WHERE p.idpost=:id";
private static final String FIND_FIRST_POSTS_BY_THREAD_ID_WITH_VOTES = "SELECT p FROM Post p LEFT JOIN FETCH p.postVotes pv ON pv.user1=:currentUser WHERE p.thethread.idthread=:id IS NULL";

If I use those two queries and I try to access a child the list might be of size > 1:

 List<Post> firstPosts = postLookup.findFirstPostsByThreadWithVotes(
        id, sessionBean.getUser());
 //another list just to load the data. The method just correspond to the 2nd query above.
 List<Post> tempList = postLookup.findReplyPostsByThreadWithVotes(
        id, sessionBean.getUser())
 firstPosts.get(0).getReplies().get(0).getPostVotes().size()