L. Grobler L. Grobler - 2 months ago 18
MySQL Question

grails - findBy highest id AND another criteria

I've looked a bunch of answers to this question here on SO and elsewhere but all I can track down is cases where people just want to find the highest id, the max dateCreated or the latest db entry but what I want to do is retrieve the latest object created that also matches another criteria. My domain class has the following properties:

id
,
number
,
company
,
type
,
dateCreated
and
content
. The
company
property can only be set to 'OYG' or 'BAW' and the
number
property is an auto incrementing int. What I want to do is retrieve the record with the highest
number
that also has its
company
property set to 'OYG' or 'BAW`.

So here's an example:

+----------------------------------------------------------+
| id | number | company | type | dateCreated | content |
+----------------------------------------------------------+
| 1 | 0 | OYG | TsAndCs | 15/09/2016 | stuff |
| 2 | 0 | BAW | TsAndCs | 15/09/2016 | stuff |
| 3 | 1 | OYG | TsAndCs | 16/09/2016 | stuff |
| 4 | 2 | OYG | TsAndCs | 17/09/2016 | stuff |
| 5 | 1 | BAW | TsAndCs | 16/09/2016 | stuff |
+----------------------------------------------------------+


I want to say def doc = Document.findByHighestNumberAndCompany('OYG') then it should bring back the object with id 4. def doc = Document.findByHighestNumberAndCompany('BAW') should bring back id 5's object, etc.

Any help would be appreciated. Thanks!

Answer

Should be easy enough if you order by the number in descending order, and limit your results to one. So perhaps something like this?

String companyName = 'OYG'
def results = MyDomain.createCriteria().list() {
    eq("company", companyName)
    maxResults(1)
    order("number", "desc")
}
println results[0].id // will print 4

Using this approach you could create a named query so you can pass the company name as a parameter.