Hi guys,

I have a pretty simple problem to solve is giving me an headeache!
I have an object A that has a mapped Set inside B.

I would like to have all the A object and only the B object that are linked to A. A quite simple LEFT JOIN.

In sql my query would be something like

"SELECT A.id, A.NAME, B.ID, B.USER_ID, B.ACTION
from A LEFT JOIN B on A.id=B.PARENT_ID and B.ACTION='some' and
B.USER_ID=12345"

Now I'm trying to do something similar with HQL but I can't get through it.
Because if I do something like:

select a from A as a left join fetch a.b as b
where B.ACTION='some' and B.USER_ID=12345"

obviously I would not get any result if, for a particoular ID, I don't have any B element!

How is it possible to solve this?

Thank you so much!!

Mario

Time ago I had the same problem. Curiously the equivalent of on in HQL is with.

By the way, I'm using Hibernate 3.0.5 and it would be rather difficoult to update it as I have several applications running with this version and we would spend too much time checking if everything is ok with new versions.

Thanks

Mario

I see this, but I think the with clause has been introduced in Hibernate 3.1.x, am I right? Unfortunately as I wrote below I prefer not to update Hibernate as it would be too tought to check all the application.
Any other solution that would be available on 3.0.5??

Thanks!

mario

Does this work?


Don't forget to rate

Nope. This can't work.
In the A table I have 1 row, in the B table I have 2 rows both Linked to the row in the A table. The user_id in the B table is 12346

If I do the one above I would have that b is not null as I have the B with 12346 linked but it couldn't be b.USER_IS=12345 as well.
So that query would return me 0 rows and that's not correct

Thanks
Mario

Hi guys, I finally find the solution!! Filters!
I wasn't aware of them but I finally got what I need.

If you have problems on left join and you want to put some other conditions on the ON clause and you are using hibernate 3.0.5 you can set up filters.

Just put something like this after one-to-many

<one-to-many .... />
<filter-def name="myFilter">
<filter-param name="myFilterParam" type="string"/>
<filter-param name="myFilterParam2" type="string"/>
</filter-def>

then within the class mapping set the filter:

<filter name="myFilter" condition="username = :myFilterParam and action=:myFilterParam2"/>

When in code you have your hql and you want to use filters just do this:

Session session = ...;
session.enabledFilter("myFilter").setParameter("myFilterParam", "1234556").setParameter("myFilterParam2", "BUY");

That's it. Examining the sql generated by hibernate I see that those filter goes in the on condition.

Thanks

mario