Left Joins in Grails

Question

Hi,

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'
        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

Answer 1

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

Answer 2

Hi, I tried with your solution but no luck , showing same error. Could you please refer my sql i edited now . This is the actual sql and domain classes i am using

Answer 3

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...

Answer 4

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