How define this HQL in API criteria ??

swtechno · **Joined:** Thu Jun 01, 2006 11:27 am **Posts:** 28

How define this HQL with the API criteria ?? Is it possible??

select ui.Item
from UserItem ui
where ui.User.Id =1

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

Assuming that all associations are bidirectional, it would be

Code:

Criteria crit = session.createCriteria(Item.class, "item");
crit.createAlias("UserItem", "ui");
crit.add(Restrictions.eq("ui.User", userWithId1);

If any of those associations don't exist, then you'll need to use subqueries. I can work that out too, if you need it.

swtechno · **Joined:** Thu Jun 01, 2006 11:27 am **Posts:** 28

The relation are unidirectional:
UserItem --> Item
UserItem --> User

That is because Item and User clases are part of another system (i can't change it).

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

Hmm. Then you'll need to use a subquery.

Code:

DetachedCriteria uiCrit = DetachedCriteria.forClass(UserItem.class, "ui");
uiCrit.add(Restrictions.eq("ui.User", userWithId1));

Criteria crit = session.createCriteria(Item.class);
crit.add(Subqueries.in("id", uiCrit));

I'm not sure that that's going to work, though: usually ids and properties are treated differently (have a look at Restrictions.idEq), so Subqueries.in mightn't accept "id" as a valid property. If that's the case, you'll have to map UserItem.userId and UserItem.itemId as normal properties, just to get Subqueries.in to work. To map those columns twice (remember, the columns are in the many-to-one associations) use <property name="userId" formula="userId"/>, not column="userId".

That'd be icky, I hope you don't have to do that.