CODEWITHSUNDEEP
grailsgrails-orm
srkiNZ84
srkiNZ84

Reputation: 3568

Filtering on multiple associations and optional where clauses

I've got a several domain classes, with some simple associations between them as follows:

class Business {
  String name
  City city
  Industry industry
}

class City {
  String name
}

class Industry {
  String name
}

In my application, I would like to have a "filter" where the list of all businesses can be filtered according to the City and Industry. I am able to get the City and Industry id's from the filter back to the Business controller, however, when I get to the controller to do the filtering, I have this code:

...
def industry = Industry.get(params.int('industryid'))
def city = City.get(params.int('cityid'))

def businessList = Business.findAllByIndustryAndCity(industry, city)
...

This code works when both the City and Industry fields have values. However, sometimes the user might want to just filter by city or industry and not both. In this case, the filter fails, as when either of the values are null no results are returned. How could I specify that if either of the association values are "null", then the "find" query should remove this constraint altogether? i.e. match "all" for that field

Note that I realise that it would be easy to put an if statement checking whether the values are null and then executing a different "find" statement based on that. However, while this would work with two values, I don't think it would scale well as more filterable values are added.

Upvotes: 2

Views: 1320

Answers (1)

Tomasz Kalkosiński
Tomasz Kalkosiński

Reputation: 3723

You can build criteria.

def c = Business.createCriteria()
def results = c.list{
    and {
        if (industry) {
            eq("industry", industry)
        }
        if (city) {
            eq("city", city)
        }
    }
}

Check reference here in grails docs.

However your code needs N+1 queries for N parameters. Maybe you can reduce it to one query using criteria idea? If your Business entity holds foreign keys to both Industry and City this one should work:

def c = Business.createCriteria()
def results = c.list{
    and {
        if (params.industryid) {
            eq("industry_id", params.industryId as Long)
        }
        if (params.cityid) {
            eq("city_id", params.cityid as Long)
        }
    }
}

Both examples are untested but you should get the idea.

Upvotes: 4

Related Questions