0

I'm currently creating a search functionality for my page. The data will be coming from DB.

In the HTML, the date is displayed in this format dd/mm/yyyy. What I want to do is, in the select query, I want to select the all columns and the date should be displayed using that format and I have a a couple of like conditions that will be used to check if there is a match.

How will I select all column, format date and at the same time check for all matches using one single statement in criteria?

Here's what I've done so far, but this wrong.

 searchString = searchString.toLowerCase() 

        def employeeSearchCri = Employee.createCriteria();
        def searchedEmployeeList = employeeSearchCri.list(){
            or {
               ilike("employeeNo", "%" + searchString + "%")
               ilike("firstName", "%" + searchString + "%")
               ilike("lastName", "%" + searchString + "%")
               ilike("middleName", "%" + searchString + "%")
               ilike("jobPosition", "%" + searchString + "%")
               ilike("date_format(hireDate, '%d/%m/%Y' )", "%" + searchString + "%")
               ilike("status", "%" + searchString + "%")
            }
            and{
                eq("companyId",companyId)
            }
        }
        return searchedEmployeeList;
user3714598
  • 1,733
  • 5
  • 28
  • 45

1 Answers1

1

The first thing I recommend is to comment out all of the ilikes except for one. Pick any one. The point is to focus on getting just one of them to work. Then, and only then, add another ilike.

Second, confirm that the pattern your using (ex. %SEARCHSTRING%) is valid for the LIKE operator of the database your using.

Third, once you get it working, you'll learn that unfortunately it won't work as well as you'd like. For example, multi-word searches won't work with your approach. Searching is hard. I got it working quite well, but boy was it a pain. If you want it done right, it's best to let the pros handle it, such as by using Apache Lucene.

Matching the date

That said, the best way to handle the date is to convert it into a Date object and to match on that. You can use something like this:

import java.text.SimpleDateFormat

def searchDate

try { 
    searchDate = new SimpleDateFormat('dd/MM/yyyy').parse(searchString)
} catch (java.text.ParseException e) { }

...

or {
    ...
    if(searchDate) eq("hireDate", searchDate)
}

Tip

You can re-write your ilikes in a more concise form by using GStrings:

ilike("employeeNo", "%$searchString%")

And if you build a list of the columns you want to use ilike on, you can simplify your query. Here's a complete example:

searchString = searchString.toLowerCase()

def columns = [
    "employeeNo",
    "firstName",
    "lastName",
    "middleName",
    "jobPosition",
    "status"
]

def searchDate

try { 
    searchDate = new java.text.SimpleDateFormat('dd/MM/yyyy').parse(searchString)
} catch (java.text.ParseException e) { }

def employeeSearchCri = Employee.createCriteria();
def searchedEmployeeList = employeeSearchCri.list() {
    or {
        columns.each { ilike(it, "%$searchString%") }        
        if(searchDate) eq("hireDate", searchDate)
    }

    and{
        eq("companyId",companyId)
    }
}

return searchedEmployeeList
Emmanuel Rosa
  • 9,697
  • 2
  • 14
  • 20
  • First of all thank you for all the tips, it was really helpful. I have one question though, assuming I have this **searchString "/2015"** In your example, this wouldn't match the hireDate column right? May I ask if there is a possibility to even do so in grails? I've tried to use this sqlRestriction("cast(date_format(hire_date, '%d/%m/%Y') AS CHAR)") and I also have a like statement at the bottom but I get this "java.lang.String cannot be cast to java.util.Date". Thank you :) – user3714598 Aug 18 '15 at 05:54
  • Correct. There's no way to turn `/2015` into a Date. But, if you come up with a search syntax for specifying the parts of a Date, such as `year:2015`, you can use that in your criteria. Ex: year("hireDate", searchYear). So basically you would need some logic to determine which *type* of search your doing (ex. plain text, hire date, hire month, hire day, hire year) and proceed from there. You can use functions year(), month(), and day() in your criteria. See https://grails.github.io/grails-doc/latest/guide/GORM.html#detachedCriteria – Emmanuel Rosa Aug 18 '15 at 16:10