Is there a cleaner, more Grailsy way to write this query?

Question

I have a couple scenarios where I want to retrieve a single Advertiser and eager fetch most of its object graph. I definitely don't want to do this by default, so I've been searching for the right way to do this for a single query. This is what I've come up with so far:

    Advertiser.createCriteria().get {
        eq('id', id)
        createAlias('foos', 'foos', CriteriaSpecification.LEFT_JOIN)
        createAlias('foos.bars', 'bars', CriteriaSpecification.LEFT_JOIN)
        createAlias('bars.baz', 'bazzes', CriteriaSpecification.INNER_JOIN)
    }

This works, but I dislike it for a couple reasons.

It seems like a very indirect and unintuitive way to say "just join the dang tables please."
There is no compile time checking. If the foos association didn't exist on an Advertiser, the compiler would not care.

Here's how I'd really love to see it work:

Advertiser.where {
    id == id
    foos {
        bars {
            baz
        }
    }
}

But this doesn't do any joining; apparently, if you're not actually specifying conditions for that property (>, <, ==, etc) it will just ignore you.

What are my options? How can I, at bare minimum, make this more intuitive to read? Looking for the closest to my ideal as I can get.

Edit

I've tried some of the suggestions below and things aren't working, and it may be partially because I'm not getting my syntax right. Let's say that I need to eager fetch an additional 1st level association called whatsits

I'm trying to do it this way:

Advertiser.withCriteria {
    idEq(id)
    whatsits
    foos {
        bars {
            baz 
        }
    }
}

I've also tried

Advertiser.withCriteria {
    idEq(id) && whatsits && foos {
        bars {
            baz
        }
    }
}

But different approaches yield different exceptions, strange looking queries, etc.

score 0 · Accepted Answer · answered May 07 '13 at 15:10

0

This isn't too far from your first example, but you may or may not find it more intuitive?

def result = Advertiser.withCriteria {
    eq('id', id)
    fetchMode 'foos', FetchMode.JOIN
    fetchMode 'foos.bars', FetchMode.JOIN
    fetchMode 'bars.baz', FetchMode.JOIN
}

answered May 07 '13 at 15:10

Andrew

2,239
13
7

The question uses 1 INNER_JOIN and 2 LEFT_JOINs. FetchMode.JOIN will result in 3 INNER JOINs. – Ed J May 06 '17 at 06:30

dmahapatro · Answer 2 · 2013-05-07T18:27:54.350

0

You can use DSLs in createCriteria/withCriteria as you have used in the where clause, they are by default eagerly fetched. You get result as a list, so result[0] should be it.

def result = Advertiser.withCriteria {
    idEq(id)
    foos{
       bars{
          baz{

          }
       }
    }
}

After getting the result back you can check whether you have the associations present, that is,

if(result[0].foos){...}

UPDATE

Refer this Sample Code for details.

UPDATE2

&&,||,== cannot be used in a criteria. They have dedicated DSL's for the same operations like and{}, or{}, eq(). Have a look at Grails Criteria

Advertiser.withCriteria {
    idEq(id)
    whatsits{}
    foos {
        bars {
            baz
        }
    }
}

edited May 07 '13 at 18:27

answered May 07 '13 at 15:23

dmahapatro

49,365
7
88
117

Is there a way to tell it to only fetch one object, like `createCriteria().get` does in my example? – Samo May 07 '13 at 15:31
In above case it will fetch only one object since we are fetching against the primary key `id`. Isn't it? You can use `createCriteria().get` to get the same result back as the object itself and not as list of one object. – dmahapatro May 07 '13 at 16:21
Also, this doesn't work. I get "No such property: foos for class: grails.orm.HibernateCriteriaBuilder" – Samo May 07 '13 at 16:26
Ok then it will be great if we can see all the domain classes and associations. – dmahapatro May 07 '13 at 16:27
I may be having a syntax issue. Please see the update on my question. Thanks! – Samo May 07 '13 at 17:57
Refer this workable [Sample Code](http://paste.ubuntu.com/5642307/) for more details. – dmahapatro May 07 '13 at 18:20
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/29555/discussion-between-samo-and-dmahapatro) – Samo May 07 '13 at 18:24

Is there a cleaner, more Grailsy way to write this query?

2 Answers2