grails createCriteria for date calculation

Question

I am trying to fetch data from database using criteria

Domain class Invoice -

class Invoice{
    Date invoiceNo
    Date invoiceDate
    int interval

    static constraints = { }
    }

criteria module from the controller

if(!params.sort && !params.order) {
            params.sort = "invoiceDate + interval" //invoiceDate+ interval
            params.order = "asc"
        }
        def c = Invoice.createCriteria()
        def results = c.list(params) {
            between("invoiceDate+ interval", invoiceDate, invoiceDate+ interval)

        }

I want to calculate dueDate from the invoiceDate & interval.

i want a result like this

invoiceNo | invoiceDate | interval| dueDate    |
001       | 2016-09-30  | 5       | 2016-10-05 |
002       | 2016-09-15  | 5       | 2016-09-20 |

Answer 1

I will insist you to modify your Domain class to add derived field as given below.

Modified Domain class - adding derived field dueDate

class Invoice{
Date invoiceNo
Date invoiceDate
int interval

//new field added
Date dueDate //Derived filed- will not persisted to the database

static mapping = {
   //formula to calculate dueDate (invoiceDate + interval)
    dueDate formula:"ADDDATE(invoice_date, interval)"
}

static constraints = { }
}

criteria code snippet- to fetch the invoices whose dueDate is in between current Date and current Date + 7.

def criteria = Invoice.createCriteria()

   Date currentDate =  new Date()

   //fetching data based on the criteria
   List invoiceList = criteria.list {
         between('dueDate', currentDate, (currentDate + 7))
     }