How do I write this JPQL query?

Question

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?

Answer 1

In JPQL, we can only write simple queries (...)

That's not true and JPQL does support [ LEFT [OUTER] | INNER ] JOIN. For Inner Joins, refer to the section 4.4.5.1 Inner Joins (Relationship Joins) of the specification:

4.4.5.1 Inner Joins (Relationship Joins)

The syntax for the inner join operation is

[ INNER ] JOIN join_association_path_expression [AS] identification_variable

For example, the query below joins over the relationship between customers and orders. This type of join typically equates to a join over a foreign key relationship in the database.

SELECT c FROM Customer c JOIN c.orders o WHERE c.status = 1

The keyword INNER may optionally be used:

SELECT c FROM Customer c INNER JOIN c.orders o WHERE c.status = 1

You just need to think association between entities.

Answer 2

Ok, this is the final answer. Took 1 hour to frame this 1 line. I got all sorts of strange errors during this 1 hour :p. But now, my concepts are clear enough

@NamedQuery(name = "BlogMembers.findBlogsOnWhichCommentsAreMade", query = "SELECT bm.blogId FROM BlogMembers bm Inner Join bm.blogId b Inner Join b.blogPostsList bp Inner Join bp.blogPostCommentList bpc INNER JOIN bpc.blogMembersId bmt WHERE bm.userId = :userId")