Using filter on many-to-many relations

emsa · **Joined:** Tue Mar 15, 2005 7:37 am **Posts:** 16

Hi,

I'm trying out the new hb3 filters but a cannot figure out how to add a filter to a many-to-many relation.

If I add a filter like this:

Code:

on a class the generated SQL will map ID to the registered ID for the class just fine.

Adding this filter to a many-to-many relation does not however work since the 'primary' table in the generated SQL will be the table holding the relation and not the table that I'm expecting the results from. The result of this is that the ID-property/column above is not mapped to the related table but to the table holding the relation. Something like this:

Code:

select 
   table1_.MAIN_TABLE_ID as MAIN_TABLE1___,
   table1_.TABLE_TWO_ID as TABLE_TWO2___, 
   table2_.ID as ID0_,
from
   JOIN_TABLE table1_ 
inner join 
   TABLE2 table2_ 
on 
   table1_.TABLE_TWO_ID=table2_.ID 
where  
   table1_.ID = ? 
and
   table1_.MAIN_TABLE_ID=?

the correct SQL should have:

Code:

table2_.ID = ?

and not:

Code:

table1_.ID = ?

Is there a way aroud this or is this a limitation of the filters?

Thanks

/Magnus

emmanuel · **Posted:** Fri Mar 18, 2005 5:22 am

It is not implemented yet. You can check JIRA for some news about that one

emsa · **Joined:** Tue Mar 15, 2005 7:37 am **Posts:** 16

For reference: http://opensource.atlassian.com/projects/hibernate/browse/HHH-229

/Magnus

vgiguere · **Posted:** Tue Apr 19, 2005 5:19 pm

JIRA item HHH-229 is CLOSED and comments state that the correction is available in version 3.0.1.

I am running into the same problem with version 3.0.1. When navigating an association: A JDBC exception is thrown because the column name is not found on the association table.

my jar's manifest:

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.6.2
Created-By: 1.4.2-38 ("Apple Computer, Inc.")
Hibernate-Version: 3.0.1

steve · **Posted:** Tue Apr 19, 2005 9:42 pm

Well the case is closed, but it does not mention the fix being in 3.0.1. It was an enhancement originally targetted for 3.0.1, but it slipped because we wanted to get 3.0.1 out with its new features. This has now been slated for 3.0.2 which will go out this weekend. It is my last task for that release, so I'll be working on that the rest of this week.

vgiguere · **Posted:** Thu Apr 28, 2005 2:46 pm

Hi Steve,

I have read on the 3.0.2 release note that the following improvement had been made:

Quote:

improved filtering of many-to-many associations

I am not sure if this means that the bug referenced in JIRA item HHH-229 has been fixed. If this is the case, I am still having problems with filtering on many-to-many associations.

Here is my case:

I have a Group and a Role entity with a group_to_role association table.

Group has a many-to-many relationship to Role, on which I have added a filter. The filter checks that the attribute Role.domain_id is set to a certain value. The attribute domain_id is not present on the group_to_role association table.

By looking at the query being executed, I see that it discriminates based on domain_id on both Group and group_to_role tables, which thrown a SQL Exception:

Quote:

Invalid column name 'domain_id'.

Here is the mapping of the association that is not being filtered properly.

Code:

<set name="assignedRoles" table="security_group_to_role" lazy="true" cascade="save-update" access="field">
            <key column="group_id"/>
            <many-to-many class="SecurityRoleImpl" column="role_id"/>
            <filter name="FilterSecurityObjectBasedOnDomainId" condition="domain_id=:domainId"/>
</set> 

Thanks in advance for your help,

steve · **Posted:** Thu Apr 28, 2005 5:36 pm

well it would be filtering on Group because you have a filter defined on the Group class mapping (I am assuming that is the class owning this association).

There are two forms of filters for many-to-many. What you have now filters on the association table (your security_group_to_role). You want the other type (I assume):

Code:

<set name="assignedRoles" table="security_group_to_role" lazy="true" cascade="save-update" access="field"> 
            <key column="group_id"/> 
            <many-to-many class="SecurityRoleImpl" column="role_id"> 
                <filter name="FilterSecurityObjectBasedOnDomainId" condition="domain_id=:domainId"/> 
            </many-to-many>
</set> 

vgiguere · **Posted:** Tue May 03, 2005 5:18 pm

It does work fine.
I had not seen the change in the DTD.

Thanks for your help,

Vincent.