Grails / GORM criteria query with hasmany String

Question

I have a domain object (Cat) like this:

class Cat {
   String name

   static hasMany = [
      nicknames: String
   ]
}

(A cat has a name, and also has many nicknames (which are Strings))

And I am trying to query all the cats with certain nicknames.

I've tried this:

PagedResultList getCatsByNickname(String nickname, Map params) {
   PagedResultList results = Cat.createCriteria().list(params) {
      'ilike'('nicknames','%'+nickname+'%')
   }
   return results
}

But it never returns any results. (If I change the query to just use the simple name attribute, it works finding all cats with that name, but I want to query against the nicknames.)

I also tried this:

PagedResultList getCatsByNickname(String nickname, Map params) {
   PagedResultList results = Cat.createCriteria().list(params) {
      'nicknames' {
         'ilike'('nicknames','%'+nickname+'%')
       }
   }
   return results
}

But I get the error: org.hibernate.MappingException: collection was not an association: example.Cat.nicknames

So the question is, how do I query against a hasMany of type String?

Custer

Some people asking the same question: grails.1312388.n4.nabble.com/… grails.1312388.n4.nabble.com/… – Custer

This bug appears to be the cause of the error you are getting: GRAILS-5887 – Monomerous

Answer 1

After a lot of trying and researching, I found this will work with Grails 2.4.0, I don't know about older versions.

Cat.withCriteria {
    createAlias('nicknames', 'n') 
    ilike 'n.elements', '%kitty%'
}

The trick is to use 'n.elements'

Sharkey

I still get "org.hibernate.MappingException: collection was not an association" when using this method in 2.4.0 – Monomerous

This worked for me and I think this is not documented anywhere in the Grails doc. Thanks. – Disruption

This works for me and I cannot find any document about this – Betthel

Answer 2

You can use HQL for querying in such a scenario. For example,

Cat.findAll("from Cat c where :nicknames in elements(c.nicknames)", [nicknames:'kitty'])

Unscathed

Thank you for your response! Okay, this gets me started, but how do I say I want nicknames like kitty? – Custer

I think that findAll uses PostgreSQL syntax, so try %kitty% – Mutineer

%kitty% doesn't work. i tried this on mysql and hibernate generates a sql that looks "select * from cat c where 'kitty' in ( select nicknames_string from cat_nicknames n where c.id=n.cat_id )" ....unfortunately it seems like you would need to use an OR clause (nickname like %kitty%' OR nickname like '%timmy%' OR ...) – Unscathed

Right... I was thinking of Like or Ilike. – Mutineer

Answer 3

You can also use HQL (tested with Grails 2.5.0):

Cat.findAll("from Cat c inner join c.nicknames as n where upper(n) like '%'||?||'%'", [nickname.toUpperCase()])

Millennial