Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3

Mapping documents:
ProjectComponent
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="de.mpicbg.db.endotrack.model.ProjectComponent"
table="PROJECT_COMPONENT">
<id name="id" column="ID">
<generator class="assigned" />
</id>
<version name="version" column="VERSION" unsaved-value="null" />
<property name="name" column="NAME" type="text" />
<property name="description" column="DESCRIPTION" type="text" />
<property name="code" column="CODE" />
<property name="type" column="TYPE" />

<set name="allowableParentTypes"
table="ALLOWABLE_PARENT_TYPES">
<key column="TYPE_ID" />
<element type="string" column="TYPE_VALUE" />
</set>
<set name="allowableChildTypes" table="ALLOWABLE_CHILD_TYPES">
<key column="TYPE_ID" />
<element type="string" column="TYPE_VALUE" />
</set>

<set name="childComponents" cascade="save-update"
table="CHILD_COMPONENT">
<key column="PARENT_ID" />
<many-to-many
class="de.mpicbg.db.endotrack.model.ProjectComponent"
column="CHILD_ID" />
</set>
<set name="parentComponents" cascade="save-update"
table="PARENT_COMPONENT">
<key column="CHILD_ID" />
<many-to-many
class="de.mpicbg.db.endotrack.model.ProjectComponent"
column="PARENT_ID" />
</set>

<joined-subclass
name="de.mpicbg.db.endotrack.model.SimpleProjectComponent"
table="SIMPLE_PROJECT_COMPONENT">
<key column="ID" />
</joined-subclass>

<joined-subclass name="de.mpicbg.db.endotrack.model.WorkItem"
table="WORKITEM">
<key column="ID" />
<property name="endDate" column="END_DATE" />
<property name="startDate" column="START_DATE" />

<many-to-one name="leadOrganisation"
column="LEAD_ORGANISATION" cascade="save-update" />

<set name="secondaryOrganisations" cascade="save-update"
table="SECONDARY_ORGANISATIONS">
<key column="WORKITEM_ID" />
<many-to-many
class="de.mpicbg.db.endotrack.model.Organisation"
column="ORGANISATION_ID" />
</set>

<joined-subclass
name="de.mpicbg.db.endotrack.model.SimpleWorkItem"
table="SIMPLEWORKITEM">
<key column="ID" />
</joined-subclass>

</joined-subclass>
</class>
</hibernate-mapping>

Organisation
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="de.mpicbg.db.endotrack.model.Organisation"
table="ORGANISATION">
<id name="id" column="ID">
<generator class="assigned" />
</id>
<version name="version" column="VERSION" unsaved-value="null" />
<property name="name" column="NAME" not-null="true" />
<property name="description" column="DESCRIPTION" />
<property name="url" column="URL" />
<property name="country" column="COUNTRY" />
<property name="code" column="CODE" unique="true"
not-null="true" />
<property name="shortName" column="SHORT_NAME" unique="true"
not-null="true" />
<joined-subclass
name="de.mpicbg.db.endotrack.model.SimpleOrganisation"
table="SIMPLEORGANISATION">
<key column="ID" />
</joined-subclass>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
List<?> orgs = getHibernateTemplate().find(
"from WorkItem wi where ? in wi.secondaryOrganisations", org);
Full stack trace of any exception that occurs:
java.sql.SQLException: Unexpected token: . in statement [select workitem0_.ID as ID12_, workitem0_1_.VERSION as VERSION12_, workitem0_1_.NAME as NAME12_, workitem0_1_.DESCRIPTION as DESCRIPT4_12_, workitem0_1_.CODE as CODE12_, workitem0_1_.TYPE as TYPE12_, workitem0_.END_DATE as END2_18_, workitem0_.START_DATE as START3_18_, workitem0_.LEAD_ORGANISATION as LEAD4_18_, case when workitem0_2_.ID is not null then 3 when workitem0_.ID is not null then 2 end as clazz_ from WORKITEM workitem0_ inner join PROJECT_COMPONENT workitem0_1_ on workitem0_.ID=workitem0_1_.ID left outer join SIMPLEWORKITEM workitem0_2_ on workitem0_.ID=workitem0_2_.ID, SECONDARY_ORGANISATIONS secondaryo1_, ORGANISATION organisati2_ where workitem0_.ID=secondaryo1_.WORKITEM_ID and secondaryo1_.ORGANISATION_ID=organisati2_.ID and (? in (.))]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:497)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:415)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1538)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2157)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2041)
at org.hibernate.loader.Loader.list(Loader.java:2036)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:844)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:835)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:831)
at de.mpicbg.db.endotrack.dao.hibernate.WorkItemHibernateDao.getAllBySecondaryOrg(WorkItemHibernateDao.java:78)
at de.mpicbg.db.endotrack.dao.WorkItemDaoTest.testGetBySecondaryOrg(WorkItemDaoTest.java:145)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)
at org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)
at org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)
at org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)
at org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)
at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)
at org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)
at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Name and version of the database you are using:
Postgres 8.2
The generated SQL (show_sql=true):
select workitem0_.ID as ID12_, workitem0_1_.VERSION as VERSION12_, workitem0_1_.NAME as NAME12_, workitem0_1_.DESCRIPTION as DESCRIPT4_12_, workitem0_1_.CODE as CODE12_, workitem0_1_.TYPE as TYPE12_, workitem0_.END_DATE as END2_18_, workitem0_.START_DATE as START3_18_, workitem0_.LEAD_ORGANISATION as LEAD4_18_, case when workitem0_2_.ID is not null then 3 when workitem0_.ID is not null then 2 end as clazz_ from WORKITEM workitem0_ inner join PROJECT_COMPONENT workitem0_1_ on workitem0_.ID=workitem0_1_.ID left outer join SIMPLEWORKITEM workitem0_2_ on workitem0_.ID=workitem0_2_.ID, SECONDARY_ORGANISATIONS secondaryo1_, ORGANISATION organisati2_ where workitem0_.ID=secondaryo1_.WORKITEM_ID and secondaryo1_.ORGANISATION_ID=organisati2_.ID and (? in (.))
Debug level Hibernate log excerpt:
1477 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - parse() - HQL: from de.mpicbg.db.endotrack.model.WorkItem wi where ? in wi.secondaryOrganisations
1493 [main] DEBUG org.hibernate.hql.ast.AST - --- HQL AST ---
\-[QUERY] 'query'
+-[SELECT_FROM] 'SELECT_FROM'
| \-[FROM] 'from'
| \-[RANGE] 'RANGE'
| +-[DOT] '.'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[DOT] '.'
| | | | | +-[DOT] '.'
| | | | | | +-[IDENT] 'de'
| | | | | | \-[IDENT] 'mpicbg'
| | | | | \-[IDENT] 'db'
| | | | \-[IDENT] 'endotrack'
| | | \-[IDENT] 'model'
| | \-[IDENT] 'WorkItem'
| \-[ALIAS] 'wi'
\-[WHERE] 'where'
\-[IN] 'in'
+-[PARAM] '?'
\-[IN_LIST] 'inList'
\-[DOT] '.'
+-[IDENT] 'wi'
\-[IDENT] 'secondaryOrganisations'

1494 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
1531 [main] DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker - select << begin [level=1, statement=select]
1546 [main] DEBUG org.hibernate.hql.ast.tree.FromElement - FromClause{level=1} : de.mpicbg.db.endotrack.model.WorkItem (wi) -> workitem0_
1550 [main] DEBUG org.hibernate.hql.ast.tree.FromReferenceNode - Resolved : wi -> workitem0_.ID
1550 [main] DEBUG org.hibernate.hql.ast.tree.DotNode - getDataType() : secondaryOrganisations -> org.hibernate.type.SetType(de.mpicbg.db.endotrack.model.WorkItem.secondaryOrganisations)
1551 [main] DEBUG org.hibernate.hql.ast.tree.FromElementFactory - createManyToMany() : path = wi.secondaryOrganisations role = de.mpicbg.db.endotrack.model.WorkItem.secondaryOrganisations associatedEntityName = de.mpicbg.db.endotrack.model.Organisation
1555 [main] DEBUG org.hibernate.hql.ast.tree.FromElement - FromClause{level=1} : de.mpicbg.db.endotrack.model.Organisation (no alias) -> organisati2_
1555 [main] DEBUG org.hibernate.hql.ast.tree.FromClause - addJoinByPathMap() : wi.secondaryOrganisations -> ORGANISATION organisati2_
1555 [main] DEBUG org.hibernate.hql.ast.tree.DotNode - dereferenceCollection() : Created new FROM element for wi.secondaryOrganisations : SECONDARY_ORGANISATIONS organisati2_
1555 [main] DEBUG org.hibernate.hql.ast.tree.FromReferenceNode - Resolved : wi.secondaryOrganisations -> .
1555 [main] DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker - select : finishing up [level=1, statement=select]
1555 [main] DEBUG org.hibernate.hql.ast.HqlSqlWalker - processQuery() : ( SELECT ( FromClause{level=1} WORKITEM workitem0_ SECONDARY_ORGANISATIONS organisati2_ ) ( where ( in ? ( inList ( . workitem0_.ID secondaryOrganisations ) ) ) ) )
1560 [main] DEBUG org.hibernate.hql.ast.HqlSqlWalker - Derived SELECT clause created.
1564 [main] DEBUG org.hibernate.hql.ast.util.JoinProcessor - Using FROM fragment [SECONDARY_ORGANISATIONS secondaryo1_, ORGANISATION organisati2_]
1564 [main] DEBUG org.hibernate.hql.ast.util.SyntheticAndFactory - Using WHERE fragment [workitem0_.ID=secondaryo1_.WORKITEM_ID and secondaryo1_.ORGANISATION_ID=organisati2_.ID]
1564 [main] DEBUG org.hibernate.hql.ast.util.JoinProcessor - Using FROM fragment [WORKITEM workitem0_ inner join PROJECT_COMPONENT workitem0_1_ on workitem0_.ID=workitem0_1_.ID left outer join SIMPLEWORKITEM workitem0_2_ on workitem0_.ID=workitem0_2_.ID]
1564 [main] DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker - select >> end [level=1, statement=select]
1567 [main] DEBUG org.hibernate.hql.ast.AST - --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (PROJECT_COMPONENT,SECONDARY_ORGANISATIONS,WORKITEM)
+-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
| +-[SELECT_EXPR] SelectExpressionImpl: 'workitem0_.ID as ID12_' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=wi,role=null,tableName=WORKITEM,tableAlias=workitem0_,origin=null,colums={,className=de.mpicbg.db.endotrack.model.WorkItem}}}
| \-[SQL_TOKEN] SqlFragment: 'workitem0_1_.VERSION as VERSION12_, workitem0_1_.NAME as NAME12_, workitem0_1_.DESCRIPTION as DESCRIPT4_12_, workitem0_1_.CODE as CODE12_, workitem0_1_.TYPE as TYPE12_, workitem0_.END_DATE as END2_18_, workitem0_.START_DATE as START3_18_, workitem0_.LEAD_ORGANISATION as LEAD4_18_, case when workitem0_2_.ID is not null then 3 when workitem0_.ID is not null then 2 end as clazz_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=2, fromElements=2, fromElementByClassAlias=[wi], fromElementByTableAlias=[workitem0_, organisati2_], fromElementsByPath=[wi.secondaryOrganisations], collectionJoinFromElementsByPath=[], impliedElements=[]}
| +-[FROM_FRAGMENT] FromElement: 'WORKITEM workitem0_ inner join PROJECT_COMPONENT workitem0_1_ on workitem0_.ID=workitem0_1_.ID left outer join SIMPLEWORKITEM workitem0_2_ on workitem0_.ID=workitem0_2_.ID' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=wi,role=null,tableName=WORKITEM,tableAlias=workitem0_,origin=null,colums={,className=de.mpicbg.db.endotrack.model.WorkItem}}
| \-[FROM_FRAGMENT] ImpliedFromElement: 'SECONDARY_ORGANISATIONS secondaryo1_, ORGANISATION organisati2_' ImpliedFromElement{implied,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=null,role=de.mpicbg.db.endotrack.model.WorkItem.secondaryOrganisations,tableName=ORGANISATION,tableAlias=organisati2_,origin=WORKITEM workitem0_ inner join PROJECT_COMPONENT workitem0_1_ on workitem0_.ID=workitem0_1_.ID left outer join SIMPLEWORKITEM workitem0_2_ on workitem0_.ID=workitem0_2_.ID,colums={workitem0_.ID ,className=de.mpicbg.db.endotrack.model.Organisation}}
\-[WHERE] SqlNode: 'where'
+-[THETA_JOINS] SqlNode: '{theta joins}'
| \-[SQL_TOKEN] SqlFragment: 'workitem0_.ID=secondaryo1_.WORKITEM_ID and secondaryo1_.ORGANISATION_ID=organisati2_.ID'
\-[IN] InLogicOperatorNode: 'in'
+-[PARAM] ParameterNode: '?' {ordinal=0, expectedType=null}
\-[IN_LIST] SqlNode: 'inList'
\-[DOT] DotNode: '.' {propertyName=secondaryOrganisations,dereferenceType=3,propertyPath=secondaryOrganisations,path=wi.secondaryOrganisations,tableAlias=organisati2_,className=de.mpicbg.db.endotrack.model.Organisation,classAlias=null}
+-[ALIAS_REF] IdentNode: 'workitem0_.ID' {alias=wi, className=de.mpicbg.db.endotrack.model.WorkItem, tableAlias=workitem0_}
\-[IDENT] IdentNode: 'secondaryOrganisations' {originalText=secondaryOrganisations}

1568 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
1578 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - HQL: from de.mpicbg.db.endotrack.model.WorkItem wi where ? in wi.secondaryOrganisations
1578 [main] DEBUG org.hibernate.hql.ast.QueryTranslatorImpl - SQL: select workitem0_.ID as ID12_, workitem0_1_.VERSION as VERSION12_, workitem0_1_.NAME as NAME12_, workitem0_1_.DESCRIPTION as DESCRIPT4_12_, workitem0_1_.CODE as CODE12_, workitem0_1_.TYPE as TYPE12_, workitem0_.END_DATE as END2_18_, workitem0_.START_DATE as START3_18_, workitem0_.LEAD_ORGANISATION as LEAD4_18_, case when workitem0_2_.ID is not null then 3 when workitem0_.ID is not null then 2 end as clazz_ from WORKITEM workitem0_ inner join PROJECT_COMPONENT workitem0_1_ on workitem0_.ID=workitem0_1_.ID left outer join SIMPLEWORKITEM workitem0_2_ on workitem0_.ID=workitem0_2_.ID, SECONDARY_ORGANISATIONS secondaryo1_, ORGANISATION organisati2_ where workitem0_.ID=secondaryo1_.WORKITEM_ID and secondaryo1_.ORGANISATION_ID=organisati2_.ID and (? in (.))
1578 [main] DEBUG org.hibernate.hql.ast.ErrorCounter - throwQueryException() : no errors
1593 [main] DEBUG org.springframework.transaction.support.TransactionSynchronizationManager - Retrieved value [org.springframework.orm.hibernate3.SessionHolder@44a541] for key [org.hibernate.impl.SessionFactoryImpl@c26acd] bound to thread [main]
1604 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
1604 [main] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection
1604 [main] DEBUG org.hibernate.SQL - select workitem0_.ID as ID12_, workitem0_1_.VERSION as VERSION12_, workitem0_1_.NAME as NAME12_, workitem0_1_.DESCRIPTION as DESCRIPT4_12_, workitem0_1_.CODE as CODE12_, workitem0_1_.TYPE as TYPE12_, workitem0_.END_DATE as END2_18_, workitem0_.START_DATE as START3_18_, workitem0_.LEAD_ORGANISATION as LEAD4_18_, case when workitem0_2_.ID is not null then 3 when workitem0_.ID is not null then 2 end as clazz_ from WORKITEM workitem0_ inner join PROJECT_COMPONENT workitem0_1_ on workitem0_.ID=workitem0_1_.ID left outer join SIMPLEWORKITEM workitem0_2_ on workitem0_.ID=workitem0_2_.ID, SECONDARY_ORGANISATIONS secondaryo1_, ORGANISATION organisati2_ where workitem0_.ID=secondaryo1_.WORKITEM_ID and secondaryo1_.ORGANISATION_ID=organisati2_.ID and (? in (.))
Problems with Session and transaction handling?

The case I have is that the WorkItem holds a list of organisations. I want to have an HQL quer which says - give me all the workitems which have in their secondaryOrganisation - this organisation. The thought that the HQL query shown above would do that for me. Hibernate receives the HQL, parses it and tries to execute the SQL. However the exceuted SQL throws an error. I've parsed the SQL and exceuted the query (taking out the (? in (.)) bit) but it seems to return nothing back. I suspect I haev teh HQL query incorrect if this is the case, any clues as to the correct HQL would be greatly appreciated.
Read this: http://hibernate.org/42.html

Change your query so it's something like this:

"from WorkItem wi where wi.yourProperty in (:org_list)"

which you can load with session.getNamedQuery(queryName) or however you want to load this query. Then, set your query parameter list as:

query.setParameterList("org_list", secondaryOrganisations);

Hibernate should be able to translate between the secondaryOrganisations object and the ids necessary for the query in this case.

Hope this helps,
Justin

Hello,

Thanks for your post however that doesn't solve my problem. The desired action is something like this:

* I have an organisation - we'll call it org1
* For every workitem in the database (there are 100s), get the secondaryOrganisation
* If the secondary organisation contains my seraching organisation - org1 then add the workitem to the list of items to be returned
* then return the list of workitems

So there are many secondaryOrganisations linked to the workitems and I don't have the secondaryOrganisations as a static list but instead I want to search through all of the collections in the system.

Thanks, for your post and assistance.

Cheers,

Neil

Hello,

Just pinging this to see if anyone can help?? The basic problem is that I have a class which has a collection of items. I have an example of an item and I want to know instances of the class have my exmaple in their collections. The HQL query (I think would look like:

from WorkItem wi where ? in wi.secondaryOrganisations

In this case WorkItem is the class and secondaryOrganisations is the collection. However executing this query gives a bizarre query as follows:

select workitem0_.ID as ID12_, workitem0_1_.VERSION as VERSION12_, workitem0_1_.NAME as NAME12_, workitem0_1_.DESCRIPTION as DESCRIPT4_12_, workitem0_1_.CODE as CODE12_, workitem0_1_.TYPE as TYPE12_, workitem0_.END_DATE as END2_18_, workitem0_.START_DATE as START3_18_, workitem0_.LEAD_ORGANISATION as LEAD4_18_, case when workitem0_2_.ID is not null then 3 when workitem0_.ID is not null then 2 end as clazz_ from WORKITEM workitem0_ inner join PROJECT_COMPONENT workitem0_1_ on workitem0_.ID=workitem0_1_.ID left outer join SIMPLEWORKITEM workitem0_2_ on workitem0_.ID=workitem0_2_.ID, SECONDARY_ORGANISATIONS secondaryo1_, ORGANISATION organisati2_ where workitem0_.ID=secondaryo1_.WORKITEM_ID and secondaryo1_.ORGANISATION_ID=organisati2_.ID and (? in (.))

The problem with this is the bit at teh end which is (.) - that is not valid SQL and causes an invalid SQL error. I can write the SQL for this pretty easily, all I need to write is (here I will use the IDs for the object):

select wi.id from workitem wi, secondary_organisations so, organisation o where wi.id = wo.workitem_id and so.organisation_id=o.id and o.id=?

Here ? is the id of the organisation I am querying. The thing is that I'm stumped - what should be a fairly easy and common query is _so_ difficult to get working and I cannot find anyone who has managed this. That can't be true, so any help anyone can provide would be greatly received.

Cheers,

Neil

Cheers,

Neil