Win a copy of Micro Frontends in Action this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

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

 
Ranch Hand
Posts: 42
  • 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
  • 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
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is valid code:

 
Sheriff
Posts: 22002
107
Eclipse IDE Spring VI Editor Chrome Java Ubuntu Windows
  • 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
  • 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
Posts: 22002
107
Eclipse IDE Spring VI Editor Chrome Java Ubuntu Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome.
 
expectation is the root of all heartache - shakespeare. tiny ad:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic