Hi,

I have the following problem. I have a many-to-many mapping between "Collection"<->"Object", so "Collection" has a set property in which I can put "Objects". So, I can retrieve some "Collection" and show it contents.

Now I want to add a security filter. I've added a many-to-many mapping between "Object"<->"Role", so each "Object" has set of roles that can view it. If this set of roles is empty - everyone can view the object.

If I use session.createFilter(collection, hql) everything is fine. For example (selecting objects everyone can view):



This works just fine - the query selects all objects having no roles associated with them. The filter is translated into something like "((select role3_.id from my.objects_roles roles2_, my.roles role3_ where object0_.id=roles2_.object_id and roles2_.role_id=role3_.id) is null)"

Now I want to use named filters from mapping file. I create a <filter> with the same text in condition ("(select r.id from this.roles r) is null"), launch it and get an exception that SQL is incorrect, because the condition is translated into something like "(select r.id from this.roles r) is null" in final SQL. That's incorrect, because "roles" is not a column in the database, but only collection property in Hibernate mapping (which is implemented via separate table with two foreign keys pointing at "Object" and "Role" tables).

So, in my point of view, the problem is that named filters do not support filtering a data when condition uses collection properties. However, session.createFilter works fine in that situation adding all necessary "selects" and joins.

How can I resolve this situation?

One possible workaround. Split "Object"<->"Role" many-to-many relationship into two one-to-many relationships and use the filter condition like: "(select 1 from my.object_role as or_ where or_.object_id = object_id) is null".

However, I'm still looking for clearer solution. Also, in the query above I have to specify database scheme name ("my"), otherwise it does not work. So, it seems to me that in case of session.createFilter HQL is used, but in case of named <filter>s inside mapping XML, these conditions are appended blindly to the native SQL.

I encounter same problem - HQL is not translated into correct sql on named filters. Any reason for this issue ?