0

When I have a Criteria request from a root element (Company for example) with a fetchMode JOIN and an Alias LEFT_OUTER_JOIN to a collection (Employees for example), I don't manage to get distinct Companies paginated (with MaxResults and firstElement).

criteria = sessionFactory.getCurrentSession().createCriteria(Company.class);

criteria.createAlias("employees", "employees", JoinType.LEFT_OUTER_JOIN);
criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
criteria.setFetchMode("employees", FetchMode.JOIN);

criteria.setFirstResult((pageNb - 1) * nbPerPage);
criteria.setMaxResults(nbPerPage);

If I ask for the firsts 20 companies, I only have 3 because the first of the results has 18 employees.

I would like to have the firsts 20 as asked but with the employees loaded to optimised the lazy loading.

Jérôme R
  • 1
  • 1

1 Answers1

1

I found this post that has a similar situation. When you limit your results in a criteria, hibernate will apply the limit in the SQL query, and when you work with joins/distinct root entity, in many cases, it will result in a smaller result list. The results you reported confirms this.
The workaround suggested is to change the fetch mode for your alias to SELECT. The result is that more than one query will be called, but surely will be less costly than lazy loading for each element.

Vitor Santos
  • 581
  • 4
  • 15
  • It doesn't change anything. The results are the same even with the SELECT. The fact is that I don't use the _limit_ method but the _setMaxResults_ and the _setFirstResult_. – Jérôme R Feb 01 '19 at 10:22