6

Grails gives the possibility of creating simple string/value map properties section "Maps of Objects", first paragraph.

I was wondering, is there a way to later query the domain class (using Gorm dynamic finders, criterias or HQL) using the map property as part of the query (i.e adding a condition for the key X to have the value Y)?

dmahapatro
  • 49,365
  • 7
  • 88
  • 117
Deigote
  • 1,721
  • 14
  • 15

2 Answers2

2

After playing with it a bit and almost give up, I discovered the map syntax to (surprisingly) work in HQL. Assuming the class looks like:

class SomeClass {
   Map pairKeyProperty
}

You can build queries that look like the following:

select * from SomeClass sc where sc.pairKeyProperty['someKey'] = 'someValue' and sc.pairKeyProperty['someOtherKey'] = 'someOtherValue'

Pretty neat! I still would prefer to use criterias as they are much cleaner to compose, but they seem to not support the same syntax (or I couldn't find it).

I created a sample app in GitHub: https://github.com/deigote/grails-simple-map-of-string-value-pairs

It can be visisted at: http://grails-map-of-string-pairs.herokuapp.com/

Deigote
  • 1,721
  • 14
  • 15
1

The form above uses a cross join. To enforce an inner join use

join sc.pairKeyProperty pk1 on index(pk1) = 'someKey'
where 'someValue' in elements(pk1)
jphiloon
  • 77
  • 1
  • 9
  • Better to use "member of" rather than "elements:" join sc.pairKeyProperty pk1 on index(pk1) = 'someKey' where 'someValue' member of pk1 Does anyone know how to enforce an inner join and still have comparison operators ("like", "ilike") instead of just membership tests? – jphiloon Jan 19 '15 at 13:27
  • Hey jphiloon, thanks for the answer! I was wondering if there's any performance difference between the two joins when the cross join uses the "where" properly - my last understanding is that there's not, but maybe I'm wrong. – Deigote Jan 20 '15 at 09:43