Ordering the results of a Hibernate Criteria query by using information of the child entities of the asked entities

Question

I have got two entities Person and Book. Only one instance of a specific book is stored to the system (When a book is added, application checks if that book is already found before adding a new row to the database). Relevant source code of the entities is can be found below:

@Entity
@Table(name="persons")
@SequenceGenerator(name="id_sequence", sequenceName="hibernate_sequence")
public class Person extends BaseModel
{
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_sequence")
    private Long id = null;

    @ManyToMany(targetEntity=Book.class)
    @JoinTable(name="persons_books", joinColumns = @JoinColumn( name="person_id"), inverseJoinColumns = @JoinColumn( name="book_id"))
    private List<Book> ownedBooks = new ArrayList<Book>();
}

@Entity
@Table(name="books")
@SequenceGenerator(name="id_sequence", sequenceName="hibernate_sequence")
public class Book extends BaseModel
{
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_sequence")
    private Long id = null;

    @Column(name="name")
    private String name = null;
}

My problem is that I want to find persons, which are owning some of the books owned by a specific person. The returned list of persons should be ordered by using following logic: The person owning most of the same books should be at the first of the list, second person of the the list does not own as many books as the first person, but more than the third person. The code of the method performing this query is added below:

@Override
public List<Person> searchPersonsWithSimilarBooks(Long[] bookIds) {
    Criteria similarPersonCriteria = this.getSession().createCriteria(Person.class);
    similarPersonCriteria.add(Restrictions.in("ownedBooks.id", bookIds));

    //How to set the ordering?
    similarPersonCriteria.addOrder(null);

    return similarPersonCriteria.list();
}

My question is that can this be done by using Hibernate? And if so, how it can be done? I know I could implement a Comparator, but I would prefer using Hibernate to solve this problem.

When using SQL the result I want can be achieved by using following SQL query:

select p.name, count(p.name) as bookCount from persons p, books b, person_book pb
where pb.person_id = p.id and pb.book_id = b.id and b.id in (1,2,3,4,5,6) group by
name order by bookCount desc 

I have been trying to figure out how to translate this into the Criteria query. I have been trying to use Projections, but i seem to be unable to map the results back to the Person object.

Answer 1

Is this of any use?

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-ordering

Answer 2

I finally managed to solve this problem. The following method works as expected:

@Override
public List<Person> searchPersonsWithSimilarBooks(Long[] bookIds) {
    Criteria similarPersonCriteria = this.getSession().createCriteria(Person.class, "p");

    Criteria bookCriteria = similarPersonCriteria.createCriteria("ownedBooks", "b");
    bookCriteria.add(Restrictions.in("b.id", bookIds));

    similarPersonCriteria.setProjection(Projections.projectionList()
            .add(Projections.groupProperty("p.id"), "id")
            .add(Projections.rowCount(), "similarBookCount"));

    similarPersonCriteria.addOrder(Order.desc("similarBookCount"));
    similarPersonCriteria.setResultTransformer(new AliasToBeanResultTransformer(Person.class));

    return similarPersonCriteria.list();
}

I also updated my person class by adding a transient property called similarBookCount, which can be useful in some situations.