Dynamically creating a query based on params being

问题:

In my task management application, users should be able to filter tasks based on : assignedTo, priority, status and/or dueDate

I am not sure on how to create a dynamic query in that it will build a query based on the available parameters.

For example :

If I have a URL such as : task/index?assignedTo=1&status=2

I can build a query based on only these two parameters. The method I am used to is the

Task.findAllByAssignedToAndStatus(
   User.get(params.assignedTo),
   TaskStatus.get(params.status)
)

I obviously dont want to implement a DRY method by writing out each findAllBy query for every possible URL parameter combination.

Is there a good way to do this in grails?

回答1:

We have implemented filter functionality on the domain classes to do this. In short you add small snippets of namedQueries to your domain class.

Example:

class Employee {

    String firstname
    String lastname
    Integer age

    static constraints = {
    }

    static namedQueries = {
        filteronFirstname { String inFirstname ->
            if (inFirstname&& inFirstname?.size() > 0) {
                ilike 'firstname', "%${inFirstname}%"
            }
        }

        filteronLastname { String inLastname ->
            if (inLastname && inLastname?.size() > 0) {
                ilike 'lastname', "%${inLastname}%"
            }
        }

        filteronAgeOlderThen { String ageOlderThen ->
            if (age && age ?.size() > 0) {
                gt 'age', ageOlderThen as Integer
            }
        }

    }
}

This enable fine grained filter capabilities so you can build 1 list method that uses all filter methods and depending on what the user gives as input the namedqueries are joined together.

Employee.filteronFirstname("John").
filterOnLastname("Doe").
filteronAgeOlderThen("10").
list(params)

回答2:

I managed to get this working using createCriteria as follows :

I am using the value 0 for any of the params if the user selects 'All' for that particular filter, therefore it will be omitted from the where clause, ie no eq() statement for that parameter.

A snippet of the code:

else
    {       
        def assignedTo = Integer.parseInt(taskParams.assignedTo)
        def priority = Integer.parseInt(taskParams.priority)
        def status = Integer.parseInt(taskParams.status)

        tasks = Task.createCriteria().list {            

            eq("project", Project.get(taskParams.PId))

            if(assignedTo > 0)
                eq("assignedTo", User.get(assignedTo))

            if(priority > 0)
                eq("priority", TaskPriority.get(priority))

            if(status > 0)
                eq("status", TaskStatus.get(status))                
        }           
    }
    return tasks
}

回答3:

I would consider the Searchable Plugin for this (http://grails.org/plugin/searchable). It is easy to specify which properties of Task should be searchable.

回答4:

Take a look at fluent interfaces, you can use these to add or remove (filter) each parameter.

http://en.wikipedia.org/wiki/Fluent_interface

回答5:

My solution for a dynamic Where query from URL:

if (params.query) {
    def classLoader = getClass().getClassLoader();
    def query = new GroovyShell(classLoader).evaluate(
            "import myapp.Visit; Visit.where {$params.query}")
    visits = query.list(params)
} 

This takes its constraints from an URL like this:

visit/index?query=client.status=='suspect';atHome==false