Multiple joins generated..

nstuart · **Joined:** Tue Mar 20, 2007 5:27 pm **Posts:** 12

I have a fairly simple HQL Query

Code:

SELECT 
DISTINCT SUM(tbl.fees.price),  
    country.region.regionName  
    FROM Account tbl 
    WHERE tbl.fees.price > :fees_price 
    GROUP BY country.region.regionName 

And it almost
works, the only problem with it is that it joins the fees table twice in the SQL.

Code:

select distinct SUM(fees1_.price) as col_0_0_, region3_.regionName as col_1_0_ from Account account0_, AccountFee fees1_, Country country2_, Region region3_, AccountFee fees4_ where account0_.id=fees4_.account_id and country2_.region_id=region3_.id and account0_.country_id=country2_.id and account0_.id=fees1_.account_id and fees4_.price>? group by region3_.regionName

Notice the fees4_.prrice > ? in the where clause. I really want that to be just fees1_.price instead. What do I need to add to get this to work correctly? I really don't need the extra join in there at all and would like it to just go away if possible.

Thanks for the help!
-Nick

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Code:

SELECT
DISTINCT SUM(f.price), 
    country.region.regionName 
    FROM Account tbl
    JOIN tbl.fees f
    WHERE f.price > :fees_price
    GROUP BY country.region.regionName

nstuart · **Joined:** Tue Mar 20, 2007 5:27 pm **Posts:** 12

Der, that makes sense. Of course this is more difficult because I'm building these queries dynamically and don't know when I'll need to join or not.

Hmph...well off to testing. Thanks!

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

If you're building queries dynamically, prefer criteria. A single createAlias before referring to tbl.fees would do the same thing as the pre-emptive join that I suggested. Plus, the code is easier to read when you use criteria.

nstuart · **Joined:** Tue Mar 20, 2007 5:27 pm **Posts:** 12

I tried that route first, but with the hack job that is needed to get server side DISTINCTs to work, I opted out of it. With out being able to use distinct it pretty much goes down the drain when trying to get the correct results. And even the hack I found in the forums here didn't work correctly.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You need to hack something? Why is setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) not everything you'll ever need?

nstuart · **Joined:** Tue Mar 20, 2007 5:27 pm **Posts:** 12

Last I checked (and have seen it noted anywhere else) that is not a SQL side distinct. This does a couple things. One, it slows things down when pulling a lot of records. Two, it could screw up some of the aggregate function results which is no good. (for example, I need a distinct in a count(), which as far as i know isn't possible through the criteria API)

The hackage I'm reffering to is from this thread:
http://forum.hibernate.org/viewtopic.php?t=941669

The supplied work around didn't work for me, so I fell back to the HQL.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

count(distinct *) is probably not natively supported in Hibernate, but Projections.sqlProjection can do it for you.

The transformer isn't server-side distinct, it's true. I'd have thought that Projection.distinct would work, but that thread you posted suggests otherwise. Is there a JIRA issue about this?

I'd imagine that, even if there was an easy way to use server-side distinct with criteria, it would rarely do what you'd expect it to. Each Criteria.createCriteria or createAlias is join to add a join to the select statement, making distinct much less useful. You'd still get 10 rows representing object A, if A has 10 different children joined to it during the criteria query.

Anyway, if you feel that you have to build the HQL queries dynamically, then I recommend that you always use explicit joins. It means that you'll have to build three strings simultaneously (select clause, join clause, where clause) and then build a full SQL statement when finished building. But if you can't deal with app-side distinct filtering, then these are the prices you must pay.

nstuart · **Joined:** Tue Mar 20, 2007 5:27 pm **Posts:** 12

Ya, its not so much about the limitations of the query building for the criteria API, but this one feature missing is kind of a killer. And I understand it may be missing, because as you said, how do you get to act as one would 'expect' 99% of the time?

As far as the query building, that is how I am doing now. I build each section up individually; SELECT, FROM, and WHERE (and GROUP BYs and ORDERS if needed) and go through the FROM clause to see if there is anything I need to join on and blah blah blah. But for now its working as we need to, and isn't to terribly complicated.