HQL - need help on query

Question

I have entities: Tweet and User.

Tweet has an author, which is an instance of User class. User is following others users and also keeps track of users following him:

@ManyToMany
public List<User> following;

@ManyToMany(mappedBy = "following", fetch = FetchType.LAZY)
public List<User> followedBy;

Now I'd like to load tweets of users that I'm currently following. I tried this:

"FROM Tweet AS tweet " +
                "JOIN tweet.author as author " +
                "JOIN author.followedBy as followedBy " +
                "WHERE tweet.author = followedBy AND ? in followedBy ORDER BY dateCreated DESC"

But it doesn't work. I suck in sophisticated queries.

EDIT

Generated query, which I copied from the exception which was thrown:

SELECT TWEET0_.ID AS ID10_, TWEET0_.AUTHOR_ID AS AUTHOR4_10_, TWEET0_.CONTENT AS CONTENT10_, TWEET0_.DATECREATED AS DATECREA3_10_ 
  FROM TWEET TWEET0_ 
  INNER JOIN USER USER1_ ON TWEET0_.AUTHOR_ID=USER1_.ID 
    WHERE USER1_.ID IN 
      (SELECT .[*] FROM USER USER2_, USER_USER FOLLOWING3_, USER USER4_ WHERE USER2_.ID=FOLLOWING3_.FOLLOWEDBY_ID AND FOLLOWING3_.FOLLOWING_ID=USER4_.ID AND USER2_.ID=?) 
ORDER BY TWEET0_.DATECREATED DESC LIMIT ?

Edit 2

Unfortunately, now I'm getting IllegalArgumentException:

    IllegalArgumentException occured : 
org.hibernate.QueryException: illegal attempt to dereference collection [user2_.id.following] with element property reference [id] [select tweet from models.Tweet tweet join tweet.author author where author in (select user.following.id from models.User user where user.id = :id) order by tweet.dateCreated desc]

Stevi Deter · Accepted Answer · 2011-06-09T18:48:07.597

1

I'd use a subquery here:

select tweet from Tweet tweet
join tweet.author author
where author in 
(select following.id from User user join user.following following where user.id = :id) 
order by tweet.dateCreated desc

edited Jun 09 '11 at 18:48

answered Jun 08 '11 at 19:03

Stevi Deter

1,653
12
16

unfortunately that doesn't work. I'm getting error that there is an error in sql statement :/ – jjczopek Jun 09 '11 at 15:11
what's the exact error? it usually displays the sql it has generated, which helps in determining what's wrong in the HQL. – Stevi Deter Jun 09 '11 at 17:27
I've modified the query to just return the user.following.id; that should return a usable list from the subselect instead of the select .[*] – Stevi Deter Jun 09 '11 at 17:56
@Stevi - I tested it, but now I'm getting IllegalArgumentException, I edited my question. – jjczopek Jun 09 '11 at 18:24
OK, I'm not 100% certain we can do joins in a subquery, but edited to try. If that doesn't work, I'd recommend doing two separate queries, fetch the follower ids separate and then pass into a simpler version of the query you started with. This is a case where not mapping the UserFollower as a separate entity makes the syntax a little more complex. – Stevi Deter Jun 09 '11 at 18:50

HQL - need help on query

EDIT

Edit 2

1 Answers1