Filtering on Many to Many relationship

chris.howe-jones · **Posted:** Wed Apr 19, 2006 6:11 am

I am using EJB 3.0 persistence shipped with JBoss AS 4.0.4.CR2 (EJB 3.0 RC 6).

I have a Many to Many relationship between two entities. I need to filter the collection from both entities based on dates within the entities.

E.g. Within a class 'C' I have a Collection of 'CtoD' entities defined in a Many to Many relationship that I want to filter by the dates on 'CtoD'.

Code:

   
@ManyToMany(mappedBy = "owners",  cascade=CascadeType.ALL, fetch= FetchType.EAGER)
@Filter(name = "timeFilter", condition = ":time between dateEffective and dateInactive")
private Collection<CtoD> cdCollection = new ArrayList<CtoD>();

(I am using field access in the above example).
In this example the class CtoD has columns called dateEffective and dateInactive.

When I activate the 'timeFilter' and set the ':time' parameter and then try and fetch a 'C' I get the following error:

Code:

10:03:20,759 WARN  [JDBCExceptionReporter] SQL Error: 1054, SQLState: 42S22
10:03:20,759 ERROR [JDBCExceptionReporter] Unknown column 'cdcollecti1_.dateEffective' in 'on clause'
10:03:20,759 INFO  [DefaultLoadEventListener] Error performing load command
org.hibernate.exception.SQLGrammarException: could not load an entity: [com.tecssphere.chj.C#1]
....

I can only filter a many to many relationship using columns within the joining table which is not what I require.

Can anyone help with this?

alesj · **Posted:** Thu Apr 20, 2006 3:25 am

Maybe write 2 OneToMany relations on a middle object.

C - has One2Many to M

M (middle) - has Many2One to C and Many2One to D

D - has One2Many to M

And define filter on the M.

chris.howe-jones · **Posted:** Thu Apr 20, 2006 9:37 am

I thought of that but it means compromising my design to enable this feature! There really is no information on the relationship itself so I would rather not have to introduce this as it over complicates and already complex situation.

emmanuel · **Posted:** Thu Apr 27, 2006 9:36 am

this is intrusive but you can use a
session.createFilter(c.cdCollection(), "this.begindDate = :begindate ...")