ok, so here are the details of my new problem. essentially it's a many-to-many reference (ProtProtInteraction.evidence) to another many-to-many (Evidence.references). the latter is has 2 subclasses (LiteratureReference and BookReference).
what is hibernate 3 head?
thanks,
sara
--------------------------------------------------
This is the code i'm trying to execute:
Code:
try{
references=session.createCriteria(Reference.class)
.add(Expression.like("authors", first_author))
.list();
logger.info("got "+references.size()+" items for author "+first_author);
for(int i=0;i<references.size();i++)
{
Query hq=session.createQuery("select distinct ppi from ProtProtInteraction ppi inner join ppi.evidence evid where ? in elements(evid.references)");
hq.setEntity(0,references.get(0));
list.addAll(hq.list());
}
}
catch(Exception e){
e.printStackTrace();
}
and here are the mapping files. our architecture depends on the many-to-many tables we have (so it's impossible to remove them).
first the common package file:
Code:
<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate//Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="ca.mcgill.mcb.bias.hibernate.common">
<!--Litterature stuff-->
<class name="Reference" table="Reference">
<id name="id" column="ref_id" type="integer">
<generator class="increment"/>
</id>
<property name="authors" column="authors" type="string" />
<property name="title" column="title" type="string" />
<property name="year" column="year" type="int" />
<property name="url" column="URL" type="string" />
<property name="comment" column="comment" type="string" />
<set name="evidences" lazy="true" table="Reference2Evidence">
<key column="referenceId"/>
<many-to-many
class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId" />
</set>
<set name="techniques" table="Reference2Technique" inverse="false"
lazy="true">
<key column="referenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Technique"
column="techniqueId"/>
</set>
<joined-subclass name="LiteratureReference" table="LiteratureReference">
<key column="ref_id"/>
<property name="pubmed" column="pubmed_id" type="long" />
<property name="doi" column="doi" type="string"/>
<property name="journal" column="journal" type="string"/>
<property name="volume" column="volume" type="string"/>
<property name="pages" column="pages" type="string"/>
<property name="issue" column="issue" type="string"/>
</joined-subclass>
<joined-subclass name="BookReference" table="BookReference">
<key column="ref_id"/>
<property name="publisher" column="publisher" type="string"/>
<property name="publisher_location" column="publisher_location"
type="string"/>
<property name="isbn" column="isbn" type="string"/>
<property name="page_no" column="page_no" type="int"/>
</joined-subclass>
</class>
<!--Evidence stuff-->
<class name="Evidence" table="Evidence">
<id name="evidenceId" column="evidenceId" type="integer">
<generator class="increment"/>
</id>
<set name="references" inverse="true" lazy="true" table="Reference2Evidence">
<key column="evidenceId"/>
<many-to-many
class="ca.mcgill.mcb.bias.hibernate.common.Reference"
column="referenceId" />
</set>
<many-to-one name="technique"
class="ca.mcgill.mcb.bias.hibernate.common.Technique"
column="techniqueId"
/>
<many-to-one name="parameters"
class="ca.mcgill.mcb.bias.hibernate.common.TechniqueParameters"
column="parametersId"
/>
<property name="score"
column="score"
type="double"
/>
<!-- many-to-one name="evidenceType"
class="ca.mcgill.mcb.bias.hibernate.common.EvidenceType"
column="evidenceType"
/ -->
<property name="comment"
column="comment"
type="string"
/>
<!--we need to add these reverse pointers to update correctly-->
<set name="matrices" inverse="true" lazy="true" table="Evidence2Matrix">
<key column="evidenceId"/>
<many-to-many
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="matrixID" />
</set>
<set name="protProtInteractions" table="Evidence2ProtProtInteraction"
lazy="true">
<key column="evidenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
column="protProtInteractionId"/>
</set>
<set name="complexes" table="Evidence2Complex"
lazy="true">
<key column="evidenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.sig.Complex"
column="complexId"/>
</set>
<set name="interactionControls" table="Evidence2InteractionControl"
lazy="true">
<key column="evidenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.sig.InteractionControl"
column="interactionControlId"/>
</set>
<set name="maskedDNASequences" table="Evidence2MaskedDNASequence" inverse="true"
lazy="true">
<key column="evidenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.MaskedDNASequence"
column="maskedDNASequenceId"/>
</set>
<set name="geneticInteractionDescriptions" table="Evidence2GeneticInteractionDescription"
lazy="true">
<key column="evidenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.genetic.GeneticInteractionDescription"
column="geneticInteractionDescriptionId"/>
</set>
<!--set name="protDNAInteractionEffects" table="Evidence2ProtDNAInteractionEffect"
lazy="true">
<key column="evidenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.protDNA.ProtDNAInteractionEffect"
column="protDNAInteractionEffectId"/>
</set-->
<set name="modules" table="Evidence2Module" inverse="true"
lazy="true">
<key column="evidenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.protDNA.Module"
column="moduleId"/>
</set>
<set name="proteinDNAInteractions" table="Evidence2ProteinDNAInteraction" inverse="true" lazy="true">
<key column="evidenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.protDNA.ProteinDNAInteraction"
column="pdiId"/>
</set>
<set name="protGeneRegulations" table="Evidence2ProtGeneRegulation" inverse="true"
lazy="true">
<key column="evidenceId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.bio.protDNA.ProtGeneRegulation"
column="protGeneRegulationId"/>
</set>
</class>
<class name="Technique" table="Technique">
<id name="id" column="ID" type="integer">
<generator class="increment" />
</id>
<property name="name" column="name" type="string" />
<property name="description" column="description" type="string" />
<many-to-one name="techniqueType"
class="ca.mcgill.mcb.bias.hibernate.common.TechniqueType"
column="type_id" cascade="save-update" />
<many-to-one name="relatedTechnique"
class="ca.mcgill.mcb.bias.hibernate.common.Technique"
column="techniqueId"
/>
<set name="references" table="Reference2Technique" inverse="true"
lazy="true">
<key column="techniqueId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Reference"
column="referenceId"/>
</set>
</class>
<class name="Reference2Technique" table="Reference2Technique">
<composite-id>
<key-many-to-one name="reference" class="ca.mcgill.mcb.bias.hibernate.common.Reference" column="referenceId"/>
<key-many-to-one name="technique" class="ca.mcgill.mcb.bias.hibernate.common.Technique" column="techniqueId"/>
</composite-id>
</class>
<class name="TechniqueType" table="TechniqueType">
<id name="id" column="ID" type="integer">
<generator class="increment"/>
</id>
<property name="name" column="name" type="string" />
</class>
<class discriminator-value="0" name="TechniqueParameters" table="TechniqueParameters">
<id name="ID" column="ID" type="integer" unsaved-value="null">
<generator class="increment" />
</id>
<discriminator column="technique_id" type="integer" />
<many-to-one name="technique"
class="ca.mcgill.mcb.bias.hibernate.common.Technique"
column="technique_id"
insert="false" update="false" />
<subclass name="TransfacConvParameters3" discriminator-value="3" lazy="true">
<many-to-one name="originalMatrix"
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="intPar1"/>
</subclass>
<subclass name="TransfacConvParameters4" discriminator-value="4" lazy="true">
<many-to-one name="originalMatrix"
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="intPar1"/>
</subclass>
<subclass name="TransfacConvParameters5" discriminator-value="5" lazy="true">
<many-to-one name="originalMatrix"
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="intPar1"/>
</subclass>
<subclass name="TransfacConvParameters6" discriminator-value="6" lazy="true">
<many-to-one name="originalMatrix"
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="intPar1"/>
</subclass>
<subclass name="TransfacConvParameters7" discriminator-value="7" lazy="true">
<many-to-one name="originalMatrix"
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="intPar1"/>
</subclass>
<subclass name="TransfacConvParameters8" discriminator-value="8" lazy="true">
<many-to-one name="originalMatrix"
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="intPar1"/>
</subclass>
<subclass name="SimMatGenWideParameters" discriminator-value="23" lazy="true">
<many-to-one name="matrix"
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="intPar1"/>
</subclass>
<subclass name="SimMatPromRegParameters" discriminator-value="24" lazy="true">
<many-to-one name="matrix"
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="intPar1"/>
<property name="upstreamLength"
type="java.lang.Integer"
column="intPar2"/>
<property name="downstreamLength"
type="java.lang.Integer"
column="intPar3"/>
</subclass>
<subclass name="OneMCnMMSymGRE" discriminator-value="25" lazy="true">
<many-to-one name="matrix"
class="ca.mcgill.mcb.bias.hibernate.transfac.Matrix"
column="intPar1"/>
<property name="mismatches"
type="java.lang.Integer"
column="intPar2"/>
</subclass>
<subclass name="LimmaParameters" discriminator-value="14" lazy="true">
<property name="s2prior"
column="par1"
type="ca.mcgill.mcb.bias.hibernate.Microarray.DoubleType"
/>
<property name="varprior"
column="par2"
type="ca.mcgill.mcb.bias.hibernate.Microarray.DoubleType"
/>
<property name="dfprior"
column="par3"
type="ca.mcgill.mcb.bias.hibernate.Microarray.DoubleType"
/>
<property name="proportion"
column="par4"
type="ca.mcgill.mcb.bias.hibernate.Microarray.DoubleType"
/>
</subclass>
</class>
<class name="Reference2Evidence" table="Reference2Evidence">
<composite-id>
<key-many-to-one name="reference"
class="ca.mcgill.mcb.bias.hibernate.common.Reference"
column="referenceId"/>
<key-many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"/>
</composite-id>
</class>
</hibernate-mapping>
and below is the mapping file for the sig.* package:
Code:
<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="ca.mcgill.mcb.bias.hibernate.sig">
<class name="ProtInteractorState" table="SIG_ProtInteractorState">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<many-to-one name="interactor"
class="ca.mcgill.mcb.bias.hibernate.sig.Interactor"
column="interactorId"
/>
<property name="name"
column="name"
type="string"
/>
<many-to-one name="compartment"
class="ca.mcgill.mcb.bias.hibernate.sig.CellCompartment"
column="compartment"
/>
<set name="interactions" table="SigInteractionPartners" lazy="true"
inverse="false">
<key column="interactorId"/>
<many-to-many
class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
column="interactionId"/>
</set>
</class>
<!--class name="EvidenceSet" table="SIG_EvidenceSet">
<composite-id>
<key-property name="id" column="id" type="integer"/>
<key-many-to-one name="evidenceId"
class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"
/>
</composite-id>
</class-->
<class name="Modification" table="SIG_Modification">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<property name="position"
column="position"
type="integer"
/>
<many-to-one name="modificationType"
class="ca.mcgill.mcb.bias.hibernate.sig.ModificationType"
column="modificationType"
/>
<property name="necessary"
column="necessary"
type="boolean"
/>
<property name="sufficient"
column="sufficient"
type="boolean"
/>
</class>
<class name="ProtProtInteraction" table="SIG_ProtProtInteraction">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<set name="evidence" table="Evidence2ProtProtInteraction" inverse="true"
lazy="true">
<key column="protProtInteractionId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"/>
</set>
<!--many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.EvidenceSet"
column="evidence"/-->
<!--many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.EvidenceSet">
<column name="id"/>
<column name="evidenceId"/>
</many-to-one-->
<property name="description"
column="description"
type="string"
/>
<property name="confidence"
column="confidence"
type="string"
/>
<set name="interactors" table="SigInteractionPartners" lazy="true"
inverse="true">
<key column="interactionId"/>
<many-to-many
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="interactorId"/>
</set>
</class>
<class name="Evidence2ProtProtInteraction" table="Evidence2ProtProtInteraction">
<composite-id>
<key-many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.Evidence" column="evidenceId"/>
<key-many-to-one name="protProtInteraction"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
column="protProtInteractionId"/>
</composite-id>
</class>
<class name="CellCompartment" table="SIG_CellCompartment">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<property name="name"
column="name"
type="string"
/>
</class>
<class name="InteractionPartners" table="SIG_interactionPartners">
<composite-id>
<key-many-to-one name="interactionId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
column="interactionId"
/>
<key-many-to-one name="interactorId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="interactorId"
/>
</composite-id>
</class>
<class name="Dbxref" table="SIG_dbxref">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<property name="xrefkey"
column="xrefkey"
type="string"
/>
<property name="xref_dbname"
column="xref_dbname"
type="string"
/>
<property name="xref_desc"
column="xref_desc"
type="string"
/>
</class>
<class name="InteractionControl" table="SIG_InteractionControl">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<many-to-one name="PPInteractionID"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction"
column="PPInteractionID"
/>
<many-to-one name="controllerId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="controllerId"
/>
<many-to-one name="controlType"
class="ca.mcgill.mcb.bias.hibernate.sig.ControlType"
column="controlType"
/>
<property name="necessary"
column="necessary"
type="boolean"
/>
<property name="sufficient"
column="sufficient"
type="boolean"
/>
<!--the many versions of the evidence system...-->
<set name="evidence" table="Evidence2InteractionControl" inverse="true" lazy="true">
<key column="interactionControlId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"/>
</set>
</class>
<class name="Evidence2InteractionControl"
table="Evidence2InteractionControl">
<composite-id>
<key-many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.Evidence" column="evidenceId"/>
<key-many-to-one name="interactionControl"
class="ca.mcgill.mcb.bias.hibernate.sig.InteractionControl" column="interactionControlId"/>
</composite-id>
</class>
<class name="ControlType" table="SIG_ControlType">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<property name="name"
column="name"
type="string"
/>
</class>
<class name="StateModifications" table="SIG_stateModifications">
<composite-id>
<key-many-to-one name="protStateId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="protStateId"
/>
<key-many-to-one name="modificationId"
class="ca.mcgill.mcb.bias.hibernate.sig.Modification"
column="modificationId"
/>
</composite-id>
</class>
<class name="Interactor" table="SIG_Interactor">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<property name="name"
column="name"
type="string"
/>
<set name="dbxref" table="Dbxref2Interactor" inverse="true" lazy="true">
<key column="interactorId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.sig.Dbxref"
column="dbxrefId"/>
</set>
<set name="interactorStates" lazy="true" inverse="false">
<key column="interactorId"/>
<one-to-many class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"/>
</set>
</class>
<class name="DbxrefSet2Interactor" table="dbxrefSet2Interactor">
<composite-id>
<key-many-to-one name="dbxref"
class="ca.mcgill.mcb.bias.hibernate.sig.Dbxref" column="dbxrefSetId"/>
<key-many-to-one name="interactor"
class="ca.mcgill.mcb.bias.hibernate.sig.Interactor" column="interactorId"/>
</composite-id>
</class>
<class name="ModificationType" table="SIG_ModificationType">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<set name="dbxref" table="Dbxref2ModificationType" inverse="true"
lazy="true">
<key column="modificationTypeId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"/>
</set>
<property name="name"
column="name"
type="string"
/>
</class>
<class name="Dbxref2ModificationType" table="Dbxref2ModificationType">
<composite-id>
<key-many-to-one name="dbxref"
class="ca.mcgill.mcb.bias.hibernate.sig.Dbxref" column="dbxrefId"/>
<key-many-to-one name="modificationType"
class="ca.mcgill.mcb.bias.hibernate.sig.ModificationType" column="modificationTypeId"/>
</composite-id>
</class>
<class name="Complex" table="SIG_Complex">
<id name="id" column="id" type="integer">
<generator class="increment"/>
</id>
<!--many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.EvidenceSet">
<column name="id"/>
<column name="evidenceId"/>
</many-to-one-->
<!--many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.EvidenceSet"
column ="evidence"/-->
<set name="evidence" table="Evidence2Complex" inverse="true" lazy="true">
<key column="complexId"/>
<many-to-many class="ca.mcgill.mcb.bias.hibernate.common.Evidence"
column="evidenceId"/>
</set>
</class>
<class name="Evidence2Complex" table="Evidence2Complex">
<composite-id>
<key-many-to-one name="evidence"
class="ca.mcgill.mcb.bias.hibernate.common.Evidence" column="evidenceId"/>
<key-many-to-one name="complex"
class="ca.mcgill.mcb.bias.hibernate.sig.Complex" column="complexId"/>
</composite-id>
</class>
<class name="ComplexMembership" table="SIG_complexMembership">
<composite-id>
<key-many-to-one name="complexId"
class="ca.mcgill.mcb.bias.hibernate.sig.Complex"
column="complexId"
/>
<key-many-to-one name="memberId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="memberId"
/>
</composite-id>
</class>
<class name="InteractionResults" table="SIG_interactionResults">
<composite-id>
<key-many-to-one name="protStateId"
class="ca.mcgill.mcb.bias.hibernate.sig.ProtInteractorState"
column="protStateId"
/>
<key-many-to-one name="modificationId"
class="ca.mcgill.mcb.bias.hibernate.sig.Modification"
column="modificationId"
/>
</composite-id>
</class>
</hibernate-mapping>
here is the stack trace
the exception:org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1364)
at org.hibernate.loader.Loader.list(Loader.java:1344)
at org.hibernate.loader.QueryLoader.list(QueryLoader.java:349)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:265)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:994)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at ca.mcgill.mcb.bias.modules.pimp.ERData.getInteractions(ERData.java:254)
at ca.mcgill.mcb.bias.modules.pimp.ERData.<init>(ERData.java:109)
at ca.mcgill.mcb.bias.modules.pimp.ERStatistics.<init>(ERStatistics.java:37)
at ca.mcgill.mcb.bias.modules.pimp.ERStatistics.main(ERStatistics.java:166)
Caused by: java.sql.SQLException: Unknown column 'reference4_.referenceId' in 'field list'
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2247)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:108)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1109)
at org.hibernate.loader.Loader.doQuery(Loader.java:349)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:195)
at org.hibernate.loader.Loader.doList(Loader.java:1361)
... 9 more
there is ALOT of debug output, but since the problem is in the query formation, here is the original HQL:
53565 DEBUG QueryTranslatorImpl - parse() - HQL: select distinct ppi from ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction ppi inner join ppi.evidence evid where ? in elements(evid.references)
53582 DEBUG AST - --- HQL AST ---
\-'query' [QUERY]
+-'SELECT_FROM' [SELECT_FROM]
| +-'from' [FROM]
| | +-'.' [DOT]
| | | +-'.' [DOT]
| | | | +-'.' [DOT]
| | | | | +-'.' [DOT]
| | | | | | +-'.' [DOT]
| | | | | | | +-'.' [DOT]
| | | | | | | | +-'ca' [IDENT]
| | | | | | | | \-'mcgill' [IDENT]
| | | | | | | \-'mcb' [IDENT]
| | | | | | \-'bias' [IDENT]
| | | | | \-'hibernate' [IDENT]
| | | | \-'sig' [IDENT]
| | | \-'ProtProtInteraction' [IDENT]
| | +-'ppi' [ALIAS]
| | \-'join' [JOIN]
| | +-'inner' [INNER]
| | +-'.' [DOT]
| | | +-'ppi' [IDENT]
| | | \-'evidence' [IDENT]
| | \-'evid' [ALIAS]
| \-'select' [SELECT]
| +-'distinct' [DISTINCT]
| \-'ppi' [IDENT]
\-'where' [WHERE]
\-'in' [IN]
+-'?' [PARAM]
\-'inList' [IN_LIST]
\-'elements' [ELEMENTS]
\-'.' [DOT]
+-'evid' [IDENT]
\-'references' [IDENT]
and here is the final query:
53672 DEBUG AST - --- SQL AST ---
\-'SELECT' [SELECT] querySpaces (Reference2Evidence,Evidence2ProtProtInteraction,SIG_ProtProtInteraction,Evidence,Reference)
+-'{select clause}' [SELECT_CLAUSE]
| +-'distinct' [DISTINCT]
| +-'protprotin0_.id as id' [ALIAS_REF] {alias=ppi, className=ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction, tableAlias=protprotin0_}
| \-'protprotin0_.description as descript2_177_, protprotin0_.confidence as confidence177_' [SQL_TOKEN]
+-'from' [FROM] FromClause{from}
| \-'SIG_ProtProtInteraction protprotin0_' [FROM_FRAGMENT] FromElement{className=ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction,explicit,not a collection join,classAlias=ppi,tableName=SIG_ProtProtInteraction,tableAlias=protprotin0_,colums={}}
| \-'inner join Evidence2ProtProtInteraction evidence1_ on protprotin0_.id=evidence1_.protProtInteractionId inner join Evidence evidence2_ on evidence1_.evidenceId=evidence2_.evidenceId' [JOIN_FRAGMENT] FromElement{className=ca.mcgill.mcb.bias.hibernate.common.Evidence,explicit,not a collection join,classAlias=evid,tableName=Evidence,tableAlias=evidence2_,colums={protprotin0_.id }}
\-'where' [WHERE]
\-'in' [IN]
+-'?' [PARAM]
\-'inList' [IN_LIST]
\-'SELECT' [SELECT] querySpaces (Reference2Evidence,Evidence2ProtProtInteraction,SIG_ProtProtInteraction,Evidence,Reference)
+-'{select clause}' [SELECT_CLAUSE]
| \-'(select reference4_.referenceId from Reference2Evidence references3_ where evidence2_.evidenceId=references3_.evidenceId)' [SQL_TOKEN] {method=elements,selectColumns=[(select reference4_.referenceId from Reference2Evidence references3_ where evidence2_.evidenceId=references3_.evidenceId)],fromElement=FromElement{<1> tableAlias='reference4_', className='ca.mcgill.mcb.bias.hibernate.common.Reference', classAlias='null', role='null', includeSubclasses=true, collectionJoin=false, fetch=false, origin=FromElement{<0> tableAlias='references3_', className='ca.mcgill.mcb.bias.hibernate.common.Reference', classAlias='null', role='ca.mcgill.mcb.bias.hibernate.common.Evidence.references', includeSubclasses=true, collectionJoin=false, fetch=false, origin=FromElement{<1> tableAlias='evidence2_', className='ca.mcgill.mcb.bias.hibernate.common.Evidence', classAlias='evid', role='ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction.evidence', includeSubclasses=false, collectionJoin=false, fetch=false, origin=FromElement{<0> tableAlias='protprotin0_', className='ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction', classAlias='ppi', role='null', includeSubclasses=true, collectionJoin=false, fetch=false, origin=null, persister=SingleTableEntityPersister(ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction), queryableCollection=null, joinSequence=null, columns=null}, persister=SingleTableEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Evidence), queryableCollection=BasicCollectionPersister(ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction.evidence), joinSequence=JoinSequence{->BasicCollectionPersister(ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction.evidence)[evidence1_]->SingleTableEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Evidence)[evidence2_]}, columns=[protprotin0_.id]}, persister=JoinedSubclassEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Reference), queryableCollection=BasicCollectionPersister(ca.mcgill.mcb.bias.hibernate.common.Evidence.references), joinSequence=JoinSequence{->BasicCollectionPersister(ca.mcgill.mcb.bias.hibernate.common.Evidence.references)[references3_]}, columns=[evidence2_.evidenceId]}, persister=JoinedSubclassEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Reference), queryableCollection=null, joinSequence=JoinSequence{->JoinedSubclassEntityPersister(ca.mcgill.mcb.bias.hibernate.common.Reference)[reference4_]}, columns=[references3_.referenceId]}}
| \-'.' [DOT] {propertyName=references,dereferenceType=DEREF_COLLECTION,propertyPath=references,path=evid.references,tableAlias=references3_,className=ca.mcgill.mcb.bias.hibernate.common.Reference,classAlias=ca.mcgill.mcb.bias.hibernate.common.Reference}
| +-'evidence2_.evidenceId' [ALIAS_REF] {alias=evid, className=ca.mcgill.mcb.bias.hibernate.common.Evidence, tableAlias=evidence2_}
| \-'references' [IDENT] {originalText=references}
+-'{from}' [FROM] FromClause{{from}}
| +-'Reference references3_' [JOIN_FRAGMENT] FromElement{className=ca.mcgill.mcb.bias.hibernate.common.Reference,implied,not a collection join,classAlias=ca.mcgill.mcb.bias.hibernate.common.Reference,tableName=Reference,tableAlias=references3_,colums={evidence2_.evidenceId }}
| \-'Reference reference4_' [JOIN_FRAGMENT] FromElement{className=ca.mcgill.mcb.bias.hibernate.common.Reference,implied,not a collection join,classAlias=ca.mcgill.mcb.bias.hibernate.common.Reference,tableName=Reference,tableAlias=reference4_,colums={references3_.referenceId }}
\-'WHERE' [WHERE]
\-'{and}' [SYNTHETIC_AND] MULTIPLE_FRAGMENTS FromElement{className=ca.mcgill.mcb.bias.hibernate.common.Reference,implied,not a collection join,classAlias=ca.mcgill.mcb.bias.hibernate.common.Reference,tableName=Reference,tableAlias=reference4_,colums={references3_.referenceId }}
+-'evidence2_.evidenceId=references3_.evidenceId' [SQL_TOKEN]
\-'references3_.referenceId=reference4_.ref_id' [SQL_TOKEN]
53672 DEBUG parser - throwQueryException() : no errors
53684 DEBUG QueryTranslatorImpl - HQL: select distinct ppi from ca.mcgill.mcb.bias.hibernate.sig.ProtProtInteraction ppi inner join ppi.evidence evid where ? in elements(evid.references)
53684 DEBUG QueryTranslatorImpl - SQL: select distinct protprotin0_.id as id, protprotin0_.description as descript2_177_, protprotin0_.confidence as confidence177_ from SIG_ProtProtInteraction protprotin0_ inner join Evidence2ProtProtInteraction evidence1_ on protprotin0_.id=evidence1_.protProtInteractionId inner join Evidence evidence2_ on evidence1_.evidenceId=evidence2_.evidenceId where (? in(select (select reference4_.referenceId from Reference2Evidence references3_ where evidence2_.evidenceId=references3_.evidenceId) from Reference references3_, Reference reference4_ where evidence2_.evidenceId=references3_.evidenceId and references3_.referenceId=reference4_.ref_id))