Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: Query w/ distinct paged results and JOIN on non primary key
PostPosted: Sun Oct 29, 2017 9:31 pm 
Newbie

Joined: Sun Oct 29, 2017 6:12 pm
Posts: 1
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-2772

Code:
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-tryin

I 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.


Top
 Profile  
 
 Post subject: Re: Query w/ distinct paged results and JOIN on non primary key
PostPosted: Mon Oct 30, 2017 2:13 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1515
The issue is still open, so you are better of writing the SQL query instead.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.