criteria uses "inner join" instead "left join" approach by default making my query work not the way I planned

Question

The question is: how do I make GORM generate left joins instead of inner joins in this particular example?

Testbed:

Given classes A, B and C:

class A{
    B someObject
}

class B{
    C importantObject
}

class C{
    boolean interestingFlag
}

I want to list all the elements of A class that:

their B.C object is null OR
their B.C object interestingFlag value is false

What I tried so far:

This approach produces correct list of A where B.C is null (conditional 2 commented out) OR correct list of A where B.C.interestingFlag = false (no matter if conditional 1 is commented out or not). When both conditionals are uncommented it returns only a list of elements where A.B.C.interestingFlag = false (A.B.C = null conditional is ignored)

// approach 1 (conditional 1 is ignored)
def result = A.withCriteria{
    someObject{
        or{
            isNull('importantObject') // conditional 1, works well when conditional 2 is commented out
            importantObject{
                eq('interestingFlag', false) // conditional 2, works well alone, discards conditional 1 when both of them are uncommented
            }
        }  
    } 
}

Edit: As requested in comment I'm pasting a hibernate generated sql:

Hibernate: select this_.id as id1_2_, this_.version as version1_2_, 
this_.some_object_id as some3_1_2_, someobject1_.id as id2_0_, 
someobject1_.version as version2_0_, someobject1_.important_object_id as 
important3_2_0_, importanto2_.id as id0_1_, importanto2_.version as version0_1_, 
importanto2_.interesting_flag as interest3_0_1_ from a this_ 
inner join b someobject1_ on this_.some_object_id=someobject1_.id 
inner join c importanto2_ on someobject1_.important_object_id=importanto2_.id 
where ((someobject1_.important_object_id is null or (importanto2_.interesting_flag=?)))

When I copy and paste it in the pgAdmin query tool directly with a few things changed (inner joins changed to left joins, and provided the interestingFlag = "false" parameter) everything works as I wanted (I get both A.B.C = null and A.B.C.importantFlag = false objects)

Hibernate: select this_.id as id1_2_, this_.version as version1_2_, 
this_.some_object_id as some3_1_2_, someobject1_.id as id2_0_, 
someobject1_.version as version2_0_, someobject1_.important_object_id as 
important3_2_0_, importanto2_.id as id0_1_, importanto2_.version as version0_1_, 
importanto2_.interesting_flag as interest3_0_1_ from a this_ 
left join b someobject1_ on this_.some_object_id=someobject1_.id 
left join c importanto2_ on someobject1_.important_object_id=importanto2_.id 
where ((someobject1_.important_object_id is null or (importanto2_.interesting_flag=false)))

Try enabling logSql=true in your data source and see what sql this qquery generates. Post it here, please. — Tomasz Kalkosiński, Jun 13 '13 at 09:11
@TomaszKalkosiński as requested here are the hibernate sql outputs. I know how to tweak the query to work as I want to. I don't know how to make GORM produce the correct query — Andrzej Bobak, Jun 13 '13 at 09:57

Andrzej Bobak · Accepted Answer · 2019-10-14T14:06:42.307

43

Tested and working solution:

    def result = A.withCriteria{
        createAlias('someObject', 'so', CriteriaSpecification.LEFT_JOIN)
        createAlias('so.importantObject', 'imp', CriteriaSpecification.LEFT_JOIN)
        or {
            isNull('so.importantObject')
            eq('imp.interestingFlag', false)
        } 

    }

Solution update as suggested in comment:

    def result = A.withCriteria{
        createAlias('someObject', 'so', JoinType.LEFT_OUTER_JOIN)
        createAlias('so.importantObject', 'imp', JoinType.LEFT_OUTER_JOIN)
        or {
            isNull('so.importantObject')
            eq('imp.interestingFlag', false)
        } 

    }

edited Oct 14 '19 at 14:06

answered Jun 17 '13 at 11:00

Andrzej Bobak

2,106
3
28
36

5

CriteriaSpecification.LEFT_JOIN has become deprecated. Use JoinType.LEFT_OUTER_JOIN for recent versions. Source: https://docs.jboss.org/hibernate/orm/5.0/javadocs/org/hibernate/criterion/CriteriaSpecification.html – Roland Mar 07 '16 at 12:51

Tomasz Kalkosiński · Answer 2 · 2013-06-14T07:11:44.860

5

Use left join to achieve this. This should work, but I haven't tested it live.

def result = A.withCriteria{
    someObject {
        createAlias("importantObject", "io", CriteriaSpecification.LEFT_JOIN)
        or{
            isNull('importantObject') // conditional 1
            eq('io.interestingFlag', false) // conditional 2            
        }  
    } 
}

Eidt: based on this post: http://grails.1312388.n4.nabble.com/CriteriaBuilder-DSL-Enhancements-td4644831.html this should also work:

def result = A.withCriteria{
    someObject {
        isNull('importantObject') // conditional 1
        importantObject(JoinType.LEFT) {
            eq('interestingFlag', false) // conditional 2
        }  
    } 
}

Please post your results on both solutions.

Edit 2: This is a query that is exactly a situation you've described, but differs from your examples. You must start from here, use showSql to debug generated SQLs and fiddle with left joins.

def result = A.withCriteria{
    or {
        isNull('someObject')
        eq('someObject.importantObject.interestingFlag', false)
    } 
}

edited Jun 14 '13 at 07:11

answered Jun 13 '13 at 10:19

Tomasz Kalkosiński

3,673
1
19
25

They both generated errors. Solution 1: Class org.hibernate.QueryException Message could not resolve property: importantObject of: A. Solution 2: groovy.lang.MissingMethodException Message No signature of method: AController.importantObject() is applicable for argument types: (javax.persistence.criteria.JoinType, AController$_list_closure1_closure2_closure3) values: [LEFT, AController$_list_closure1_closure2_closure3@40230d] – Andrzej Bobak Jun 13 '13 at 13:08
This is strange, since messages indicate errors on A object, while importantObject is in someObject closure. As for solution 1 try to createAlias on someObject and see if that helps. – Tomasz Kalkosiński Jun 13 '13 at 13:23
No luck after changing alias to createAlias("someObject.importantObject", "io", CriteriaSpecification.LEFT_JOIN). Now it can't find "io() method" – Andrzej Bobak Jun 13 '13 at 13:41
@AndrzejBobak I think there is something wrong in your question and it affects your queries too. Structure you presented doesn't match your question and queries. We can't move on without correct structure so we can fix your queries. – Tomasz Kalkosiński Jun 13 '13 at 13:46
This is the EXACT copy of what is in my sample project. Setting it up for the purpose of the question and generating the sql output took me around 10 minutes. I use Grails 2.2.1 and Postgresql 8.3. If you're interested I can send you a copy of this project but it's so tiny it can be created in a matter of a few minutes (3 classes, each with 1 field, generated views + scaffolds). I really don't understand what you mean by "structure I presented doesn't match my question and queries". – Andrzej Bobak Jun 13 '13 at 14:05
Ok, there was a mistake in the requirements I described. We only work with C class elements, we want only a list of A's whose B.C either doesn't exist or B.C.interestingFlag equals to false. But still, the third solution also doesn't work. This time it produces error: "could not resolve property: someObject.importantObject.interestingFlag of: A" – Andrzej Bobak Jun 14 '13 at 07:28
Sorry @AndrzejBobak but you have to fiddle for yourself. I can't help anymore without real examples. Use links I've posted as a reference or maybe try to change your model structure to have easier query. – Tomasz Kalkosiński Jun 14 '13 at 07:33

score 1 · Answer 3 · edited May 18 '16 at 17:22

I was able to achieve left outer joins only with HQL

Class Transaction {
    String someProperty
    static hasMany = [reviews: Review]
    static hasOne = [reviewQueue: ReviewQueue]
}

Class ReviewQueue {
   Transaction transaction
   Boolean isComplete
   Boolean isReady
}

Class Review {
  Transaction transaction
  String reviewResult
}

def list = Transaction.executeQuery(
    "select  t.id, rq.isReady, rq.isComplete, count(r.transaction.id) " +
    "from Transaction t " +
    "join t.reviewQueue rq " +
    "left outer join t.reviews r " +
    "where rq.isComplete = false " +
    "and rq.isReady = true " +
    "group by t.id " +
    "having count(r.transaction.id) = 0 " +
    "order by rq.transaction.id ",
[max: 10, offset: 0])

criteria uses "inner join" instead "left join" approach by default making my query work not the way I planned

3 Answers3

Linked