Left Joins in Grails

How to call joins in grails.Here are my Domain classes :

package com.org.domain

class CoverageList {
    Integer id
    Integer pub_cat_id
    Integer brand_id
    Integer brand_set_no
    String cov_list_desc
    Date edit_date
    Date part_edit_date
    Integer category_id


    static constraints = {
    }

    static mapping = {
        table 'coverage_list'
        version false
        id column : 'COV_LIST_ID'
        pub_cat_id column : 'PUB_CATEGORY_ID'
        brand_id column : 'BRAND_ID'
        brand_set_no column : 'BRAND_SET_NO'
        cov_list_desc column : 'COV_LIST_DESC'
        edit_date column : 'EDIT_DATE'
        part_edit_date column : 'PART_EDIT_DATE'
        category_id column : 'CATEGORY_ID'
    }
}

package com.org.domain

class AssocCovList {
    Integer id
    Integer association_id
    Integer cov_list_id
    String assoc_line_code
    Date edit_date
    Date add_date

    static constraints = {
    }

    static mapping = {
        table 'assoc_cov_list'
        version false
        id column : 'ASSOC_COV_LIST_ID'
        association_id column : 'ASSOCIATION_ID'
        cov_list_id column : 'COV_LIST_ID'
        assoc_line_code column : 'ASSOC_LINE_COD'
        edit_date column : 'EDIT_DATE'
开发者_JAVA技巧        add_date column : 'ADD_DATE'
    }
}

class Brand {
    Integer id
    String brand_name
    String aaia_brand_id
    String brand_owner_name
    String parent_company_name
    String site_link_code
    Date edit_date

    static hasMany = [cov_lists : CoverageList]

    static constraints = {
    }

    static mapping = {
        table 'brand'
        version false
        id column : 'BRAND_ID'
        brand_name column : 'BRAND_NAM'
        aaia_brand_id column : 'AAIA_BRAND_ID'
        brand_owner_name column : 'BRAND_OWNER_NAME'
        parent_company_name column : 'PARENT_COMPANY_NAME'
        site_link_code column : 'SITE_LINK_COD'
        edit_date column : 'EDIT_DATE'
    }
}

def cov_list = CoverageList.executeQuery
    ("SELECT c.id, c.cov_list_desc, b.brand_name, a.id, a.association_id, a.cov_list_id FROM CoverageList c LEFT JOIN AssocCovList a ON (c.id = a.cov_list_id AND a.association_id = 1) JOIN Brand b ON b.id= c.brand_id ORDER BY b.brand_name, c.cov_list_desc")

But i'm getting error like this :

{ERROR} portal-web.docroot.html.portal.render_portlet.jsp org.hibernate.hql.ast.QuerySyntaxException: 
    unexpected token: ON near line 1, column 187 [SELECT c.id, c.cov_list_desc, b.brand_name, a.id, a.association_id, a.cov_list_id, a.assoc_line_code, a.edit_date, a.add_date FROM com.org.domain.CoverageList c LEFT JOIN AssocCovList a ON (c.id = a.cov_list_id AND a.association_id = 1) JOIN Brand b ON b.id= c.brand_id ORDER BY b.brand_name, c.cov_list_desc]

Am i going wrong, Please any one help me .

thanks

You cannot use LEFT JOIN with 2 columns. Rewrite your query as the following: ... CoverageList c LEFT JOIN AssocCovList a ON (c.id = a.cov_list_id) WHERE a.association_id = 1

Shouldn't your last JOIN be a WHERE instead ? I would add "Brand b" to the FROM clause and replace the latest JOIN by "WHERE b.id= c.brand_id".

I didn't test though... it's just an idea...

Your code looks like Oracle SQL, but Grails uses Hibernate for ORM. Hibernate uses Hibernate Query Language (HQL) that doesn't seem to support joins followed by and ON clause. HQL: The Hibernate Query Language