Hibernate version:

2.1.2

Mapping documents:

HostLeague.hbm.xml
...
<set
name="clubs"
table="host_league_club"
cascade="all"
>
<key column="fk_hlg_iid"/>
<many-to-many
column="fk_clb_iid"
class="com.xxx.ffl.model.Club"
/>
</set>

Club.hbm.xml
...
<set
name="hostLeagues"
table="host_league_club"
cascade="none"
>
<key column="fk_clb_iid"/>
<many-to-many
column="fk_hlg_iid"
class="com.xxx.ffl.model.HostLeague"
/>
</set>


Code between sessionFactory.openSession() and session.close():

...
HostLeague hlg supplied as parameter to method

List clubs =
session.find
("from Club as clb " +
"where clb.id not in " +
"(select hlg.clubs.elements.id from HostLeague hlg " +
"where hlg.id = ?) order by clb.code",
new Long(hlg.getId()),
Hibernate.LONG);


Full stack trace of any exception that occurs:

net.sf.hibernate.QueryException: expecting 'elements' or 'indices' after: elements.id [from com.xxx.ffl.model.Club as clb where clb.id not in (select hlg.clubs.elements.id from com.xxx.ffl.model.HostLeague hlg where hlg.id = ?) order by clb.code]
at net.sf.hibernate.collection.CollectionPropertyMapping.toType(CollectionPropertyMapping.java:53)
at net.sf.hibernate.hql.PathExpressionParser.getPropertyType(PathExpressionParser.java:244)
at net.sf.hibernate.hql.PathExpressionParser.end(PathExpressionParser.java:283)
at net.sf.hibernate.hql.SelectPathExpressionParser.end(SelectPathExpressionParser.java:14)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:154)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:124)
at net.sf.hibernate.hql.WhereParser.token(WhereParser.java:212)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1530)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1501)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1491)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1487)
at com.xxx.ffl.model.Club.listUnlinked(Club.java)
at com.xxx.ffl.usecase.ManageClub.listUnlinkedClubs(ManageClub.java)
at com.xxx.ffl.servlet.StartSeasonServlet.doPost(StartSeasonServlet.java)
at com.xxx.ffl.servlet.StartSeasonServlet.doGet(StartSeasonServlet.java)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:536)

Name and version of the database you are using:

PostgreSQL v7.4.2

The generated SQL (show_sql=true):

Don't get that far!

Debug level Hibernate log excerpt:


Problem description
I have two POJOs named HostLeague and Club. Each has a collection of the other as a property, e.g.

public class HostLeague
{
...
private Set clubs;
}

public class Club
{
...
private set hostLeagues;
}

The model is that a Club can be a member of one or more HostLeague and that a HostLeague contains one or more Club

The relational tables are:

host_league ---< host_league_club >--- club

I have successfully managed to list the Clubs that belong to a given HostLeague - what I cannot figure out (using Hibernate) is how to list the Clubs that do not belong to a given HostLeague. The SQL I would like to reproduce is something like:

select * from club where instance_id not in
(select fk_clb_iid from host_league_club where fk_hlg_iid = 1);

I've had a few of attempts to do this - the latest of which you can see above, along with the exception it generates. I'm currently thinking that a filter might be the answer but can't quite work out how to code it.

All help gratefully received.

Thanks,

Charles.