SQL :


Line 1 is easy to convert in HQL.
Line 3 doesn't need to be converted to HQL.
How can I convert Line 2?

In other words, I want A X B where X denotes left outer join. There is a direct relationship between A and B. But I want to apply filter on B table and then I want A X B(filtered).

Please follow Cross Post for more info.

Any body?

Hello Vishal,
after looking at the cross post have no idea if this can be of any help.. I was browsing some other post and was wondering if this would help you anyways below is my two cents.
Have you tried to define a filter on the mapping file itself? here is the linky:-
http://docs.jboss.org/hibernate/stable/ ... lters.html

Long story short the article mentions to create a filter in your case on the Table B and enable filter and then execute your HQL query.
THIS MAY NOT DO THE LEFT OUTER JOIN THOU.
<class name="myClass" ...>
...
<filter name="myFilter" condition=":myFilterParam = MY_FILTERED_COLUMN"/>
</class>

enable the filter and then run the query.

Session session = ...;
session.enableFilter("myFilter").setParameter("myFilterParam", 35236);
List results = session.createQuery(<<your HQL>>)
.list();

Hope this helps,
Srilatha.

hi, I think I'm doing the same as what's suggested here. Filter enabled on a one-to-many collection myCollectionField of ObjectA, then outer join. Then I did
With Criterion API:
session.createCriteria(ObjectA.class)
.createAlias("myCollectionField", "collB", CriteriaSpecification.LEFT_JOIN)
.add(Restrictions.in("id", myIds))
.setFetchMode("collB" , FetchMode.JOIN)
.list();
Then I tried with HQL:
session.createQuery("select objA, collB from ObjectA objA" +
" left outer join objA.myCollectionField collB where objA.id in (:idList)")
.setParameterList("idList", myIds)
.list();

In both cases, without Filter enabled, I get an outer-join. But with filter enabled, i get an (effectively inner-join) Statement.

select ... from OBJECTA_TABLE objA, OBJECTB_TABLE collB where objA.ID=collB.objA_ID(+)
and (collB filter condition)
and (objA.ID in ( ? , ?))


This as mentioned is like an inner-join, because the filter condition ccurs in the where clause instead of the sub-select as required by Vishal.

So if anyone knows another way to achieve that, I'll be happy to know it.
thanks.