Hibernate/Gorm query with distinct results and JOIN on non primary key
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
def criteria = Employee.createCriteria()
criteria.list(offset: offset, max: max) { ... }
I have the following domain classes (shorthand)
class Employee {
static hasMany = [
jobs: EmployeeJob,
]
}
class EmployeeJob {
Employee employee
Company company
}
class Company {
static hasMany = [
addresses: Address,
]
}
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.
Before this ticket I had not needed to bring back the Companies associated with the Employee but now that is a requirement.
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.
In my from clause before the constraint I had
fromClause += "left join e.jobs as jobs left join jobs.company as company"
But I need to join on a condition that is not tied to the Company primary key
"left join e.jobs as jobs left join jobs.company as company with company.scope = :scope"
I believe I can't join on anything other than the primary key
Hibernate ticket
org.hibernate.hql.internal.ast.InvalidWithClauseException
with clause can only reference columns in the driving table
Related SO Post
I have tried adding things to the original criteria query
resultTransformer Criteria.DISTINCT_ROOT_ENTITY
That did remove the duplicates but the pagedResults.getTotalCount() still included the duplicates. Per another SO user:
When using ResultTransformer, hibernate does not include DISTINCT in SQL query, so we fall in troubles with paging (limit/offset)
I have tried:
Projections.groupProperty("id")
but then the results only contain the id.
And I have tried:
projections {
sqlGroupProjection '...', '..', [...], [...]
}
and I believe it only brought back the column I was grouping by after it got the syntax down.
The Hibernate version is 4.3.8.1.
Are there methods of doing this I am missing or does one of these seem viable?
It seems to me that I can't find any other methods of doing this other than dropping down and writing raw SQL.
You can do something like below,with HQL
def query = "select distinct(e) from Employee e " +
"left join e.jobs j " +
"left join Company c on c.id = j.company.id and c.scope = :scope"
Employee.executeQuery(query, args)
Note: join e.jobs will end up in two joins . I would rather make that reference table by myself.
上一篇: 如何提高Haskell程序的性能？