Grails GORM : SELECT AS

问题:

I'm trying to get all the Users that are born today with GORM but I'm failing to write this query in Grails:

SELECT
DAY(dateOfBirth) AS 'day',
MONTH(dateOfBirth) AS 'month'
FROM Users
WHERE day = '...' AND month = '...';

... will be replaced with today's values.

Minimal User Domain class

class User {

  Date dateOfBirth

  ...

}

Minimal UserService class

@Transactional
class UserService {

  def getTodayBirthdays() {

    def bornTodayQuery = User.where{
      /* I'm thinking here I must
       * select the DAY and MONTH from the DB
       * and compare it with the today ones.
       */
    }       

    User usersBornToday = bornTodayQuery.findAll()      

    usersBornToday      

  }

}

Any ideas how can I do an alias (SELECT field AS alias) with GORM?

I'm using:

• Grails 2.4.4

Thanks!

回答1:

You could use a where query in your service:

@Transactional(readOnly = true)
def listBirthday(int _month, int _day) {
  // Calendar.JANUARY equals to zero!
  def m = _month + 1
  // Run a where query on the users
  User.where {
    month(dateOfBirth) == m && day(dateOfBirth) == _day
  }
}

@Transactional(readOnly = true)
def listBirthdayToday() {
  def cal = Calendar.getInstance()
  listBirthday(cal.get(cal.MONTH), cal.get(cal.DAY_OF_MONTH))
}

In addition to month and day there are some other functions, here is the documentation (look for "Other Functions")