2

We have a grails 2.5.5 application with hibernate4:4.3.8.1 plugin.

When we query a domain, that has a has-many relation, with criteria-api and left-join, gorm does not load all has-many associations.

Example:

class Role {
    String name
}

class User {
    String name
    static hasMany = [roles: Role]
}

Test data:

def role1 = new Role(name: "Role1").save()
def role2 = new Role(name: "Role2").save()

def user = new User(name: "User")
user.addToRoles(role1)
user.addToRoles(role2)
user.save()

When we query the result with an inner-join, it works as expected and all roles of the user are loaded:

User.withNewSession {
    def user = User.withCriteria({
        roles { 
            eq "name", "Role1" 
        }
    }).first()
    assert user.roles.size() == 2
}

But when querying with a left-join, the user result contains only the queried "Role1". "Role2" is not loaded.

User.withNewSession {
    def user = User.withCriteria({
        roles(CriteriaSpecification.LEFT_JOIN) { 
            eq "name", "Role1" 
        }
    }).first()
    assert user.roles.size() == 2 //This breaks! Only Role1 is loaded.
}

Has any one ideas why this breaks?

Note: This code does only break if the roles were not loaded before correctly. That's why I use withNewSession in this example.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
gregorr
  • 373
  • 4
  • 13

1 Answers1

0

I'm not home yet, but i think you should use alias instead of directly using the class variable, just like in this thread

Ok, i finally have some spare time to look about it more further. Sorry that my first answer doesn't help you.

The problem is that the way GORM transform your code to SQL is not what you expect.

If you try to turn the SQL log on and copy paste that into your DB, then you'll know what i meant. The query return only the entity where role.name = Role1, and thus this lead you to see that "GORM doesn't load all has-many associations".

quick fix for this is to reload the instance once more by doing user.refresh().

P.S: withNewSession won't help you nothing regarding this matter, so you may as well remove it from your code

tyrion
  • 23
  • 6
  • Thanks for your comment, but the approach has the same result: `User.withNewSession { def user = User.withCriteria({ createAlias('roles', 'rolesJoin', JoinType.LEFT_OUTER_JOIN) eq "rolesJoin.name", "Role1" }).first() assert user.roles.size() == 2 //This breaks! Only Role1 is loaded. }` – gregorr May 30 '17 at 08:16
  • Thanks for your 2nd answer. I know what you mean and I can understand why only 1 role is loaded - but imho it's a hibernate/gorm bug. I'm writting a valid criteria-statement, that returns an invalid object. Furthermore the wrong result is cached and all other statements return the same wrong result. I'm looking for a way to avoid this (in my eyes) wrong behavior. – gregorr Jun 09 '17 at 07:29
  • In case of performance issues a refresh() is not really an option. – gregorr Jun 11 '17 at 09:20
  • 1
    Then you should use HQL `User.executeQuery("select u from User u join u.roles r where r.name = 'Role1'")` – tyrion Jun 12 '17 at 21:41