Using: Hibernate version is 4.3.8.1 and Grails/Gorm
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
Code:
def criteria = Employee.createCriteria()
criteria.list(offset: offset, max: max) { ... }
I have the following domain classes
Code:
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
Code:
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
Code:
"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
https://hibernate.atlassian.net/browse/HHH-2772Code:
org.hibernate.hql.internal.ast.InvalidWithClauseException
with clause can only reference columns in the driving table
https://stackoverflow.com/questions/26027015/getting-with-clause-can-only-reference-columns-in-the-driving-table-when-tryinI have tried adding things to the original criteria query
Code:
resultTransformer Criteria.DISTINCT_ROOT_ENTITY
That did remove the duplicates but the pagedResults.getTotalCount() still included the duplicates. Per another SO user:
Quote:
When using ResultTransformer, hibernate does not include DISTINCT in SQL query, so we fall in troubles with paging (limit/offset)
I have tried:
Code:
Projections.groupProperty("id")
but then the results only contain the id.
And I have tried:
Code:
projections {
sqlGroupProjection '...', '..', [...], [...]
}
and I believe it only brought back the column I was grouping by after it got the syntax down.
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.
Login
FAQ
Search
