Groovy search criteria with priority

Question

I have a search box and while searching with user code, my expected result is the user codes which starts with the given keyword (keyword%) and the remaining result should be (%keyword%) .

Search with "TEST"

Expected result:

1. TEST1
2. TEST_ABC
3. TEST_QRS
...
11. ABC_TEST
12. BCTEST

like this

But the result I'm getting is :

1. ABC_TEST
2. BCTEST
...
11. TEST1
12. TEST_ABC
13. TEST_QRS

How can I write a groovy criteria to get the search result with these priorities ?

My current code is :

def searchKeyword = PERCENT+keyword+PERCENT
def userList = User.createCriteria().list (max: max, offset: offset) {
  or {
    ilike "code", searchKeyword
    ilike "name", searchKeyword
  }
  order "code", "asc"
}

Thanks in advance.

score 1 · Answer 1 · edited May 23 '17 at 12:31

I do not think you can achieve the desired results using the GORM criteria orderby syntax. You will have to use a raw sql query groovy.sql.Sql.

One of the approach to priorities orderby results is to use CASE expression i.e. for the case of Mysql. For example in your case to achieve the desired result you can use a raw query like

SELECT * FROM user WHERE name LIKE '%test%' or code LIKE '%test%' ORDER BY 
CASE WHEN name LIKE 'test%' THEN 1 WHEN code LIKE 'test%' THEN 2 ELSE 3 END;

In this case names starting with 'test%' are first priority followed by codes starting with 'test%' and finally the remaining results. For the LIMIT arguments max and offset you will have to use positional parameters (?) to specify them.

For more details check:

Grails GORM sort by CASE statement

Ordering by specific field value first

Thanks for the answer, will try this and update soon. – JiniKJohny Jun 16 '16 at 09:31 — JiniKJohny, Jun 16 '16 at 09:31

score 0 · Answer 2 · answered Jun 09 '16 at 20:29

I'm not sure, but maybe this can help: you can sort the output list of users with a comparator. The comparator could work in this way:

If user1 has no letters before the keyword but user2 has, then user1 < user2 and vice-versa
if both of users has letters before the keyword, those that has less letters before the keyword has -1 as a result.
If both of users has letters after the keyword, those that has less letters has -1.

Something like this:

class UserComparator implements Comparator<User> {

    final String token

    UserComparator(String token){
        this.token = token
    }

    @Override
    int compare(User u1, User u2) {
        int u1Start = u1.name.indexOf(token)
        int u2Start = u2.name.indexOf(token)

        if (u1Start == 0 && u2Start != 0) return -1
        if (u2Start == 0 && u1Start != 0) return 1

        if (u1Start != 0 && u2Start != 0) return u1Start - u2Start

        return u1.name.size() - u2.name.size()
    }
}

Groovy search criteria with priority

2 Answers2