Grails hasMany and criteria not working as expected

Question

I have two models with a hasMany relation

class Puturru implements Serializable {
    String name

    Set<Fua> fuas

    static hasMany = [fuas: Fua]

    static constraints = {
        fuas(nullable: true)
    }
}

And

class Fua {

    String name

    static constraints = {
    }
}

When running the next test I'm getting a LazyInitializationException

 void "test something"() {
    given:
    def puturrus

    Puturru.withNewSession { session ->
        p = Puturru.build()
        Fua f
        for (int i = 0; i < 10; i++) {
            f = Fua.build()
            p.addToFuas(f)
        }
        f.save(failOnError: true, flush: true)

    }

    when:
    Puturru.withNewSession {

        puturrus = Puturru.createCriteria().listDistinct {

            fetchMode("fuas", FetchMode.JOIN)
        }

    }

    then:
    Puturru.withNewSession {
        assert puturrus.fuas*.name.size() > 0

    }

    p.fuas.each { it.delete() }
    p.delete()
}

If I set puturrus mapping

static mapping = {
    fuas lazy: false
}

Then all the elements are retrieved although it uses multiple selects, otherwise it use this query

select ...
from puturru ...
left outer join puturru_fua fuas2_ on this_.id=fuas2_.puturru_fuas_id

I can't set mapping in the real app, because I don't want to fetch all entries in all cases. To me looks like I'm missing something to make the criteria go further than the intermediate table.

I'm using grails 2.3.6 and hibernate 3.6.10.8.

Do somebody know why is not getting the "fuas" objects?

EDIT 1: Some more information

I have been trying to do some changes to the domains and test aiming to figure out what the problem is.

After @sudhir suggestion, I started playing with joins I changed the criteria to something like

    puturrus = Puturru.createCriteria().listDistinct {
        fuas {
        }
    }

Then the query generated by gorm and hibernate was

select ...
from puturru this_
inner join puturru_fua fuas3_ on this_.id=fuas3_.puturru_fuas_id
inner join fua fuas_alias1_ on fuas3_.fua_id=fuas_alias1_.id

This time we got all the entries but not their relations with the puturrus domains. We compromise and put a second query just for getting the associations, so for the time being the second session block is

Puturru.withNewSession {

    puturrus = Puturru.createCriteria().listDistinct {
        fuas {
        }
    }

    Puturru.createCriteria().listDistinct {
        fetchMode("fuas", FetchMode.JOIN)
    }
}

This is way better than my previous approach that was iterate over all the puturrus and all the fuas within the criteria session, but I think is still sub-optimal, so are there any way to get both the data and the associations in a single query?

You are not saving Putturu at the end of given: block. Why are you using withNewSession at all this places, its not required at all. Also, beaware, fetchmode join can give you duplicate results, you may want to use fetchmode FM.SELECT — Sudhir N, Mar 23 '15 at 07:02
Actually build method, which is part of build test data plugin, save. I made this code just to reproduce a situation I found in a real world app, in the real world there are good reasons to split the three sessions. I tried FM.SELECT too with no success. — esauro, Mar 23 '15 at 09:12
Try adding join 'fuas' in your criteria, and remove fetchMode, so it would be like criteria.listDistinct { join "fuas"} — Sudhir N, Mar 23 '15 at 09:22
@sudhir if I do so, the query has two inner joins, but still doesn't work. However if I inspect the elements with a debugger, this query `select fuas0_.puturru_fuas_id as puturru1_1_0_, fuas0_.fua_id as fua2_0_ from puturru_fua fuas0_ where fuas0_.puturru_fuas_id=?` and then the elements are available out of the session. — esauro, Mar 23 '15 at 13:16

Sudhir N · Answer 1 · 2015-03-24T04:20:07.417

0

You can use following hql

Puturru.executeQuery("select p from Puturru p inner join fetch p.fuas as f where p.id = :id", [id:id])

If you want all Puturru remove where condition

edited Mar 24 '15 at 04:20

answered Mar 24 '15 at 04:14

Sudhir N

4,008
1
22
32

You're right, that would be useful is the code were so simple like this examples, but the real code is much more complex and it includes several other domains, and we don't want such a big hql query. – esauro Mar 24 '15 at 09:41

score 0 · Accepted Answer · answered Mar 24 '15 at 10:54

We found a proper solution using createAlias

Puturru.withNewSession {
    puturrus = Puturru.createCriteria().listDistinct {
        createAlias("fuas", "fuas", CriteriaSpecification.LEFT_JOIN)
    }
}

Actually it seems to be a bug in Hibernate 3.6 already fixed in Hibernate 4

Grails hasMany and criteria not working as expected

EDIT 1: Some more information

2 Answers2