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?

Some people asking the same question: http://grails.1312388.n4.nabble.com/Criteria-query-on-properties-of-type-List-lt-String-gt-td1325707.html http://grails.1312388.n4.nabble.com/GORM-how-to-set-criteria-for-object-with-list-or-set-of-strings-td1388277.html — McDave, Sep 23 '11 at 21:35
This bug appears to be the cause of the error you are getting: [GRAILS-5887](https://jira.grails.org/browse/GRAILS-5887) — cweston, Oct 31 '14 at 20:29

Igor Aguiar · Answer 1 · 2014-06-13T18:48:41.523

15

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'

edited Jun 13 '14 at 18:48

answered Jun 13 '14 at 18:43

Igor Aguiar

176
1
5

2

I still get "org.hibernate.MappingException: collection was not an association" when using this method in 2.4.0 – cweston Oct 31 '14 at 19:57
2

This worked for me and I think this is not documented anywhere in the Grails doc. Thanks. – Shashank Agrawal Apr 29 '15 at 12:53
2

This works for me and I cannot find any document about this – Samuel Jan 03 '18 at 03:31

aldrin · Answer 2 · 2011-09-25T05:26:47.463

8

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'])

edited Sep 25 '11 at 05:26

answered Sep 23 '11 at 16:21

aldrin

4,482
1
33
50

Thank you for your response! Okay, this gets me started, but how do I say I want nicknames **like** kitty? – McDave Sep 23 '11 at 21:01
I think that findAll uses PostgreSQL syntax, so try %kitty% – bitbucket Sep 23 '11 at 21:13
%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 ...) – aldrin Sep 25 '11 at 05:35
Right... I was thinking of Like or Ilike. – bitbucket Sep 26 '11 at 23:13

score 0 · Answer 3 · answered Oct 05 '16 at 16:31

0

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()])

answered Oct 05 '16 at 16:31

artemisian

2,976
1
22
23

Grails / GORM criteria query with hasmany String

3 Answers3

Linked