Creating and using an SQL database function

The Expr method on the DBFunction class returns a function defined by a list of column references and character sequences. The argument to this function is a list that contains only objects of type:

• java.lang.CharSequence – For example, pass a String that contains SQL operators or other functions.
• gw.api.database.ColumnRef – The type that the query.getColumnRef(columnName) method returns.

The query builder APIs concatenate the objects in the list in the order specified to form an SQL expression.

For example, the following Gosu code creates a new database function from column references to two columns, with the sum (+) operator. You can use the new function to compare values against the sum of these two columns.

// Create an SQL function that subtracts two integer properties 
var query = Query.make(Person).withLogSQL(true)
var expr = DBFunction.Expr({
  query.getColumnRef("NumDependents") , " - ", query.getColumnRef("NumDependentsU18")
})
query.compare(Person#NumDependents, Relop.NotEquals, null)
query.compare(Person#NumDependentsU18, Relop.NotEquals, null)
query.compare(expr, GreaterThan, DBFunction.Constant(2))

var results = query.select()
results.orderBy(QuerySelectColumns.path(Paths.make(Person#FirstName)))
        .thenBy(QuerySelectColumns.path(Paths.make(Person#LastName)))

print("Rows where Person has more than 2 dependents aged 18 or over")
for (row in results) {
  print(row.DisplayName + " Total Dependents " + row.NumDependents + 
          ", Dependents 18 or over " + (row.NumDependents - row.NumDependentsU18))
}

This code prints results similar to the following:

Rows where Person has more than 2 dependents aged 18 or over
Adam Auditor Total Dependents 5, Dependents 18 or over 3
Alex Griffiths Total Dependents 4, Dependents 18 or over 3
Alice Shiu Total Dependents 5, Dependents 18 or over 5
Allison Whiting Total Dependents 6, Dependents 18 or over 5
Annie Turner Total Dependents 5, Dependents 18 or over 3
...