Can I do a math operation inside a createCriteria

Question

Is it posible to do a math problem inside a createCriteria?

For example:

If in my table I have two columns, if the two together make 100 I don't want to show it in my result of the query

or

if the other column is in other table?

table

column 1  column 2

  50        50
  20        20

I want the second row

or

table 1       table 2 

  50            50
  20            20

score 3 · Answer 1 · answered Mar 06 '13 at 18:18

3

One option would be to define a formula field in your domain class. Something like:

class SumFormula {
    Integer column1
    Integer column2
    Integer sum

    static mapping = {
        sum formula: 'column1 + column2'
    }
}

Then, you can apply criteria as follows:

SumFormula.createCriteria().list() {
    ne("sum", 100)
}

answered Mar 06 '13 at 18:18

Andrew

Derived (`formula`) properties are SQL expressions, so it's not easy to traverse associations between tables/classes. You might be able to get fancy with SQL, but in a somewhat limited/inflexible way. – Andrew Mar 06 '13 at 19:18

1 Answers1