CODEWITHSUNDEEP
hibernatehql
Lawrence
Lawrence

Reputation: 446

HQL order by many to many collection property

I have a hibernate object called User which has a many-to-many relationship with another hibernate object called Post. The relationship is called likedPosts (from the User perspective). 

<set name="likedPosts" lazy="true" table="Likes" where="Deleted=0">
        <cache usage="read-write"/>
        <key column="UserID"/>
        <many-to-many column="PostID" class="Post"/>
    </set>

And currently I have a simple HQL query to return the list of liked posts as follows:

"select user.likedPosts from User user where user.id=:uid"

What I would like is to have this list ordered by a property in the Post object (specifically the post id). My (naive) attempt to do this was this:

"select user.likedPosts from User user where user.id=:uid 
        order by user.likedPosts.id desc"

This doesn't work though - I get an exception telling me I'm not allowed to do this. How would I go about doing this? Thanks!

Stack trace for error: 

org.hibernate.QueryException: illegal attempt to dereference collection [user0_.ID.likedPosts] with element property reference [id] [select user.likedPosts from com.pashash.domain.User user where user.id=:uid order by user.likedPosts.id desc]
    at org.hibernate.hql.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:46)
    at org.hibernate.hql.ast.tree.DotNode.checkLhsIsNotCollection(DotNode.java:513)
    at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:221)
    at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:94)
    at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:90)
    at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:728)
    ...

Upvotes: 3

Views: 1767

Answers (1)

JB Nizet
JB Nizet

Reputation: 691715

You need an explicit join to do that:

select post from User user
inner join user.likedPosts post
where user.id = :userId
order by post.id desc

Upvotes: 6

Related Questions