Criteria and many to many query + like or in clause

*alexandre* · **Joined:** Tue Jun 06, 2006 3:37 am **Posts:** 29

Hi,

I've a problem while I'm trying to execute a query I need to do the following sql query within the use of criteria or hql

Code:

 select affaire.nom, affaire.id ... from affaire, requerant, affaire_requerant where affaire_requerant.ref_affaire = affaire.id_affaire and affaire_requerant.ref_requerant = requerant.id_requerant and requerant.nom like '%Jaq%?

in my Affaire entity I've a set of Requerant declared

I first load all the requerant that meets the like clause and then try to use a

Code:

criteria.createCriteria(Affaire.class)
Criteria testMany = criteria.createCriteria("requerants");
testMany.add(Restrictions.in("id", getRequerantsArray());
criteria.list()

where getRequerantsArray return an array of id of the eligible requerant

any idea ?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Code:

Criteria critAff==session.createCriteria(Affaire.class);
Criteria critReq=critAff.createCriteria("requerants");
critReq.add(Restrictions.like("nom", "%Jaq%");
critReq.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
return critReq.list();

*alexandre* · **Joined:** Tue Jun 06, 2006 3:37 am **Posts:** 29

The problem with this solution is I have to do it with

affaire.requerants.nom
affaire.requerants.prenom
affaire.requerants.dateNaissance

andd all the fields of other relations

this is definitly not possible to subquery temporary subset of already retrieved elements list ? by using subquery or detached criteria ?

the getRequerantsArray() already filter all data related to a specifc relation

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Je ne comprends pas quel est le problème.
Donne-moi quelques details de plus, s.t.p.

*alexandre* · **Joined:** Tue Jun 06, 2006 3:37 am **Posts:** 29

Criteria critReq=critAff.createCriteria("requerants");
critReq.add(Restrictions.like("nom", "%Jaq%");

mean affaire.requerans.requerant.nom ? ???

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

*alexandre* · **Joined:** Tue Jun 06, 2006 3:37 am **Posts:** 29

parfait merci beacoup :)