title: Hibernate/Gorm query with distinct results and JOIN on non primary key
body: <p>I am trying to solve an issue where I am rewriting a query to remove duplicates and add criteria that is currently constructed via criteria.list</p>
  
  <pre><code>def criteria = Employee.createCriteria()
  criteria.list(offset: offset, max: max) { ... }
  </code></pre>
  
  <p>I have the following domain classes (shorthand)</p>
  
  <pre><code>class Employee {
      static hasMany = [
        jobs: EmployeeJob,
      ]
  }
  
  class EmployeeJob {
      Employee employee
      Company company
  }
  
  class Company {
      static hasMany = [
        addresses: Address,
      ]
  }
  </code></pre>
  
  <p>I allow the user to enter text for the Company address so if an Employee is tied to two addresses with the same county the query will bring back two rows.</p>
  
  <p>Before this ticket I had not needed to bring back the Companies associated with the Employee but now that is a requirement.</p>
  
  <p>I attempted to rewrite the query in HQL as that is what was done in the past to eliminate duplicates but yet still be able to easily paginate.  Things were going well until I went to add in a final constraint on the Company join.</p>
  
  <p>In my from clause before the constraint I had</p>
  
  <pre><code>fromClause += "left join e.jobs as jobs left join jobs.company as company"
  </code></pre>
  
  <p>But I need to join on a condition that is not tied to the Company primary key</p>
  
  <pre><code>"left join e.jobs as jobs left join jobs.company as company with company.scope = :scope"
  </code></pre>
  
  <p>I believe I can't join on anything other than the primary key</p>
  
  <p><a href="https://hibernate.atlassian.net/browse/HHH-2772" rel="nofollow noreferrer">Hibernate ticket</a></p>
  
  <pre><code>org.hibernate.hql.internal.ast.InvalidWithClauseException 
  with clause can only reference columns in the driving table
  </code></pre>
  
  <p><a href="https://stackoverflow.com/questions/26027015/getting-with-clause-can-only-reference-columns-in-the-driving-table-when-tryin">Related SO Post</a></p>
  
  <p>I have tried adding things to the original criteria query</p>
  
  <pre><code>resultTransformer Criteria.DISTINCT_ROOT_ENTITY
  </code></pre>
  
  <p>That did remove the duplicates but the pagedResults.getTotalCount() still included the duplicates. Per another SO user:</p>
  
  <blockquote>
    <p>When using ResultTransformer, hibernate does not include DISTINCT in SQL query, so we fall in troubles with paging (limit/offset)</p>
  </blockquote>
  
  <p>I have tried:</p>
  
  <pre><code>Projections.groupProperty("id")
  </code></pre>
  
  <p>but then the results only contain the id.</p>
  
  <p>And I have tried:</p>
  
  <pre><code>    projections {
          sqlGroupProjection '...', '..', [...], [...]
      }
  </code></pre>
  
  <p>and I believe it only brought back the column I was grouping by after it got the syntax down.</p>
  
  <p>The Hibernate version is 4.3.8.1.</p>
  
  <p>Are there methods of doing this I am missing or does one of these seem viable?  </p>
  
  <p>It seems to me that I can't find any other methods of doing this other than dropping down and writing raw SQL.</p>