java - Top 5 records by size of collection -

i have following entity:

public final class stock implements serializable {      @jsonignore     @manytomany(mappedby = "stocks", fetch = fetchtype.lazy)     private set<user> users = new hashset<>();      [other fileds]      [getters/setters] } 

and write query in jpql top5 stock entity based on size of set users. far write native query in sql , looks like:

select s.ticker, count(s.ticker) t_stock s inner join t_user_stocks on s.id = us.stock_id  inner join t_user u on us.user_id = u.id group s.ticker order count desc 

and want jqpl query return top 5 stocks entity. me?

assuming entity mapped follows.

@entity public class stock {      @id     @generatedvalue(strategy = generationtype.auto)     private long id;      @column     private string ticker;      @jsonignore     @manytomany(fetch = fetchtype.lazy, cascade = cascadetype.all)     @jointable(name = "stock_user", joincolumns = { @joincolumn(name = "stock_id", nullable = false, updatable = false) }, inversejoincolumns = { @joincolumn(name = "user_id", nullable = false, updatable = false) })     private set<user> users = new hashset<user>();   } 

i did following using native sql result.if insist on using jpql, answer here friend.

public interface stockrepository extends jparepository<stock, integer> {      @query(value = "select  s.ticker, count(s.ticker) stock s inner join "             + "stock_user on s.id = us.stock_id  inner join user u on us.user_id = u.id group s.ticker order count(*) desc limit 1", nativequery = true)     public list<object[]> findstock(); }