Dynamically generating SQL queries in Grails

Question

We have a search page on a grails application. This page needs to be able to dynamically build queries based on a number of properties.

For example:

• "find users with email like bob.smith@%"
• "find users with email equal "[email protected]"
• "find users who are a member of group x"
• "find user with id 42"
• "find user with name like "Jason P%"

If this were a Java application I would use the hibernate criteria API. One problem with the criteria API is it makes arbitrary joins impossible.

So is there a way to dynamically build queries like this? If the criteria API in GORM can be dynamically modified that could work, or I could use a totally different approach.

Answer 1

Seems like a similar question from you was answered by @dmahapatro few months back. Your present scenario would look something like:

def user = User.createCriteria().list{
      if(params.email != null){
          or{
             eq('email', params.email)
             iLike('email', "%${params.email}%")
          }
      } else if(params.id != null){
          idEq(params.id)
      } else if(params.name != null){
          eq('name', params.name)
      } else if(params.memberOf != null){ //This can be optimized
         groups{
            eq('name', params.memberOf)
         }
      }
   }

Is this what you were looking for?