How to left or right join in criteria query in grails

Question

I am using grails 2.1.1. Here I have 2 domain. One is -InvIssue- and other is -SlsDoMst-. There is a foreign key of SlsDoMst in InvIssue domain. Now I need to find all the SlsDoMst row that is not in InvIssue table. So far I am using plain sql for now as follows ::

my oracle query >>>

    SELECT MS. *
FROM SLS_DO_MST MS
LEFT OUTER JOIN INV_ISSUE ISS ON MS.MID = ISS.SLS_DO_MST_MID

But I need to do this with criteria query. My domain are as follows ::

InvIssue >>>

    class InvIssue{
    String clnCode
    Long id
    SlsDoMst slsDoMst
    String remk
}

SlsDoMst >>>

    class SlsDoMst {
    Long id
    String code
}

I think it better to write in HQL. Criteria better to use for simple things - it's do code more pretty, but if you want to use JOINs better to use HQL it will be more clear — Koloritnij, Jan 14 '16 at 09:27
its possible with relationships using hasMany or hasOne. But in my case there is no hasMany or hasOne — Sumon Bappi, Jan 14 '16 at 09:36

score 0 · Answer 1 · answered Jan 14 '16 at 10:06

I have done it with hql and gorm query as follows. Hope it will be usefull >>>

def ids = inv.InvIssue.executeQuery("select iss.slsDoMst.id From inv.InvIssue iss where iss.slsDoMst.id is not null")
    def list = SlsDoMst.findAll()
    if(ids.size() > 0){
        list = SlsDoMst.findAllByIdNotInList(ids)
    }

score 0 · Answer 2 · answered Jan 14 '16 at 17:09

0

This might work:

import static org.hibernate.sql.JoinType.*

def list = InvIssue.withCriteria {
    createAlias 'slsDoMst', 'mst', RIGHT_OUTER_JOIN

    projections {
        property 'mst'
    }

    isNull 'id'
}

I'm just not sure if the projection will work. If not, you can use this HQL:

select b from InvIssue as a right outer join a.slsDoMst as b where a.id is null

answered Jan 14 '16 at 17:09

Emmanuel Rosa

9,697
2
14
20

this message is shown :: `join type not supported by OracleJoinFragment (use Oracle9iDialect/Oracle10gDialect)` – Sumon Bappi Jan 17 '16 at 06:48

How to left or right join in criteria query in grails

2 Answers2