grails - findBy highest id AND another criteria

Question

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!

score 5 · Accepted Answer · answered Sep 16 '16 at 12:27

5

Despite Joshua Moore gave you a good solution, there is another simplier in one line.

MyDomain.findAllByCompany(company, [sort: 'number', order: 'desc', limit: 1])?.first()

answered Sep 16 '16 at 12:27

quindimildev

1,280
8
21

score 4 · Answer 2 · answered Sep 15 '16 at 13:11

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.

grails - findBy highest id AND another criteria

2 Answers2