Say I have 5 tables,
tblBlogs tblBlogPosts tblBlogPostComment tblUser tblBlogMember
BlogId BlogPostsId BlogPostCommentId UserId BlogMemberId
BlogTitle BlogId CommentText FirstName UserId
PostTitle BlogPostsId BlogId
BlogMemberId
Now I want to retrieve only those blogs and posts for which blogMember has actually commented. So in short, how do I write this plain old SQL?
Select b.BlogTitle, bp.PostTitle, bpc.CommentText from tblBlogs b Inner join tblBlogPosts bp on b.BlogId = bp.BlogId Inner Join tblBlogPostComment bpc on bp.BlogPostsId = bpc.BlogPostsId Inner Join tblBlogMember bm On bpc.BlogMemberId = bm.BlogMemberId Where bm.UserId = 1;
As you can see, everything is Inner join, so only that row will be retrieved for which the user has commented on some post of some blog. So, suppose he/she has joined 3 blogs whose ids are 1,2,3 (The blogs which user has joined are in tblBlogMembers) but the user has only commented in blog 2 (of say BlogPostId = 1). So that row will be retrieved and 1,3 won't as it is Inner Join. How do I write this kind of query in JPQL?
In JPQL, we can only write simple queries like say:
Select bm.blogId from tblBlogMember Where bm.UserId = objUser;
Where objUser is supplied using:
em.find(User.class,1);
Thus once we get all blogs (here blogId represents a blog object) which user has joined, we can loop through and do all fancy things. But I don't want to fall in this looping business and write all this things in my Java code. Instead, I want to leave that for the database engine to do. So, how do I write the above plain SQL into JPQL? And what type of object will the JPQL query return? Because I am only selecting few fields from all table. In which class should I typecast the result to?
I think I posted my requirement correctly, if I am not clear please let me know.
UPDATE :- As per pascal's answer, I tried to write JPQL query for the above SQL query. I am facing a little problem. This query is working, but is incomplete:
SELECT bm.blogId FROM BlogMembers bm
Inner Join bm.blogId b
Inner Join b.blogPostsList bp
Inner Join bp.blogPostCommentList bpc
WHERE bm.userId = :userId
I want to modify this to:
SELECT bm.blogId FROM BlogMembers bm
Inner Join bm.blogId b
Inner Join b.blogPostsList bp
Inner Join bp.blogPostCommentList bpc
WHERE bpc.blogMembersId = bm.blogMembersId And bm.userId = :userId
But the above query is not working :(
How can it be fixed?