Top 5 records by size of collection

Question

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 i would like write query in jpql to get top5 Stock entity based on size of set users. So far i write native query in sql and it looks like:

SELECT s.ticker, COUNT(s.ticker)
FROM t_stock s INNER JOIN t_user_stocks us ON s.id = us.stock_id 
INNER JOIN t_user u on us.user_id = u.id GROUP BY s.ticker ORDER BY count DESC

And i want a jqpl query which return top 5 stocks entity. Could someone help me?

Answer 1

Assuming your entity is mapped as 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 the following using native SQL to get the result.If you insist on using JPQL, the answer here is your friend.

public interface StockRepository extends JpaRepository<Stock, Integer> {

    @Query(value = "SELECT  s.ticker, COUNT(s.ticker) FROM stock s INNER JOIN "
            + "stock_user us ON s.id = us.stock_id  INNER JOIN user u on us.user_id = u.id GROUP BY s.ticker order by count(*) desc limit 1", nativeQuery = true)
    public List<Object[]> findStock();
}