Refactor giant single query to a lot of smaller subqueries, but with the same result

Question

I have the list of parameters, and I want to build the query from it. Actually, I've already finished that work, and now I have the giant method, which I've trying to refactor. I need the way to split the one giant method to a lot of smaller methods and then combine them as the one.

I've met the troubles while building dynamic query request and need advice.

So, I've looked to the documentation "6.4.4 Detached Criteria"(http://grails.org/doc/2.2.4/guide/GORM.html#detachedCriteria).

In the chapter "Using Detached Criteria for Subqueries" found the next sample:

def results = Person.withCriteria {
 gt "age", new DetachedCriteria(Person).build {
     projections {
         avg "age"
     }
 }
 order "firstName"
}

And I've tried to write some methods that returns DetachedCriteria as analogue, for example:

DetachedCriteria insertCriteriaLong(def objClass, String cleanKey, def e) {
    new DetachedCriteria(objClass).build {
        if (e.key.toString().endsWith("_from")) {
            gte(cleanKey, e.value as Long)
        } else if (e.key.toString().endsWith("_to")) {
            lte(cleanKey, e.value as Long)
        } else {
            eq(cleanKey, e.value as Long)
        }
    }
}

While debugging, I see that method created the DetachedCriteria object and fill it with the correct filter(criteria) rules.

But then I've trying to call this method from inside the main request, I've got zero criterions as the result.

    def cr = new DetachedCriteria(objClass).build { 
        insertCriteriaLong(objClass, cleanKey, e)
    }
    cr.list(params)

And the "cr.list(params)" will return a lot of records. "insertCriteriaLong" was ignored.

So, I've thought to rewrite and to return Criteria or just a Closure, but I've still not found the correct syntax. For example, I've tried:

Closure insertCriteriaInteger2 = { String cleanKey, def e ->
    if (e.key.toString().endsWith("_from")) {
        gte(cleanKey, e.value as Integer)
    } else if (e.key.toString().endsWith("_to")) {
        lte(cleanKey, e.value as Integer)
    } else {
        eq(cleanKey, e.value as Integer)
    }
}

Could you give me the tip, what I should try anything else?

I found the next question that looks similar, but it is not helpfull for me: Grails/Groovy: Modify a "query" closure at runtime

Could you chain together named queries? http://grails.org/doc/latest/ref/Domain%20Classes/namedQueries.html — tim_yates, Sep 06 '13 at 08:16
Nope, I can't. NamedQueries are static. But I'm generating my criterion conditions at runtime. — wwarlock, Sep 06 '13 at 08:23
Ok, I thought you might be able to have a named query for each small eventuality, and then chain them together dynamically as they are required. What if you pass the `DetachedCriteria` into `insertCriteriaLong` and than call `crit.gte(cleanKey, e.value as Long)`.. Not sure if that will work, but it might be worth a go? — tim_yates, Sep 06 '13 at 08:29
Yes I've tried to send DetachedCriteria into insertCriteriaLong, but had the same result. — wwarlock, Sep 06 '13 at 08:31
Can you wrap the Detached criteria in the new method with `return{...}` (note it's a return block with {}) and test? I wont be able to test it right now but would give a try soon. — dmahapatro, Sep 06 '13 at 12:21
Yes I've did it already, please look the question. And yes, I have tests. Could you provide your variant please? — wwarlock, Sep 06 '13 at 15:59

wwarlock · Answer 1 · 2013-09-07T02:46:25.613

Ok, I've finally found the solution. Tim_yates, thank you!

Here it is.

DetachedCriteria insertCriteriaLong(DetachedCriteria previousCriteria, String cleanKey, def e) {
    if (e.key.toString().endsWith("_from")) {
        previousCriteria.gte(cleanKey, e.value as Long)
    } else if (e.key.toString().endsWith("_to")) {
        previousCriteria.lte(cleanKey, e.value as Long)
    } else {
        previousCriteria.eq(cleanKey, e.value as Long)
    }
    previousCriteria
}

And it works even if we have more than one criteria record, something like (the code is simplified for example).

DetachedCriteria insertCriteriaString(DetachedCriteria previousCriteria, def e) {
    10.times {
        previousCriteria.ilike(e.key, '%' + it + '%')
    }
    previousCriteria
}

Actually, I found we are mustn't return any result at all. So, the next code would be correct too:

void insertCriteriaLong(DetachedCriteria currentCriteria, String cleanKey, def e) {
    if (e.key.toString().endsWith("_from")) {
        previousCriteria.gte(cleanKey, e.value as Long)
    } else if (e.key.toString().endsWith("_to")) {
        previousCriteria.lte(cleanKey, e.value as Long)
    } else {
        previousCriteria.eq(cleanKey, e.value as Long)
    }
}

Refactor giant single query to a lot of smaller subqueries, but with the same result

1 Answers1