This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes EJB and other Java EE Technologies and the fly likes Looking for alternative way of JPQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"" Watch "Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"" New topic
Forums: EJB and other Java EE Technologies JPA Certification (OCEJPA)
Author

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

Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
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

Joined: Jul 22, 2013
Posts: 42
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

Joined: Jul 22, 2013
Posts: 42
This is valid code:

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

Why return null if you have no results? Now you have to check if the result is null before you can loop over it.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Vladimir Razov
Ranch Hand

Joined: Jul 22, 2013
Posts: 42
Yes, you are right. Checking if resultList is empty is not needed. Thanks for pointing out.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19672
    
  18

You're welcome.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Looking for alternative way of JPQL "ORDER BY SIZE(some_collection)"