1

I have a question about how to use unaccent extension of PostgreSQL with Grails.

I have a lot of descriptions in french and PostgreSQL is not accent insensitive. I installed the unaccent extension and it works very well on PgAdmin III.

How can I configure Grails and/or PostgreSQL to use unaccent in my controller ?

This is my Domain:

Class Description {
   String content
   int type = -1
}

For example, can I "translate" the next query using GORM ?

SELECT content 
FROM description 
WHERE unaccent(content) ILIKE unaccent(%myInputHere%)

Thanks for reading

Abincepto
  • 1,016
  • 8
  • 13

1 Answers1

1

Since this is SQL specific you will need to use an SQL Restriction within your criteria. For example:

def myinput = params.myinputfieldname
def results = Description.createCriteria().list() {
  sqlRestriction "unaccent(content) ILIKE unaccent('%$myinput%')"
}

At least that's the idea.

Edit

Take care to note the fact 'content' in the restriction above refers to the actual SQL column name instead of the domain's field name.

Joshua Moore
  • 24,706
  • 6
  • 50
  • 73
  • 1
    @ArrrghGrails Note when you use `sqlRestriction` then actual column names has to be used instead of domain object's field names. `content` used in above answer reflects the column name in table. – dmahapatro Mar 31 '14 at 14:47
  • @dmahapatro Excellent point (Will edit post to include this warning). – Joshua Moore Mar 31 '14 at 14:56