DataServiceStringSearchRegexSearch.groovy

import grails.gorm.services.Query
import grails.gorm.services.Service
import grails.gorm.services.Where

@Service(User)
abstract class UserDataService {
    Closure transformToRegex  = { string -> string ? "^${string.trim()}.*" : '' }

    @Where({ firstName ==~ ~/${transformToRegex.call(searchString)}/|| lastName ==~ ~/${transformToRegex.call(searchString)}/})
    abstract List<User> searchUsersByFirstOrLastName(String searchString)

    @Where({firstName ==~ ~/${transformToRegex.call(firstName)}/ && lastName ==~ ~/${transformToRegex.call(lastName)}/})
    abstract List<User> searchUsersByFirstAndLastName(String firstName, String lastName)

    @Query($/SELECT DISTINCT new Map(user.id as id, user.username as username, user.clientSetupId as clientSetupId, user.email as email, user.firstName as firstName, user.lastName as lastName, user.userMetadata as userMetadata, user.lastLogin as lastLogin, user.pictureUrl as pictureUrl,ur.manager.email as managerEmail , manager.firstName as managerFirstName ,manager.lastName as managerLastName ,manager.userMetadata as managerUserMetadata,
            manager.lastLogin as managerLastLogin ,manager.pictureUrl as managerPictureUrl ,manager.externalEmployeeCode as managerExternalEmployeeCode)
             FROM $User user
              left join $UserRelationship ur on ur.user.id = user.id or ur.manager.id = user.id
              left join ur.manager manager
              WHERE user.firstName like CONCAT('%', $searchString, '%') or user.lastName like CONCAT('%', $searchString, '%') or  manager.firstName like CONCAT('%', $searchString, '%') or manager.lastName like CONCAT('%', $$searchString, '%')
           /$)
   abstract List<Map> searchForUsersWhereNameOrEmailLike(String searchString)

}