File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"

 
Vladimir Razov
Ranch Hand
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using JPA, MySQL.
There are following entities:
Post:

Comment:

Then, there is a method in PostDAOBean:

Now,you can see that named query used in this method is SELECT p FROM Post p ORDER BY SIZE(p.comments) DESC.
And this is wrong, because function SIZE(some_collection) cannot be used to be ordered by it in JPQL.
I would like to get all posts from database ordered by number of comments.
How should JPQL query look then?
 
Vladimir Razov
Ranch Hand
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found out that another way to get all posts from database ordered by number of comments is by using JPA Criteria API.
This is what I am trying now:

I am aware I am missing getting the size of the set comments, but don't know how to add that part. Need help with Criteria API.
 
Vladimir Razov
Ranch Hand
Posts: 42
  • 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is valid code:

 
Rob Spoor
Sheriff
Pie
Posts: 20372
44
Chrome Eclipse IDE Java Windows
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why return null if you have no results? Now you have to check if the result is null before you can loop over it.
 
Vladimir Razov
Ranch Hand
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, you are right. Checking if resultList is empty is not needed. Thanks for pointing out.
 
Rob Spoor
Sheriff
Pie
Posts: 20372
44
Chrome Eclipse IDE Java Windows
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic