Join SQL Operation in hibernate/goram using grails framework?

Question

Hi I am trying to join two same tables - The sql query is working fine . but when i trying the same in code using groovy and hibernate i am getting below exception .

    2015-10-15 16:35:21,084 [http-8083-3] ERROR hql.PARSER  - line 1:62: unexpected token: ON
    2015-10-15 16:35:21,104 [http-8083-3] ERROR errors.GrailsExceptionResolver  - Exception occurred when processing request: [GET] /mips/console/admin/serviceListJSONip - parameters:
    _dc: 1444907110958
    Stacktrace follows:
    org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 62 [from com.company.test.domains.Service s  LEFT JOIN Service S2 ON s.parent_id = s2.id  where 1=1 and s.serviceType = 'SERVICE' and s.enabled = 1 and cId in (1,2,2,33) order by s.id asc ,rowId asc]
        at $Proxy74.createQuery(Unknown Source)
        at com.company.test$_closure15.doCall(AdminController.groovy:884)
        at com.company.test$_closure15.doCall(AdminController.groovy)
        at com.canvas.mips.controllers.SecureController.invokeMethod(SecureController.groovy)
        at com.company.test$_closure20.doCall(AdminController.groovy:1030)
        at com.company.test$_closure20.doCall(AdminController.groovy)
        at org.grails.jaxrs.web.JaxrsFilter.doFilterInternal(JaxrsFilter.java:46)
        at java.lang.Thread.run(Thread.java:662)
    Hibernate: 

I am using old grails version i.e. 1.3 and also here is my code snippet to so is here

results = Service.executeQuery( query.toString() , [], [max:max,offset:offset]);

select 
t1.ID,
t1.Name,
t2.Name as PARENT_Name
from SERVICE t1
left join SERVICE  t2 on t1.PARENT_ID = t2.ID
order by t1.ID

Answer 1

The problem is that DomainClass.executeQuery() executes HQL, HQL does not support self-joins, and in HQL you're dealing with classes not tables so the names are case-sensitive.

The equivalent of a self-join is an association of the same type. For example, with a Service domain class like this:

class Service {
    String Name    
    Service parent
}

You can write an HQL query like this:

select 
t1.ID,
t1.Name,
t2.Name as PARENT_Name
from Service t1
left join Service.parent t2
order by t1.ID

In HQL, the joins are created with GORM/Hibernate associations. The HQL JOIN clause only allows you to change the association type: INNER, LEFT, RIGHT, etc.