CODEWITHSUNDEEP
javahibernatejpaplayframework
i.am.michiel
i.am.michiel

Reputation: 10404

How to get the most occured set of objects?

I have two objects. 

@Entity
class Person extends Model {

    ...
    @OneToOne
    Category category;
}


@Entity 
class Category extends Model {
    ...
}

I need to get the 5 most used categories. How can I do that ?

Thanks,

EDIT : Solution

List<Object[]> c = Category.find(
    "SELECT p.categorie, count(p.id) FROM Person p " +
    "GROUP BY p.category ORDER BY count(p.category) DESC").fetch(2);

Upvotes: 0

Views: 87

Answers (2)

Sean Patrick Floyd
Sean Patrick Floyd

Reputation: 299048

Your JPQL query would be something like this:

SELECT p.category, COUNT(p.category)
FROM Person p
GROUP BY p.category
ORDER BY count(p.category) DESC

And you'd do query.setMaxResults(5) also.

Upvotes: 2

JB Nizet
JB Nizet

Reputation: 691973

select category.id, count(person.id) 
from Person person 
inner join person.category category
group by category.id
order by count(person.id) desc

And before executing this query, call setMaxResults(5) on the Query object.

Upvotes: 1

Related Questions