1

Is there a way to specify that a list in a namedquery is case insensitive? I think that before grails 2.2 this was by default, but with Grails 2.2.3 is not working...

This is the line of my code that makes the call to the named query and tries to list sorting in a case insensitive way.

BackendUser.filter(company,filter).list(max:max,offset:offset,"sort":sortName,order:order,ignoreCase:true)

As you can see, I've tried adding the ignoreCase:true option, but it's not working. And I cannot add the sorting in the named query because I use it in other places that need different sorting options.

Just in case it's needed, this is the named query

static namedQueries = {
    filter {company,filter->
        eq 'company',company
        if (filter.firstName){
            ilike 'firstName',"%${filter.firstName}%"
        }
        if (filter.lastName){
            ilike 'lastName',"%${filter.lastName}%"
        }
        if (filter.email){
            ilike 'email',"%${filter.email}%"
        }
    }
}

EDIT: Sorry, I forgot to mention that I'm using MongoDB and I think is this one who's causing the problem

Eylen
  • 2,617
  • 4
  • 27
  • 42
  • ilike is a case insensitive indeed. You can enable the log of sql to see that Hibernate should output query's with `lower()`. –  Aug 19 '13 at 11:57
  • I mean the ordering, not the filtering itself. the ilike is working ok, the problem is when sorting for example by name with: 'AAA'.'aaa,'BBB'. It orders: 'AAA','BBB','aaa' instead of 'AAA','aaa','BBB' – Eylen Aug 19 '13 at 12:03
  • Hi @Eylen how did you solve your issue? I need to achieve same, I don't want to put sorting inside named queries, but inside list method of named query. – Aditya T Oct 17 '17 at 13:32
  • @AdityaThakur I think I did it manually because there was a bug in the version I used. I don't know if it's been fixed already or not – Eylen Oct 19 '17 at 06:09
  • @Eylen Thanks for replying, actually Grails by default implements case-insensitive sorting, I confirmed it by looking at the sql generated, I am using Mysql, For me, issue was some thing different. – Aditya T Oct 24 '17 at 12:20

1 Answers1

0

Looking here, it seems that you can do it with:

//pass dir to your named query
order(new Order(param, dir=='asc').ignoreCase())
  • I'd prefer not to add the sorting in the named query, just in the list method because I use this named query with different sorting needs in other places – Eylen Aug 19 '13 at 12:21
  • To handle different sorts, just pass it as param to the namedQuery and handle it. –  Aug 19 '13 at 12:32
  • 1
    I looked again in the [list method](http://grails.org/doc/latest/ref/Domain%20Classes/list.html). It have the ignoreCase and it should be true as default, so maybe it's something with 2.2.3. I will test here and update my answer. –  Aug 19 '13 at 12:50
  • I thought so, let me know if you find something please :) – Eylen Aug 19 '13 at 12:57
  • Tested here, but the order was applied correctly. Please, [enable the sql log](http://stackoverflow.com/questions/2568507/how-to-log-sql-statements-in-grails) to see if your query have the order by too. –  Aug 19 '13 at 13:53
  • I'm really sorry, I thought I have posted that I'm using MongoDB, and I think is this one that's causing the problem. Any suggestion with mongo? I'm trying to see the queries sent to mongo to see how they reach there... – Eylen Aug 20 '13 at 06:28
  • Ah, ok :-) I'm not using Mongo, but I suggest you to create a simple application with Grails 2.2.1, check if the order is correct, upgrade to 2.2.3 and check again. If you confirm this, your best bet is to raise a JIRA to the plugin. –  Aug 20 '13 at 12:00