Hibernate version:3.1.3

I have a one-to-many association between GROUP and QUESTION

This works fine when I need to get all QUESTION entries for a GROUP. The resulting query when a left join is used (simplified):
select
g.*, q.*
from
GROUP g
left outer join QUESTION q
on g.ID = q.GROUP_ID

I want to be able to add a filter / parameter that would result in the following query:
select
g.*, q.*
from
GROUP g
left outer join QUESTION q
on g.ID = q.GROUP_ID and q.ACTIVE = 'Y'

I have tried using a filter but that only adds to the where clause. That does not give me the results I need because I still want the group to be returned if it has not active questions.

I am trying not to use HQL in the case, I want to use XML mapping if at all possible.

Is it possible to add criteria to the "on" clause of a join at runtime?

I am a little bit confused by your question when you say you favour an XML mapping over a HQL command. If you need this filter over your collection you may want to set a where attribute for one-to-many association which will be added to its outer join criteria. However, you might also want to use HQL to only do this filtering at runtime and for specific use cases. Then you might want to do something like:

select g from Group g left outer join g.questions q with q.active = 'Y'


Please give more details of what you want to do if neither cases are suitable for you.


Farzad-

Try:

select
g.*, q.*
from
GROUP g
left outer join QUESTION q
on g.ID = q.GROUP_ID and (q.ACTIVE is null or q.ACTIVE = 'Y')

Sorry I meant:


select
g.*, q.*
from
GROUP g
left outer join QUESTION q
on g.ID = q.GROUP_ID
where q.ACTIVE is null or q.ACTIVE = 'Y'

malm66 adding "q.ACTIVE is null or q.ACTIVE = 'Y'" to the where clause would not give the same results as a left join.

e.g. A GROUP that has only one question where q.ACTIVE = 'N' would return no results when the condition you suggest is added to the where clause.

select
g.*, q.*
from
GROUP g
left outer join QUESTION q
on g.ID = q.GROUP_ID and q.ACTIVE = 'Y'

Will return one GROUP entry with the QUESTION portion of that being null.