title: Hibernate - distinct results with pagination
body: <p>This seems to be a well known problem for years as can be read here:
  <a href="http://blog.xebia.com/2008/12/11/sorting-and-pagination-with-hibernate-criteria-how-it-can-go-wrong-with-joins/" rel="nofollow noreferrer">http://blog.xebia.com/2008/12/11/sorting-and-pagination-with-hibernate-criteria-how-it-can-go-wrong-with-joins/</a></p>
  
  <p>And even finds reference in hibernate faqs:</p>
  
  <p><a href="https://community.jboss.org/wiki/HibernateFAQ-AdvancedProblems#Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword" rel="nofollow noreferrer">https://community.jboss.org/wiki/HibernateFAQ-AdvancedProblems#Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword</a></p>
  
  <p>This has also been discussed previously on SO</p>
  
  <p><a href="https://stackoverflow.com/questions/300491/how-to-get-distinct-results-in-hibernate-with-joins-and-row-based-limiting-pagi">How to get distinct results in hibernate with joins and row-based limiting (paging)?</a></p>
  
  <p>The problem is that even after going through all these resources, I have not been able to resolve my issue, which seems to be a little different from this standard problem, although I am not sure.</p>
  
  <p>The standard solution proposed here involves creating two queries, first one for getting distinct IDs and then using those in a higher level query to get the desired pagination. The hibernate classes in my case are something like</p>
  
  <pre><code>A
   - aId
   - Set&lt;B&gt;
  
  B
   - bId 
  </code></pre>
  
  <p>It appears to me that the subquery seems to be working fine for me and is being able to get the distinct aIds but the outer query which is supposed to do the pagination is again fetching the duplicates and thus the distinct in subquery is having no effect. </p>
  
  <p>Assuming I have one A object which has a set of four B objects, My analysis is that because of introduction of set, while fetching data for</p>
  
  <pre><code>session.createCriteria(A.class).list();
  </code></pre>
  
  <p>hibernate is populating four references in the list pointing to just one object. Because of this the standard solution is failing for me. </p>
  
  <p>Could someone please help in coming up with a solution for this case?</p>
  
  <p>Edit: I have decided to go for doing pagination by ourselves from the distinct resultset. The other equally bad way could have been to lazy load the B objects but that would have required separate queries for all the A objects to fetch corresponding B objects</p>