Hi,

Having a nightmare (understatement) getting a query to work guys. A POF object has a list collection lineItems. I have written a search function that allows a user to search across POF detail or LineItem detail. Depending on what they add into their form, I gradually build up a HQL string. In addition I have to paginate.

I now how a count working on whatever permutation they enter, having used a count(distinct p) from POF type query. This allows me to know the total possible to divide by a results-per-page-fetch size.

The main query is a real ball ache. I am getting non-distinct rows back. Hibernate in Action says I cannot expect an eager fetch to return distinct rows. Fine, I understand that, and if I were not paginating it would not be a problem. However, because I need to call a query.setMaxResults and query.setFirstRow, what happens is that the first 2 rows are the same POF and I get duplicates on my result set. I have tried both select clause and normal HQL and everything is duplicating. I cannot use a Set either to prevent this because then on a 2-per-page for example, because the first 2 rows are duplicates from the query, only 1 row gets into the set. That;s because the Set workaround is post-query. Hibernate would need to go back for another query to fill the set to the fetch size I need.

Hope this makes sense .. essentially I need to get rid of duplicates in a simple query like this ...

from POF p left outer join fetch p.lineItems

I have also tried

from POF p left outer join fetch p.lineItems li where li in elements(p.lineItems)

Hibernate version:
2.1.6

Mapping documents:
On request. Essentially a POF has LineItem collection.

Code between sessionFactory.openSession() and session.close():

Query query = session.createQuery(hql);
// (firstResult - 1) * fetchSize=5
if (firstResult > -1) {
query.setFirstResult((firstResult - 1) * IPOF.FIND_FETCH_PER_PAGE.intValue());
query.setMaxResults(IPOF.FIND_FETCH_PER_PAGE.intValue());
}

return query.list();

Full stack trace of any exception that occurs:
N/A

Name and version of the database you are using:
SQL Server 2K

The generated SQL (show_sql=true):

select top 2 pof0_.pof_id as pof_id0_, lineitems1_.line_item_id as line_ite1_1_, pof0_.created as created0_, pof0_.status as status0_, pof0_.reference as reference0_, pof0_.external_id as external5_0_, pof0_.invoice_received as invoice_6_0_, pof0_.originator as originator0_, pof0_.originator_full_name as originat8_0_, pof0_.originator_email as originat9_0_, pof0_.department as department0_, pof0_.department_code as departm11_0_, pof0_.currency_code as currenc12_0_, pof0_.tax_rate as tax_rate0_, pof0_.authoriser as authoriser0_, pof0_.authoriser_full_name as authori15_0_, pof0_.authoriser_email as authori16_0_, pof0_.applies_to as applies_to0_, pof0_.net_cost as net_cost0_, pof0_.discount_cost as discoun19_0_, pof0_.carriage_cost as carriag20_0_, pof0_.tax_cost as tax_cost0_, pof0_.gross_cost as gross_cost0_, pof0_.notes as notes0_, pof0_.supplier_id as supplie24_0_, pof0_.delivery_address_id as deliver25_0_, lineitems1_.code as code1_, lineitems1_.description as descript3_1_, lineitems1_.fn as fn1_, lineitems1_.quantity as quantity1_, lineitems1_.unit_price as unit_price1_, lineitems1_.tax_type as tax_type1_, lineitems1_.net_cost as net_cost1_, lineitems1_.notes as notes1_, lineitems1_.pof_id as pof_id1_, lineitems1_.index_col as index_col1_, lineitems1_.line_item_id as line_ite1___, lineitems1_.pof_id as pof_id__, lineitems1_.index_col as index_col__ from dbPOF.dbo.tbl_POFs pof0_ left outer join dbPOF.dbo.tbl_LineItems lineitems1_ on pof0_.pof_id=lineitems1_.pof_id where (lineitems1_.line_item_id in(select lineitems2_.line_item_id from dbPOF.dbo.tbl_LineItems lineitems2_ where pof0_.pof_id=lineitems2_.pof_id))

Debug level Hibernate log excerpt:

This is close to impossible to solve. If we would try to distinctify eager-fetched results it would already be difficult, even more so if setFirstResult/setMaxResult was still supposed to work. So the only advice I can give you is dont use eager fetching when doing pagination.

hm yes, i worried that may be the case :( boo. the problem arose because my search form has to cater for searches across POF fields AND LineItem details. If I detected the form had LineItem field searching, I was adding the join in, hence the problems.

I think what I will do then is detect a LineItem field search and then perform a separate search on LineItem, getting the owning POF ids. Then I will need to do a search back on POF where the id is IN that list and without eager fetching. That should work. Will let you know.

You can do a join combined with a select distinct IMHO - you just can not do a fetch join - think about the SQL resultset which is actually processed by hibernate ...

hi michael,

i don't really understand what you are trying to say, my brain is burnt already from spending 5 hours on 1 hql query!

i see in hibernate in action, that a join (rather than fetch join) will return an array of pairs. is this what you mean to use? i still don't see how that would work, or how i could combine a select distinct with it.

from POF p fetch join p.lineItems will give a resultset from SQL server (without setting maxResults) like this

pof_id | pof_ref | lineitem_id | lineitem_desc
----------------------------------------------------
1 | shop | 1 | apples
2 | shop | 2 | bananas
3 | books | 3 | hibernate in action

as soon as I apply maxResults(2) with fetch join OR join, I will still get the TOP 2 results of this resultset which is the same POF. I really need pof_id 1 and 3.

If you have an idea in mind, I would really appreciate some more guidance, I'm not expert enough with Hibernate yet to solve this. I will end up taking pagination out all together!

Thanks :)

Okay, let me explain. If you do


the generated SQL will be like (simplified)


So Hibernate has to select also all LineItem property values, because the LineItems have to be actually created and populated.

However if you do


the generated SQL will be like


So the LineItem properties are not actually initialized. So you should be able to do


which creates a result set containing only distinct POF items. So you should be able to do pagination on this query

you're a genius. it worked. perfectly. it's wonderful. i love hibernate again.

thanks michael, this means I won't have to commit suicide tonight as I was planning.

:)