I have got users and nodes in a many to many mapping. But nodes are of two types private and public. I have a hidden table to manage the many to many association, which has foreign keys pointing to tables users and nodes.

The user pojo has methods getPublicNodes() and getPrivateNodes() which return sets. I need to filter out the private or public nodes at DBMS itself for performace reasons.

I have the option of either using filters or the "where" attribute. The mapping of nodes in User.hbm.xml looks like this

<set>
name="privateNodes"
table="USER_NODES"
cascade="save-update"
lazy="true"
where="node.private = true"
inverse="true">

<key column="USER_ID"/>
<many-to-many>
class="Node"
column="NODE_ID"/>
</set>

the problem is the "where" doesn't work, as in the query generated is wrong. I assume the where attribute should have native sql. But is this true?

Even if I use proper sql it still didn't work as in it gave parsing errors. Later in derby docs I came accross that if we use alias for a table name in a query we cannot use the table name again. So then how do I know what alias Hibernate would use for the queries it generates?

Is there a better way to do this? Please help. If you need more information please let me know. I am stuck at this.

Forgot to mention,

Hibernate version 3.1.3
DBMS Derby version 10.1.3.1