with-clause not allowed on fetched associations

mars76 · **Joined:** Mon May 18, 2009 6:20 am **Posts:** 1

Hello,

I have a HQL with a LEFT JOIN FETCH with an extra condition:

Code:

SELECT a FROM A a LEFT JOIN FETCH a.bs b WITH b.FinalDate is null

It worked really fine without FETCH, but when I put it (and I really need it) it throws QuerySintaxException:

Quote:

with-clause not allowed on fetched associations; use filters

First of all, the other solution:

Code:

SELECT a FROM A a LEFT JOIN FETCH a.bs b WHERE b.FinalDate is null

It's not the same query, because the where clause filter the collection after the join, and of course the results are wrong.

The only workaround I think is using WITH clause without fetch and then iterate the results for getting the bs element manually, but this is really dirty.

I see the documentation (not really much to read about with-clause) and another similar topics, but nothing about how to solve this problem.
Anyone can bring me some light?

Thanks a lot and excuse my english

[postgresql 8.2 and hibernate 3.3.0 ga.]

harshalx · **Posted:** Tue Jul 07, 2009 4:50 pm

I really want Hibernate Developers to answer this question. It doesn't make any sense not to allow with clause with fetch. I would like some credible technical explanation to this.

ge0ffrey · **Joined:** Thu Oct 13, 2005 4:19 am **Posts:** 98

I got a similar problem:
Select all green houses and for each green house every green room.
Some green houses have no green rooms.

Code:

select h
from house h
  left join fetch h.rooms r with r.color = 'GREEN'
where h.color = 'GREEN'

This should fetch:
- Green house A with green rooms X and Y (but without its red room Z)
- Green house B without any green rooms (but without its red rooms W and V)

How could I write this query differently?
Note that starting from the rooms won't work: green houses with no green rooms should also be fetched.

fedor_bel · **Joined:** Tue Jun 08, 2010 8:49 am **Posts:** 2

I have exactly the same problem! Green Houses with/without green rooms!!! This is important! Somebody answer!!!

Temujin_12 · **Joined:** Mon Jun 05, 2006 11:44 am **Posts:** 11

Exact same problem here as well. Trying to avoid using native query if at all possible.

Ran into same problem trying to left join using a Criteria in Hibernate 3.3. In Hibernate 3.5 there's createAlias(String associationPath, String alias, int joinType, Criterion withClause) which, in theory, should be able to do what you're looking for.

Note: I haven't tried to see if Hibernate 3.5 fixes the "with" clauses for fetched joins in HQL. Seems to make sense that if Criteria can do it, that HQL should too, but I don't see it specifically documented in the HQL documentation.

alvagenesis · **Joined:** Wed Jul 27, 2011 10:31 am **Posts:** 5

Quote:

Ran into same problem trying to left join using a Criteria in Hibernate 3.3. In Hibernate 3.5 there's createAlias(String associationPath, String alias, int joinType, Criterion withClause) which, in theory, should be able to do what you're looking for.

createAlias(path,alias,joinType,withClause) will only allow you put one criteria in the join clause. What if i need to add more than one criteria?

eg.

Code:

join tablename on tablename.id=tablename2.id and tablename.field1='some value' and tablename.field3=somevalue and so on...

Thanks in advance.

mattg · **Joined:** Mon Apr 02, 2012 10:19 pm **Posts:** 1

This is still an issue with version 3.6.1.Final