Grails criteria projections - get rows count

Question

I have Hotel entity:

class Hotel {
City city
}

Now, i need count of hotels with given city. It could be done in this way:

def hotels = Hotel.findAllByCity(city)
def cnt = hotels.size()

But it's very dirty way. It seems that with criteria it would be better, but i have no idea how to implement it...

Answer 1

Dave is right that you can use the countBy* methods for simple counting. If you need more than two criteria you will have to revert to the criteria api, HQL or SQL. It is very common to need more than two criteria especially with an active and evolving codebase.

Here is an example of how you would use the Criteria api to do projections:

def c = Hotel.createCriteria()

def hotelCount = c.get {
    projections {
        count('id')
    }
    gt("stars", 2)          
    eq("city", city)            
    eq("deleted", false)

}

Alternatively (more elegantly) you could even use Criteria#count like the following:

def c = Hotel.createCriteria()

def hotelCount = c.count {
    gt("stars", 2)          
    eq("city", city)            
    eq("deleted", false)

}

Just for completeness sake:

class Hotel {
    City city
    Boolean deleted = false
    Integer stars
}

class City {
    String name
}

An integration test (using the build-test-data plugin)

import grails.test.*

class HotelTests extends GrailsUnitTestCase {

    void testCriteria() {
        City city1 = City.build(name:'one')
        assertNotNull(city1)
        City city2 = City.build(name:'two')
        assertNotNull(city1)

        Hotel fiveStarHotel= Hotel.build(city:city1, deleted:false, stars:5)
        assertNotNull(fiveStarHotel)

        Hotel hotelInCity2 = Hotel.build(city:city2, deleted:false, stars:5)
        assertNotNull(hotelInCity2)

        Hotel deletedHotel = Hotel.build(city:city1, deleted:true, stars:5)
        assertNotNull(deletedHotel)

        Hotel threeStarHotel = Hotel.build(city:city1, deleted:false, stars:3)
        assertNotNull(threeStarHotel)

        Hotel oneStarHotel = Hotel.build(city:city1, deleted:false, stars:1)
        assertNotNull(oneStarHotel)

        def c = Hotel.createCriteria()

        def hotelCount = c.get {
            projections {
                count('id')
            }
            gt("stars", 2)          
            eq("city", city1)           
            eq("deleted", false)

        }
        assertEquals(2, hotelCount) //should only find the 5 and 3 star hotel

        def c2 = Hotel.createCriteria()
        hotelCount = c2.count {
            gt("stars", 2)          
            eq("city", city1)           
            eq("deleted", false)

        }
        assertEquals(2, hotelCount) //should only find the 5 and 3 star hotel
    }
}

Answer 2

There are dynamic counters as well as finders on domain objects:

Hotel.countByCity(city)

More details in the user guide, of course