GORM createCriteria with one-to-many relation

Question

since a couple of hours I try to figure out how to create a criteria with a kind of must-be-in-list criterion.

If I reduce my code I have following two domain classes:

Hotel - The base domain class that I want to retrieve:

class Hotel {

    static hasMany = [rooms: Room, amenities: HotelAmenity]

}

HotelAmenity - A hotel has a list of amenities.

class HotelAmenity {

    String name

}

#1 My first approach was something like this:

PagedResultList pgl = Hotel.createCriteria().list(max: limit, offset: offset) {
    // ..
    and {
        amenities {
            'in'("id",myLongIdsList)
    }
}

This works. But in this case I get every hotel returned that contains at least one of the specified anemities. But my desired goal is to retrieve hotels that have all specified amenities only.

#2 So I tried following

// ..
amenities {
    condition.hotelAmenities.collect { it.toLong() }.each {
        and {
            eq('id', it)
        }
}

But this code looks like it's messing up the result set, because it always returns an empty list, if there are more then one amenitiies defined. By the way, in this one-amenity scenario all hotels that my result list contains won't have any other amenity in their amenities-list but that one I searched for - even though plenty of them are assigned. I totally don't understand this behaviour.

#3 Another approach I tried was playing around with listDistinct instead of list. But I did not try this any further once I recognized that I don't get a PagedResultList but a list of hotel domain objects returned. And so I won't have the totalCount parameter (which I need for my pagination functionality)

So, I am stucked between writing my own query and/or solving this issue with a GORM approach. It could also be that I am a bit confused about how exactly this should be work.

For any notice or solution I would be very glad.

Thanks, Christopher

PS: I use Grails version 1.3.7

PPS: If any informations are missing please let me know.

EDIT

This is now the solution I come along with:

Map sqlParams = [:]
String mySql = "SELECT SQL_CALC_FOUND_ROWS hotel.id FROM hotel WHERE "
if(hotelAmenities.size()>0) {
  String commaSeperatedAmenities = hotelAmenities.toString()
  // the amenity IDs as comma seperates list so we can use them in the `IN` statement (ie 1,3,6,7)
  commaSeperatedAmenities = commaSeperatedAmenities.substring(1,commaSeperatedAmenities.length()-1)
  mySql += '( SELECT COUNT(hotel_amenities_id) from hotel_hotel_amenity ' +
           'WHERE hotel_amenities_id = hotel.id AND hotel_amenity_id IN ' +
           '(' + comaAmentities + ') ) = ' + condition.hotelAmenities.size()
}

mySql += 'LIMIT :offset, :limit'
sqlParams.put("offset",offset)
sqlParams.put("limit",limit)

def mresult = sqlInstance.rows(mySql,sqlParams)
List<Hotel> hotels = Hotel.getAll(mresult.collect {it.id})
int calcFoundRows = sqlInstance.rows("SELECT FOUND_ROWS()").get(0).get("FOUND_ROWS()").toString().toInteger()

score 2 · Answer 1 · edited May 23 '17 at 12:04

2

Build the criteria on the fly:

def neededAmenities = ...

PagedResultList pgl = Hotel.createCriteria().list(max: limit, offset: offset) {
    // ..
    amenities {
        and {
            neededAmenities.each { needed ->
                idEq (needed.id)
            }
        }
    }
}

In pure SQL, it's not much easier:

where exists(amenities.id == XXX) and exists(amenities.id == YYY) and (...) ...

You could go probably more optimally with intersect query with a semantic like this:

where intersect(
    select id from amenities where ..., 
    (:neededAmenities)) 
= (:neededAmenities)

Some sample here.

edit: I tried to construct second-type criteria and failed - I see no way to build having count-type condition. Anyway, it's going to be slower then several in-s, as it will have to count Amenities for all the hotels.

edited May 23 '17 at 12:04

Community

1
1

answered Aug 22 '11 at 10:27

Victor Sergienko

13,115
3
57
91

Thanks for your reply. Your first suggested solution is similar to my approach #2 with the only difference that you use `idEq` instead. But the results are the same, one amenity works, multiple not. Further (if the criteria contains one amenitiy only) the fetched hotels won't contain their other amenities. So I am trying now to build my own HSQL and work with `Hotel.findAll(sql)`, but first I need to get used to the syntax i guess, because it's complaining about my `JOINS` (which works flawless on my MySQL console) .. – Christopher Will Aug 22 '11 at 10:55
Well, sorry - I didn't read way #2 carefully enough. Though, I believe there is a difference in order of application of `and` and `id eq`. Your criteria produces several blocks of `and{ iqEq() }`, where `and{}` makes no sense with a single condition inside. – Victor Sergienko Aug 22 '11 at 11:16
Ah OK, got it. So I will now try the pure-sql approach by fetching the hotel ids only.. and will then use `Hotel.getAll(myList)` to retrieve the grails objects. – Christopher Will Aug 22 '11 at 11:23

z.eljayyo · Answer 2 · 2011-08-23T19:54:03.253

0

UPDATE, this should give you a list of hotels that has all amenties, try this:

def amenitySize = HotelAmenity.list()?.size()
def hotelList = Hotel.createCriteria().list{
    sizeEq("amenities", amenitySize)
}

edited Aug 23 '11 at 19:54

answered Aug 22 '11 at 17:14

z.eljayyo

1,289
1
10
16

1

Yes there are better ways: 1) One shouldn't iterate the whole database in-memory; 2) `HotelAmenity.list().collection{amenity-> amenity.id}` is better spelt as `HotelAmenity.list()*.id`, same as `hotelObj.collect{hotel-> hotel.hotelAmenity.id}` turns to `hotelObj*.hotelAmenity*.id` 3) not `amenitiesIdsList.containsAll(thisHotelAmenties)`, but the opposite. So, if iterating all hotels list (which can be huge) in appserver memory was OK, it would look like: `Hotel.list().findAll{ it.hotelAmenity*.id.containsAll(amenitiesIdsList) }` – Victor Sergienko Aug 22 '11 at 20:03
Indeed, it is almost impossible to load all `hotels` into memory. We talk about almost a thousand hotels. Where each hotel has more then ten `one-to-many` relations and a couple of `many-to-many` relations (some are even set to `lazy:false`) – Christopher Will Aug 23 '11 at 02:49

GORM createCriteria with one-to-many relation

2 Answers2