I'm having problems using filter and many-to-many association.
Let me explain:

I have superclass A, and A has an auto many-to-many lazy relationship.
So I has the two accessors:

Set getAs(); and void setAs(Set as);

But I also have several joined-subclasses of A, let's call them B, C, D.

I can add Bs to Cs, etc...

b.getAs().add(c);
b.getAs().add(d);

When I have an instanceof B, how can I filter the collection from getAs() but filtered to show only instance of C's??

If it's not possible, how can I create an HQL to fetch only C's of a B??

Thanks in advance,
Felipe;

I don't think you can do such thing even in HQL. Try to use createSQLQuery and do a native SQL query.

Thanks for the reply. I have been trying to do an SQL query, but I'm having some problems. Can't give more details now because I don't the the stack trace here.

But SQLQuery part of the Hibernate Manual is to small and undetailed. Is the a more complete reference regarding SQLQuery??

Thanks,
Felipe;

I have not tryed this, but woudl this work with many-to-many and joined-subclasses?

I have an instanceof A (the superclass) called instOfA. If I want to load only the related B's:

select b from B as b, A as a where b in elements(a.as) and a = instOfA

Would this work?

Ok, I will post some code because the HQL didn't work but it seems right, maybe it's and SQL generation problem.

My test classes:
public class A {
private int id;
private String name;
private Set as;
...
}

public class B extends A {
private String strB;
...
}

public class C extends A {
private String strC;
...
}

My mappings:

<hibernate-mapping package="test">
<class name="A">
<id name="id">
<generator class="native"/>
</id>
<property name="name" not-null="true"/>
<set name="as" table="COMPOE" lazy="true" cascade="all">
<key/>
<many-to-many class="A"/>
</set>
</class>
</hibernate-mapping>

<hibernate-mapping package="test">
<joined-subclass name="B" extends="test.A">
<key/>
<property name="strB" not-null="true"/>
</joined-subclass>
</hibernate-mapping>

<hibernate-mapping package="test">
<joined-subclass name="C" extends="test.A">
<key/>
<property name="strC" not-null="true"/>
</joined-subclass>
</hibernate-mapping>

I populated my DB using the following code:

Session session = sessions.openSession();
Transaction tx = session.beginTransaction();

B parent = new B();
parent.setName("parent name");
parent.setStrB("parent str");

for (int i = 0; i < 10; i++) {
B child = new B();
child.setName("b name");
child.setStrB("b str");
session.save(child);
parent.getAs().add(child);
}

for (int i = 0; i < 10; i++) {
C child = new C();
child.setName("c name");
child.setStrC("c str");
session.save(child);
parent.getAs().add(child);
}

session.save(parent);
tx.commit();

And test code:
Session session = sessions.openSession();
List list = session.createQuery("from A").list();

for (int i = 0; i < list.size(); i++) {
A a = (A) list.get(i);
Set childs = a.getAs();

if (childs.size() == 0) { continue; }

Query q = session.createQuery("select c from C c, A a where c in elements(a.as) and a.id = " + a.getId());
List cs = q.list();
System.out.println(cs.size());
}

The println should return 10, the number of Cs.

This is the SQL generated:

select c0_.C as id, c0_.strC as strC3_, c0__1_.name as name0_ from C c0_ inner join A c0__1_ on c0_.C=c0__1_.id, A a1_ where (c0_.C in(select as2_.elt from COMPOE as2_ where a1_.id=as2_.id))and(a1_.id=22 )

And I get the following error message:

13:54:00,630 WARN JDBCExceptionReporter:38 - SQL Error: 1064, SQLState: 42000
13:54:00,630 ERROR JDBCExceptionReporter:46 - 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 as2_.elt from COMPOE as2_ where a1_.id=as2_.id))and(a1_."
13:54:00,690 WARN JDBCExceptionReporter:38 - SQL Error: 1064, SQLState: 42000
13:54:00,690 ERROR JDBCExceptionReporter:46 - 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 as2_.elt from COMPOE as2_ where a1_.id=as2_.id))and(a1_."
13:54:00,690 ERROR JDBCExceptionReporter:38 - Could not execute query
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 as2_.elt from COMPOE as2_ where a1_.id=as2_.id))and(a1_."
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1876)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1098)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1192)

Any thought on this one?

It worked with MS SQL server, but no with MySQL.