convert from SQL to HQL gives error Grails

Question

I am trying to convert this sql to hql to fetch the data from artifact table. I have this sql query which works fine on the database

select a.*
from classification c 
     join (select id from taxonomy_node start with id = 5067 connect by nocycle prior id = parent_id) h
     on (c.node_id = h.id)
     join artifact a on (a.id = c.artifact_id)
     Where a.DOCUMENT_ID = 10462 AND c.active=1

I am trying to convert it to HQL

artifacts = Artifact.executeQuery("FROM classification c "+
                                        "JOIN (SELECT id FROM taxonomy_node START WITH id = :nodeId "+
                                        "CONNECT BY NOCYCLE PRIOR id = parent_id) h "+
                                        "ON (c.node_id = h.id) "+
                                        "JOIN artifact a ON (a.id = c.artifactId) "+
                                        "WHERE a.DOCUMENT_ID = :docid AND c.active=1",
                                        [nodeId: NodeId ,docid: document.id],
                                        [max:limit, offset:startIndex])

The domain classes look like Classification

class Classification {

    public static final String USER_DEFAULT = "USER"
    public static final String USER_SYSTEM = "SYSTEM"

    TaxonomyNode node
    String artifactId //TODO can we drive this with an annotated object or something?
    Boolean active
    String createdBy
    String updatedBy
    Date dateCreated
    Date lastUpdated
    // or should we create an Artifact Toolkit plugin?

    static constraints = {
        node nullable:false, blank:false
        artifactId nullable:false, blank:false, unique: ['node']
        active nullable: false, blank: false
        createdBy nullable:false, blank:false
        updatedBy nullable:false, blank:false
    }

    static mapping = {
        id generator:'sequence', params:[sequence:'classification_seq']
        artifactId index: 'classify_by_artifact_node'
        node index: 'classify_by_artifact_node'
        active defaultValue: "1"
    }
}

Artifact looks like

class Artifact {

    public enum ArtifactType {
        CLAUSE("Clause"),
        TITLE("Title"),
        DEFINED_TERMS("Defined Terms")

        private final String value

        ArtifactType(String value) { this.value = value }
        String toString() { value }
        String getKey() { name() }
    }

    public enum ArtifactStatus {
        ALL("All"),
        REVIEWED("Reviewed"),
        FOR_REVIEW("For Review"),
        UNCLASSIFIED("Unclassified")

        private final String value

        ArtifactStatus(String value) { this.value = value }
        String toString() { value }
        String getKey() { name() }
    }

    Document document
    String artifactType
    String text
    String status
    String createdBy
    String updatedBy
    Date dateCreated
    Date lastUpdated

    static belongsTo = Document
    static hasMany = [classification: Classification]

    static constraints = {
        artifactType maxSize: 50, nullable: false, blank: false
        text nullable: false, blank: false, maxSize: 4000
        status nullable:true, blank:true
        createdBy nullable:false, blank:false
        updatedBy nullable:false, blank:false
    }

    static mapping = {
        id generator:'sequence', params:[sequence:'artifact_seq']
        document index: 'artifact_by_doc'
        artifactType index: 'artifact_by_doc,artifact_by_type'
        text index: 'artifact_by_doc,artifact_by_text'
    }
}

the taxonomy Node looks like

class TaxonomyNode {

    public static final String USER_DEFAULT = "USER"
    public static final String USER_SYSTEM = "SYSTEM"

    String hierarchyId
    String nodeId
    String label
    String inputFormat
    Boolean active
    String createdBy
    String updatedBy
    Date dateCreated
    Date lastUpdated

    static hasMany = [metadata: TaxonomyNodeMetadata]

    TaxonomyNode parent

    static constraints = {
        inputFormat nullable:true, blank:true //TODO for now we want to allow null/blank for user-entered nodes
        parent nullable:true //TODO we should enforce that only one node per hierarchyId can have a null parent
        hierarchyId nullable:false, blank:false //TODO we should enforce the parent's hierarchyId is the same
        active nullable: false, blank: false
        nodeId nullable:false, blank:false, unique: ['hierarchyId']
        label nullable:true, blank:true
        createdBy nullable:false, blank:false
        updatedBy nullable:false, blank:false
    }

    static mapping = {
        id generator:'sequence', params:[sequence:'taxonomy_node_seq']
        hierarchyId index: 'taxonomy_by_hier'
        label index: 'taxonomy_by_hier,taxonomy_by_label'
        parent index: 'taxonomy_by_hier,taxonomy_by_parent'
        active defaultValue: "1"
    }
}

I am getting this error

Error |
2014-10-22 09:22:38,633 [http-bio-8080-exec-4] ERROR ast.ErrorCounter  - line 1:28: unexpected token: (
Error |
2014-10-22 09:22:38,634 [http-bio-8080-exec-4] ERROR ast.ErrorCounter  - line 1:28: unexpected token: (
Message: unexpected token: (
    Line | Method
->>  281 | $tt__index in com.ald.aeandsdx.ArtifactController
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    198 | doFilter   in grails.plugin.cache.web.filter.PageFragmentCachingFilter
|     63 | doFilter . in grails.plugin.cache.web.filter.AbstractFilter
|   1145 | runWorker  in java.util.concurrent.ThreadPoolExecutor
|    615 | run . . .  in java.util.concurrent.ThreadPoolExecutor$Worker
^    745 | run        in java.lang.Thread
Error |
org.springframework.orm.hibernate4.HibernateQueryException: unexpected token: ( near line 1, column 28 [FROM classification c JOIN (SELECT id FROM taxonomy_node START WITH id = :nodeId CONNECT BY NOCYCLE PRIOR id = parent_id) hON (c.node_id = h.id) JOIN artifact a ON (a.id = c.artifactId) WHERE a.DOCUMENT_ID = :docid AND c.active=1]; nested exception is org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 28 [FROM classification c JOIN (SELECT id FROM taxonomy_node START WITH id = :nodeId CONNECT BY NOCYCLE PRIOR id = parent_id) hON (c.node_id = h.id) JOIN artifact a ON (a.id = c.artifactId) WHERE a.DOCUMENT_ID = :docid AND c.active=1]

Answer 1

HQL supports subqueries only in SELECT and WHERE clauses, see: http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-subqueries