CODEWITHSUNDEEP
grails
John Little
John Little

Reputation: 12343

Grails: how to get last inserted record matching query

Getting the last record is trivial in SQL, e.g. (for MySQL)

class ExchangeRate {
    Currency from  
    Currency to  
    BigDecimal rate // e.g. 10
    Date dateCreated
}
class Currency {
    String iso
    etc..
}

SQL to get the latest is trivial:

Select max(id), rate 
from exchange_rate 
where from_id = 1
  and to_id = 3

or

select rate 
from exchange_rate
where from_id = 2
order by id desc
limit 1

The question is, how does one do this efficiently in Grails? I only want a single result.

This obviously wont work:

    def query = ExchangeRate.where {
        from == from && to == to && id == max(id)
    }
    ExchangeRate exchangeRate = query.find()

There have been several posts on this, but not with an actual answer which I could figure out how to apply (I am a SQL guy, and don't know hibernate language and would prefer a solution which did not involve it if there was one)

If there was an easy way to run SQL directly without having to hand manage result sets that would work (as we will never use another DB other than MySQL)

I am sure it could be done with sort and limit, but a) haven't found an example I could copy, and b) would assume this be inefficient, because it appears that the sorting and limiting is done in code, not SQL?

This example is in the documentation:

Book.findAll("from Book as b where b.author=:author",
         [author: 'Dan Brown'], [max: 10, offset: 5])

could lead to this:

def exchangeRates = ExchangeRate.findAll("from ExchangeRate as e where e.from = :from order by id desc", [from: from], [max: 1])

if (exchangeRates.size() == 1) {
    return exchangeRates.first().rate
}
return null

is there a better way (e.g. one which doesnt use hibernate low level language, or one which uses SQL instead, or one which is more efficient?)

Upvotes: 0

Views: 769

Answers (1)

saw303
saw303

Reputation: 9072

Try using a subquery according to the documentation.

def query = ExchangeRate.where {
  id = max(id).of { from == fromValue } && to == toValue
}

query.find()

Upvotes: 1

Related Questions