WHERE condition on a Set

gifor · **Joined:** Fri Oct 17, 2003 4:11 am **Posts:** 40

Hi!
I'm using Hibernate 2.1.1 and i'd like to select a Parent class with a specific Child class in a Set field.
It is possible or i must load all the Parent classes and then make a search (via Java language) on their Sets?

My classes are (just for example):
PARENT: id : Long, description : String, childs : Set
CHILD: id : Long, description : String

From a child istance "child1" (for example the Child class with id=1) can I retrive the parent classes that have Child class with id=1 in their set with a single query?

Thanx for any help!
Gio

joib · **Joined:** Wed Mar 03, 2004 9:38 am **Posts:** 70

Look up the chapter on parent-child relationships in the hibernate manual. Especially note the points about bidirectional relationsships and the meaning of inverse="true".

gifor · **Joined:** Fri Oct 17, 2003 4:11 am **Posts:** 40

Thanx for your answer but i want to do that explained without bidirectional relation... just a query on the parent that can watch the Set....

emmanuel · **Posted:** Mon Mar 29, 2004 7:33 am

from Parent parent where :child in elements(parent.children)

gifor · **Joined:** Fri Oct 17, 2003 4:11 am **Posts:** 40

Thanks very much for your answer... i use MySQL 4.1 and I get the following error (Gruppo is Parent, Permesso is the Child class)...

Code:

        Query query=session.createQuery("from HGruppo gruppo where :permesso in elements(gruppo.permessi)");
        query.setEntity("permesso",permesso);

Code:

4953 [main] DEBUG net.sf.hibernate.SQL  - select hgruppo0_.id as id, hgruppo0_.nome as nome, hgruppo0_.descrizione as descrizi3_ from ut_gruppo hgruppo0_ where (? in(select permessi1_.idPermesso from ut_gruppo_permesso permessi1_ where hgruppo0_.id=permessi1_.idGruppo))
Hibernate: select hgruppo0_.id as id, hgruppo0_.nome as nome, hgruppo0_.descrizione as descrizi3_ from ut_gruppo hgruppo0_ where (? in(select permessi1_.idPermesso from ut_gruppo_permesso permessi1_ where hgruppo0_.id=permessi1_.idGruppo))
4953 [main] DEBUG net.sf.hibernate.impl.BatcherImpl  - preparing statement
4953 [main] DEBUG net.sf.hibernate.type.LongType  - binding '19' to parameter: 1
4953 [main] DEBUG net.sf.hibernate.util.JDBCExceptionReporter  - SQL Exception
java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select permessi1_.idPermesso from ut_gruppo_permesso permessi1_"

gifor · **Joined:** Fri Oct 17, 2003 4:11 am **Posts:** 40

DOH! I have MySQL 4.0 and maybe it doesn't work inner SELECT... :(
In your opinion can be this the problem?

I read that 4.1 is in Alpha... any idea about a solution?

christian · **Posted:** Mon Mar 29, 2004 8:33 am

Use a better database, also read http://sql-info.de/mysql/gotchas.html if you like to use MySQL.

gifor · **Joined:** Fri Oct 17, 2003 4:11 am **Posts:** 40

Ok!
I found a solution with join for my MySQL 4.0.13...

Code:

select parent from Parent parent inner join parent.children child where child = :child

Thanks to all!!