xSlok xSlok - 23 days ago 7
SQL Question

Grails withCriteria, value between two database columns?

how do I test if a value is between two of my database columns?

I need to do something like this:

between(column1, column2, 'value')


or something like this:

or{
and {
ge("hora_inicio", evento.hora_inicio)
le("hora_fim", evento.hora_inicio)
}
and {
ge("hora_inicio", evento.hora_fim)
le("hora_fim", evento.hora_fim)
}
}


any ideias?

Thanks!

Answer

There is not an option to check that two columns are between a value. You are going to have to do two separate comparisons. You can compare one database column to another though, but it doesn't see like that's quite what you want. See documentation on this here: http://docs.grails.org/latest/ref/Domain%20Classes/createCriteria.html

From your second example, it looks like you need to find all the entries that have a start time between evento.hora_inicio and evento.hora_fim or that have a order time between evento.hora_inicio and evento.hora_fim.

It feels like this can be simplified to look for all the entries that have an hora_inicio before the evento.hora_fim and have a hora_fim after the evento.hora_inicio. That would look something like this:

Event evento = getEventFromSomewhere()
Appointment.createCriteria().list{
    le 'hora_inicio', evento.hora_fim
    ge 'hora_fim', evento.hora_inicio
}